表结构如下,其中每条记录的值不超过12,统计每个ID总数出现的频率ID TA,TB,TC,TD,TE
1 1 2 3 4 5
1 2 4 5 7 9
1 1 4 9 11 12
1 3 4 5 6 72 1 6 7 8 11
2 2 4 5 7 10
2 1 7 9 11 12
2 3 4 5 6 7---------------
期望得到的查询结果如下
1 4(4),5(3),1(2),2(2),3(2),7(2),9(1),11(1),12(1),6(1)
2 7(3),1(2),4(2),5(2),6(2),8(1),9(1),10(1),11(1),12(1)
DROP TABLE tb
go
CREATE TABLE TB(id INT ,ta INT,tb INT,tc INT,td INT,te INT)
INSERT INTO tb
SELECT 1,1,2,3,4,5 UNION ALL
SELECT 1,2,4,5,7,9 UNION ALL
SELECT 1, 1, 4, 9 ,11, 12 UNION ALL
SELECT 1, 3, 4, 5, 6, 7 UNION ALL
SELECT 2, 1, 6, 7, 8, 11 UNION ALL
SELECT 2, 2 ,4 ,5 ,7, 10 UNION ALL
SELECT 2 ,1, 7, 9 ,11, 12 UNION ALL
SELECT 2, 3, 4 ,5, 6, 7
GO
SELECT * FROM dbo.tb
/*id ta tb tc td te
----------- ----------- ----------- ----------- ----------- -----------
1 1 2 3 4 5
1 2 4 5 7 9
1 1 4 9 11 12
1 3 4 5 6 7
2 1 6 7 8 11
2 2 4 5 7 10
2 1 7 9 11 12
2 3 4 5 6 7(8 行受影响)*/
DECLARE @n INT
DECLARE @sql VARCHAR(max)
SET @n=1;
SET @sql='select id, '
WHILE(@n<=12)
BEGIN
SET @sql =@sql+' No'+CAST(@n AS VARCHAR)+'=sum(CASE WHEN ta='+CAST(@n AS VARCHAR)+' THEN 1 ELSE 0 END)+
SUM(CASE WHEN tb='+CAST(@n AS VARCHAR)+' THEN 1 ELSE 0 end)+
SUM(CASE WHEN tc='+CAST(@n AS VARCHAR)+' THEN 1 ELSE 0 end)+
SUM(CASE WHEN td='+CAST(@n AS VARCHAR)+' THEN 1 ELSE 0 end)+
SUM(CASE WHEN te='+CAST(@n AS VARCHAR)+' THEN 1 ELSE 0 end),'
SET @n=@n+1
END
SET @sql=STUFF(@sql,LEN(@sql),1,'')+' from tb group by id'
EXEC (@sql)
/*id No1 No2 No3 No4 No5 No6 No7 No8 No9 No10 No11 No12
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 2 2 2 4 3 1 2 0 2 0 1 1
2 2 1 1 2 2 2 4 1 1 1 2 1
*/