pivot行轉列實踐：仿工資查詢

今天帶來的是pivot行轉列的一個案例，根據工資查詢抽象來的

首先是了解查詢工資的表結構

1、首先工資是我們的工資，是以要有一個一個表

2、工資有許多部分組成，是以要有項目表

3、由上邊兩表即可再組合出新表項目工資表（一個人可以有多個工資項目）

4、最後就是工資表

既然表已經确定，接下來就是建表了

① --員工表（員工ID、工号、姓名、性别）

SELECT * INTO 
#Employee 
FROM (
	SELECT 1 ID,100001 WorkCode,'張三' [Name],0 sex
	UNION
	SELECT 2,100002,'李四' ,0
	UNION
	SELECT 3,100003,'王五' ,0
	UNION
	SELECT 4,100004,'小明' ,0
	UNION
	SELECT 5,100005,'小紅' ,1
	UNION
	SELECT 6,100006,'李華' ,1
	) a

② --工資項目表（ID、名稱）

–有社會工作經驗的都知道，我們工資是有多部門組成的并不是單一的，也不是一層不變的，好比基本工資，這是不管如何都要發的，還有崗位工資，你是在這個崗位上的工資，

–公司有福利等，有餐補，住房補貼等，我們也有五險一金要交(雖然不是給公司，但是得從工資裡扣)（剛踏足社會找工作的朋友記得留心，這是公司必須要給我們辦理的，沒有的話可以舉報，…(苦口婆心800字省略)）

SELECT * INTO 
#SalaryElement
FROM (
	SELECT 1 SalaryElementID,'基本工資' [SalaryItemName]
	UNION
	SELECT 2,'崗位工資' 
	UNION
	SELECT 3,'餐補補貼' 
	UNION
	SELECT 4,'宿舍費用' 
	UNION
	SELECT 5,'固定績效' 
	UNION
	SELECT 6,'考核績效' 
	UNION
	SELECT 7,'平時加班' 
	UNION
	SELECT 8,'周末加班' 
	UNION
	SELECT 9,'五險一金'
	UNION
	SELECT 10,'其他類别'
	) a

③ --項目工資表（ID、員工ID、項目ID、工資金額、工資表ID（表④的））

其實這裡就已經算是可以查工資明細了，但是還有别的原因不能在這裡查

講解一條資料：員工ID為1的員工在工資項目為1的工資為1000元，他的工資表ID為1

這裡SQL較長，但是資料有規律，有想法的小夥伴可以自己寫個方法的出同樣的效果

