一个表数据如下:字段: a1 a2
----------------------------------------------
数据: a 0
a 0
b 1
c 2
c 2
c 1
c 0
c 1
希望统计出来的结果为:a1中重复数据最多,在a1中重复数据最多里面的a2为0的有多少条记录,在a1中重复数据最多里面的a2为1的有多少条记录,在a1中重复数据最多里面的a2为2的有多少条记录,也就是显示出来的结果应该是:T1为a1字段名,T2为统计出来的总数,T3为0的值时在T2中有多少个,T4为1的值时在T2中有多少个, T5为2的值时在T2中有多少个T1 T2 T3 t4 t5
---------------------------------------------------------------------
c 5 1 2 2
a 2 2 0 0
b 1 0 1 0请帮忙……
----------------------------------------------
数据: a 0
a 0
b 1
c 2
c 2
c 1
c 0
c 1
希望统计出来的结果为:a1中重复数据最多,在a1中重复数据最多里面的a2为0的有多少条记录,在a1中重复数据最多里面的a2为1的有多少条记录,在a1中重复数据最多里面的a2为2的有多少条记录,也就是显示出来的结果应该是:T1为a1字段名,T2为统计出来的总数,T3为0的值时在T2中有多少个,T4为1的值时在T2中有多少个, T5为2的值时在T2中有多少个T1 T2 T3 t4 t5
---------------------------------------------------------------------
c 5 1 2 2
a 2 2 0 0
b 1 0 1 0请帮忙……
a1 As T1,
Count(a1) As T2,
SUM(Case a2 When 0 Then 1 Else 0 End) As T3,
SUM(Case a2 When 1 Then 1 Else 0 End) As T4,
SUM(Case a2 When 2 Then 1 Else 0 End) As T5
From
T2
Group By
a1
Order By
T2 Desc
(a1 Varchar(10),
a2 Int)
Insert T2 Select 'a', 0
Union All Select 'a', 0
Union All Select 'b', 1
Union All Select 'c', 2
Union All Select 'c', 2
Union All Select 'c', 1
Union All Select 'c', 0
Union All Select 'c', 1
GO
Select
a1 As T1,
Count(a1) As T2,
SUM(Case a2 When 0 Then 1 Else 0 End) As T3,
SUM(Case a2 When 1 Then 1 Else 0 End) As T4,
SUM(Case a2 When 2 Then 1 Else 0 End) As T5
From
T2
Group By
a1
Order By
T2 Desc
GO
Drop Table T2
--Result
/*
T1 T2 T3 T4 T5
c 5 1 2 2
a 2 2 0 0
b 1 0 1 0
*/