求怎样实现多字段的统计汇总?
Code city district
__________________________________
3052310
3052314
3052314
3052314
305 24 9
80223
80224
80223
80356
假设一个表格如上
我想知道根据首先依据同一个city和不同的district的组合,看看不同code的出现次数
然后是一个city总的不同code的记录数
最后是不同code的汇总
比如以上输出就是:
for code 305
city district code(305)
23 10 1
23 14 3
23 * sum 4
24 9 1
14 * sum 1
* * sum 5
city district code(802)
2 3 2
2 4 1
2 * sum 3
5 6 1
5 * sum 1
* * sum 4
[解决办法]
select city,district,count(*) from tb group by city,districtselect city,count(distinct code) from tb group by cityselect distinct code from tb
[解决办法]
不顯示總計
use Tempdbgo--> --> if not object_id(N'Tempdb..#T') is null drop table #TGoCreate table #T([Code] int,[city] int,[district] int)Insert #Tselect 305,23,10 union allselect 305,23,14 union allselect 305,23,14 union allselect 305,23,14 union allselect 305,24,9 union allselect 802,2,3 union allselect 802,2,4 union allselect 802,2,3 union allselect 803,5,6GoSelect [Code],[city]=CASE WHEN GROUPING([Code])=0 AND GROUPING(district)=1 OR GROUPING([Code])=1 THEN '*' ELSE RTRIM([city]) end,[district]=CASE WHEN GROUPING([Code])=0 AND GROUPING(district)=1 OR GROUPING([Code])=1 THEN 'sum' ELSE RTRIM(district) end,COUNT(1) AS TotalCount from #T GROUP BY [Code],[city],district WITH rollupHAVING GROUPING([Code])=0/*Code city district TotalCount305 23 10 1305 23 14 3305 * sum 4305 24 9 1305 * sum 1305 * sum 5802 2 3 2802 2 4 1802 * sum 3802 * sum 3803 5 6 1803 * sum 1803 * sum 1*/