SELECT * INTO 
#SalaryItem
FROM (
	SELECT 1 ID,1 EmpID, 1 ElementID,1000 [Money],1 SalaryID
	UNION
	SELECT 2,1,2,1000,1 UNION
	SELECT 3,1,3,1000,1 UNION
	SELECT 4,1,4,1000,1 UNION
	SELECT 5,1,5,1000,1 UNION
	SELECT 6,1,6,1000,1 UNION
	SELECT 7,1,7,1000,1 UNION
	SELECT 8,1,8,1000,1 UNION
	SELECT 9,1,9,1000,1 UNION
	SELECT 10,1,10,1000,1 UNION
	--李四
	SELECT 11,2,1,2000,2 UNION	 	  
	SELECT 12,2,2,2000,2 UNION	 	  
	SELECT 13,2,3,2000,2 UNION		  
	SELECT 14,2,4,2000,2 UNION	 	  
	SELECT 15,2,5,2000,2 UNION		  
	SELECT 16,2,6,2000,2 UNION	 	  
	SELECT 17,2,7,2000,2 UNION	 	 
	SELECT 18,2,8,2000,2 UNION	  	  
	SELECT 19,2,9,2000,2 UNION
	SELECT 20,2,10,2000,2 UNION
	--王五
	SELECT 11,3,1,3000,3 UNION	 	  
	SELECT 22,3,2,3000,3 UNION	 	  
	SELECT 23,3,3,3000,3 UNION		  
	SELECT 24,3,4,3000,3 UNION	 	  
	SELECT 25,3,5,3000,3 UNION		  
	SELECT 26,3,6,3000,3 UNION	 	  
	SELECT 27,3,7,3000,3 UNION	 	 
	SELECT 28,3,8,3000,3 UNION	  	  
	SELECT 29,3,9,3000,3 UNION
	SELECT 30,3,10,3000,3 UNION
	--小明
	SELECT 31,4,1,4000,4 UNION	 	  
	SELECT 32,4,2,4000,4 UNION	 	  
	SELECT 33,4,3,4000,4 UNION		  
	SELECT 34,4,4,4000,4 UNION	 	  
	SELECT 35,4,5,4000,4 UNION		  
	SELECT 36,4,6,4000,4 UNION	 	  
	SELECT 37,4,7,4000,4 UNION	 	 
	SELECT 38,4,8,4000,4 UNION	  	  
	SELECT 39,4,9,4000,4 UNION
	SELECT 40,4,10,4000,4 UNION
	--小紅
	SELECT 41,5,1,5000,5 UNION	 	  
	SELECT 42,5,2,5000,5 UNION	 	  
	SELECT 43,5,3,5000,5 UNION		  
	SELECT 44,5,4,5000,5 UNION	 	  
	SELECT 45,5,5,5000,5 UNION		  
	SELECT 46,5,6,5000,5 UNION	 	  
	SELECT 47,5,7,5000,5 UNION	 	 
	SELECT 48,5,8,5000,5 UNION	  	  
	SELECT 49,5,9,5000,5 UNION
	SELECT 50,5,10,5000,5 UNION
	--李華
	SELECT 51,6,1,6000,6 UNION	 	  
	SELECT 52,6,2,6000,6 UNION	 	  
	SELECT 53,6,3,6000,6 UNION		  
	SELECT 54,6,4,6000,6 UNION	 	  
	SELECT 55,6,5,6000,6 UNION		  
	SELECT 56,6,6,6000,6 UNION	 	  
	SELECT 57,6,7,6000,6 UNION	 	 
	SELECT 58,6,8,6000,6 UNION	  	  
	SELECT 59,6,9,6000,6 UNION
	SELECT 60,6,10,6000,6 
	) a

④–工資表（ID、員工ID、年月）

上邊表③說了，那裡就可以查詢了，但是需要表④，也就是年月的原因，那把年月加到表③不就行了麼？是這樣麼，真正工資表資料并不是我這裡這麼簡單還有其他字段，我隻是抽出主要的

SELECT * INTO 
#Salary
FROM (
	SELECT 1 ID,1 EmpID,'2020-07' Years 
	UNION
	SELECT 2,2,'2020-07' 
	UNION
	SELECT 3,3,'2020-07' 
	UNION
	SELECT 4,4,'2020-07' 
	UNION
	SELECT 5,5,'2020-07' 
	UNION
	SELECT 6,6,'2020-07' 
	)a

現在表已經建好并添加了測試資料了，那就進行真經事了
關聯資料表

select  *
from #Salary x
left join #Employee e on e.ID =x.EmpID
left join #SalaryItem Item on Item.SalaryID =x.ID
left join #SalaryElement element on element.SalaryElementID = Item.ElementID

查詢結果呢就是下圖了

pivot行轉列實踐：仿工資查詢

這裡我們可以清楚的看到一個人對各項目的金額

--現在的資料還是以每個工資項目為一列顯示的，對于我們的工資條來說，這還不是最後的結果
--那現在我們要做的就是列轉行
--使用pivot for
--先列出我們要查的工資項

這裡為了代碼的美觀，我使用參數進行處理查詢的列

declare @ItemPivot nvarchar(max)
declare @ItemPivotShow nvarchar(max)

set @ItemPivot = ''--pivot内對接的
set @ItemPivotShow = '' --最終顯示的


SET @ItemPivot='
[基本工資],[崗位工資],[餐補補貼],[宿舍費用],[考核績效],
[固定績效],[平時加班],[周末加班],[五險一金],[其他類别]
';
SET @ItemPivotShow='
isnull([基本工資],0) [基本工資],
isnull([崗位工資],0) [崗位工資],
isnull([餐補補貼],0) [餐補補貼],
isnull([宿舍費用],0) [宿舍費用],
isnull([考核績效]+[固定績效],0) [績效],
isnull([平時加班]+[周末加班],0) [加班],
isnull([五險一金],0) [五險一金],
isnull([其他類别],0) [其他]
,isnull([基本工資] + [崗位工資] + [餐補補貼] + [宿舍費用] + [考核績效] + [固定績效] + [平時加班] + [周末加班] - [五險一金] - [其他類别],0) [應發金額]
--,isnull([基本工資] + [崗位工資] + [餐補補貼] + [宿舍費用] + [考核績效] + [固定績效] + [平時加班] + [周末加班] - [五險一金] - [其他類别],0) [實發金額]

