SQL Server 按照分类输出Excel

wskickahn

　　公司销售部门要统计业务员的客户并按照业务员名字输出Excel，看下面程序
　　--声明需要的变量
　　declare @sql varchar(600),@TypeID varchar(6),@MyName varchar(10)
　　--每个业务员要有一个序号（数字型）
　　select @TypeID=min(ID) from A_CUST

　　select @MyName =EMPLOYEE_NAME from A_CUST where>　　--当存在满足序号的记录时进行处理

　　while exists(select 1 from A_CUST where>　　begin
　　--拼凑需要执行的语句
　　set @sql='bcp "select * from (select'+'''CUSTOMER_CODE'''+' AS 客户编码,'+'''CUSTOMER_NAME'''+' AS 客户名称,'+'''CUSTOMER_FULL_NAME'''+' AS 客户全称,'
　　+'''EMPLOYEE_CODE'''+' AS 工号,'+'''EMPLOYEE_NAME'''+' AS 姓名,'+'''ADDRESS'''+' AS 地址,'+'''TELEPHONE'''+' AS 电话,'+'''CONTACT'''+' AS 联系人'--在xls文件中显示列名

　　set @sql=@sql+' union all select CUSTOMER_CODE,CUSTOMER_NAME,CUSTOMER_FULL_NAME,EMPLOYEE_CODE,EMPLOYEE_NAME,ADDRESS,TELEPHONE,CONTACT from A_CUST where>　　--使用xp_cmdshell系统存储过程执行拼凑好的语句（需要使用高级选项开关预先开启cmdshell组件）
　　exec master..xp_cmdshell @sql
　　--获得下一个业务员的序号（序号是不连续的）

　　select @TypeID=isnull(min(ID),@TypeID+1) from A_CUST where>
　　select @MyName =EMPLOYEE_NAME from A_CUST where>　　end
　　下面的程序是根据各表关联生成的视图
　　drop view A_CUST
　　create view A_CUST as select CUSTOMER_CODE,CUSTOMER_NAME,CUSTOMER_FULL_NAME,EMPLOYEE_CODE,EMPLOYEE_NAME,d.ADDRESS,d.TELEPHONE,e.CONTACT,A_C.ID from CUSTOMER a
　　left join CUSTOMER_SALES b ON a.CUSTOMER_BUSINESS_ID = b.CUSTOMER_ID
　　left join EMPLOYEE c ON b.Owner_Emp = c.EMPLOYEE_ID
　　left join A_C ON c.EMPLOYEE_CODE = A_C.GH
　　left join CUSTOMER_ADDRESS d ON b.CUSTOMER_BUSINESS_ID = d.CUSTOMER_BUSINESS_ID
　　left join CUSTOMER_CONTACT e ON b.CUSTOMER_BUSINESS_ID = e.CUSTOMER_BUSINESS_ID
　　where a.ApproveStatus = 'Y'
　　insert into A_C(GH) select EMPLOYEE_CODE from EMPLOYEE
　　select * from  A_C
　　select from A_CUST where ADDRESS is not null
　　select  from CUSTOMER where CUSTOMER_CODE = '0080'
　　select *from CUSTOMER_SALES where CUSTOMER_ID = 'F16DD932-0155-4A9A-4FE9-13BF5CF9277D'