SQLServer按照分类输出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'

网页名称：SQLServer按照分类输出Excel
网站URL：http://mswzjz.cn/article/gogsoj.html