求怎样实现多字段的统计汇总?解决方法

求怎样实现多字段的统计汇总?
Code city district
__________________________________
305 23 10
305 23 14
305 23 14
305 23 14
305 24 9
802 2 3
802 2 4
802 2 3
803 5 6

假设一个表格如上
我想知道根据首先依据同一个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

------解决方案--------------------

SQL code

select city,district,count(*) from tb group by city,district
select city,count(distinct code) from tb group by city
select distinct code from tb

------解决方案--------------------

不顯示總計

SQL code

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([Code] int,[city] int,[district] int)
Insert #T
select 305,23,10 union all
select 305,23,14 union all
select 305,23,14 union all
select 305,23,14 union all
select 305,24,9 union all
select 802,2,3 union all
select 802,2,4 union all
select 802,2,3 union all
select 803,5,6
Go
Select 
[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 rollup
HAVING GROUPING([Code])=0

/*
Code    city    district    TotalCount
305    23    10    1
305    23    14    3
305    *    sum    4
305    24    9    1
305    *    sum    1
305    *    sum    5
802    2    3    2
802    2    4    1
802    *    sum    3
802    *    sum    3
803    5    6    1
803    *    sum    1
803    *    sum    1
*/

求怎样实现多字段的统计汇总?解决方法

相关推荐