[SQL]在多個欄位中，找出各數值出現的次數 UNPIVOT + CROSS APPLY

2013-12-03

7166
0
SQL

要如何在多個欄位中，找出各數值出現的次數呢?
可以試著用UNPIVOT將數值轉成一個欄位後，再來統計次數哦!

有朋友問要如何在一些資料5個欄位中，找出那些數值出現3次以上。

如上面的資料，要找欄位N1~N5中，那些數值出現3次以上。

要計算數值出現的次數，直覺來看，要先將那些欄位轉成一個欄位，然後計算Count。

測試資料如下，


Use tempdb;
go
--建立RowData
--drop table RowData;
Create Table RowData
(
 [Y] int
, [S] Int
, [M] Int
, [D] Int
, [W] Int
, [N1] int
, [N2] int
, [N3] int
, [N4] int
, [N5] int
, [ID_NUM] int IDENTITY(1,1)
);

insert into RowData Values(2012, 77, 3, 30, 5, 1, 2, 3, 9, 38);
insert into RowData Values(2009, 72, 4, 10, 5, 1, 2, 3, 23, 32);
insert into RowData Values(2011, 10, 1, 12, 3, 1, 2, 4, 21, 32);
insert into RowData Values(2012, 104, 5 ,1  ,2  , 2 , 4,  13, 17 , 29 );
insert into RowData Values(2007, 257, 12 ,25  ,2  , 2 , 4,  13,  22, 28 );
insert into RowData Values(2009,132 , 7 , 3 , 5 , 2 , 4,  14, 29 , 35 );
insert into RowData Values(2009, 63, 3 , 30 , 1 ,  8, 15, 25 , 26 , 28 );
insert into RowData Values(2007,51 , 3 , 12 ,  1, 8 , 15,  25,  27 , 38 );
insert into RowData Values(2010, 81, 4 , 23 , 5 , 8 , 15,  27, 35 , 36 );
insert into RowData Values(2007,157 ,  8, 7 , 2 , 9 ,10 , 23 ,  31, 36 );
insert into RowData Values(2009,243 , 12 , 7 , 1 ,  9, 10,  25, 31 , 33 );
insert into RowData Values(2012, 152, 6 , 26 , 2 ,  9, 10 ,27 , 35,  39);

SELECT * FROM RowData
ORDER BY Y, M, D;

如下，將N1~N5的數值轉成同一個欄位來呈現，這時候可以使用 UNPIVOT 來將多欄轉成1欄，如下，


--將N1~N5轉成同一個欄位
SELECT ID_NUM, Num, NumPos
FROM 
   (SELECT ID_NUM, N1, N2, N3, N4, N5
   FROM RowData) p
UNPIVOT
   (Num FOR NumPos IN 
      (N1, N2, N3, N4, N5)
)AS unpvt;

再來再將以上的資料 CROSS APPLY 起來，就可以知道那些數值在那些時候出現，出現的次數為何，如下，


--使用CTE包裝起來
--使用unPivot + CTE + Corss Apply 找出數字出現的次數
WITH NumDetail AS
(
SELECT ID_NUM, Num, NumPos
FROM 
   (SELECT ID_NUM, N1, N2, N3, N4, N5
   FROM RowData) p
UNPIVOT
   (Num FOR NumPos IN 
      (N1, N2, N3, N4, N5)
)AS unpvt)
--查出數字出現的次數
SELECT A.ID_NUM, A.Num, B.NumCount
, C.Y, C.M, C.D, C.W, C.S
FROM NumDetail A
CROSS APPLY (SELECT Num, COUNT(*) AS NumCount
				FROM NumDetail T 
				WHERE T.Num = A.Num
				GROUP BY T.Num) B
INNER JOIN RowData C 
ON A.ID_NUM = C.ID_NUM 
--過濾出現過3次的資料
WHERE B.NumCount >= 3
ORDER BY  B.NumCount DESC, A.Num;