SQL Server 按照分類輸出Excel

標籤：exce   --   產生   exec   tom   sele   add   while   統計   

公司銷售部門要統計業務員的客戶並按照業務員名字輸出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 [email protected]
--當存在滿足序號的記錄時進行處理
while exists(select 1 from A_CUST where [email protected])
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 @[email protected]+‘ 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>[email protected]+1
select @MyName =EMPLOYEE_NAME from A_CUST where [email protected]
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‘

SQL Server 按照分類輸出Excel