标题: SQL Server 按照分类输出Excel [打印本页] 作者: Vecloud 时间: 2022-6-2 11:26 标题: SQL Server 按照分类输出Excel 公司销售部门要统计业务员的客户并按照业务员名字输出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 ID=@TypeID
--当存在满足序号的记录时进行处理
while exists(select 1 from A_CUST where ID=@TypeID)
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 ID='+cast(@TypeID as varchar(10))+')a" queryout "D:\customer\'+cast(@MyName as varchar(10))+'.xls" -c -q -S"127.0.0.1" -U"sa" -P"密码" -d"数据库名称"'--查询满足条件的记录并保存到xls文件中
--使用xp_cmdshell系统存储过程执行拼凑好的语句（需要使用高级选项开关预先开启cmdshell组件）
exec master..xp_cmdshell @sql
--获得下一个业务员的序号（序号是不连续的）
select @TypeID=isnull(min(ID),@TypeID+1) from A_CUST where ID>=@TypeID+1
select @MyName =EMPLOYEE_NAME from A_CUST where ID=@TypeID
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