然後呢，就是篩選條件了（像我們查一個人的工資，某個月的工資）

declare @years nvarchar(max) --這個可做存儲過程的參數用
set @years ='2020-07'
--還可加姓名、工号等（隻限制于這裡）

declare @pWhere nvarchar(max)
set @pWhere = ''
set @pWhere = 'x.Years=''' + @years + ''''

最後就到主角pivot登場了

declare @SalaryShow nvarchar(max)
set @SalaryShow = '
select ID,Years,WorkCode,Name, ' + @ItemPivotShow + ' 
from (
	select  x.ID,x.Years,e.WorkCode,e.Name,Money,SalaryItemName
    from #Salary x
    left join #Employee e on e.ID =x.EmpID
    left join #SalaryItem Item on Item.SalaryID =x.ID
    left join #SalaryElement element on element.SalaryElementID = Item.ElementID
	where ' + @pWhere + '
) t pivot (max(Money) for SalaryItemName in (' + @ItemPivot + ')) s
'
--pivot 聚合函數（int等類型的字段） for 要顯示的列的字段 in （需要用的字段）

最後就是執行了

EXEC (@SalaryShow
+'
DROP TABLE #Employee
DROP TABLE #SalaryElement
DROP TABLE #SalaryItem
DROP TABLE #Salary
'
)

最後：關于對這類的講解、優化及拓展

對于上邊添加臨時表的SQL，大家可以把他存入字段中，類似這裡的@SalaryShow參數一樣，然後再最後的EXEC内相加與@SalaryShow之前即可
因為我們需要繳稅，是以上邊的應發工資還不一定是實際我們到手的工資，是以，我們還可以加個扣稅處理，進行扣稅管理，得出實發工資（後邊有空我再看下吧…）
我們建立SalaryItem表是因為要可維護的原因，是以單獨做了一個表，工資的組成有很多，不同地方也有不同的工資項目，好比宿舍費用，有的公司不提供宿舍，也沒有宿舍補貼之類的
實際上的工資計算是沒有我這裡所描述的這麼簡單，因為沒一個工資項的工資已經得出來了，在實際中，我們要多方面取值

（像考勤，有遲到早退的，加班得申請并通過才有效，加班了有與打卡考勤有關，是否是有效加班呢，加班工資又安什麼規則計算呢，不同公司不同部門又不一樣，還有考核…）

…（再次省略不知道多少字，半瓶水，也不能全了解工資方面的，我隻是一個菜鳥碼農）
這段SQL也可以做成存儲過程，參數可以是年月、工号、姓名，有意拓展的話可以再把員工表分細點，加上公司部門崗位，然後查

這就是pivot行轉列實踐：真實工資查詢的全部内容了。謝謝閱讀！

追加：https://download.csdn.net/download/qq_44471040/12674013
這是我整理上傳上去的的SQL腳本，有能力的小夥伴可以下載下傳支援下！

pivot行轉列實踐：仿工資查詢

繼續閱讀

SqlServer同步資料庫-分發-訂閱

幾種SqlServer資料庫分頁方式

SQLServer資料庫比對工具用什麼好。

C#連接配接sqlserver資料庫

我的職業生涯（四）

.NET開發人員的瓶頸和職業發展

資料庫拼接列字段拼接列字段

windows8.1上安裝sqlserver2005後找不到啟動程式

hibernate.cfg.xml檔案連接配接mySql、Oracle、SqlServer配置hibernate.cfg.xml檔案連接配接mySql、Oracle、SqlServer配置

asp連接配接資料庫的代碼方法

MS SQL Server 資料庫（基礎篇）

SQL Server不存在或通路被拒絕"問題的解決"SQL Server不存在或通路被拒絕"問題的解決

asp 連接配接資料庫的方法

ms sqlserver常用sql語句

金蝶雲星空即時庫存查詢SQL語句SQLServer

解決"T-SQL無法附加到程序"錯誤的補充,T-SQLcan not attach to process