版主,再次请教SQL查询统计

Question

declare @t table(县 nvarchar(10),乡 varchar(10),村 varchar(10),人口数 int)
insert @t select 'A' ,  'A1' , 'A1'  , 500 
insert @t select 'A' ,  'A1' , 'A2'  , 600  
insert @t select 'A' ,  'A2' , 'A1'  , 500 
insert @t select 'A' ,  'A2' , 'A2'  , 600 
insert @t select 'B' ,  'B1' , 'B1'  , 600 
insert @t select 'B' ,  'B1' , 'B2'  , 800select case when grouping(县)=1 then N'县合计' else 县 end 县,
       case when grouping(乡)=1 and grouping(县)=0 then N'乡合计' else 乡 end 乡,
       case when grouping(村)=1 and grouping(乡)=0 and grouping(县)=0 then N'村合计' else 村 end 村,
       sum(人口数) 人口数
from @t
group by 县,乡,村
with rollup/*
县          乡          村          人口数
---------- ---------- ---------- -----------
A          A1         A1         500
A          A1         A2         600
A          A1         村合计        1100
A          A2         A1         500
A          A2         A2         600
A          A2         村合计        1100
A          乡合计        NULL       2200
B          B1         B1         600
B          B1         B2         800
B          B1         村合计        1400
B          乡合计        NULL       1400
县合计        NULL       NULL       3600(12 行受影响)*/

Answer 1

declare @t table(县 nvarchar(10),乡 varchar(10),村 varchar(10),人口数 int)
insert @t select 'A' ,  'A1' , 'A1'  , 500 
insert @t select 'A' ,  'A1' , 'A2'  , 600  
insert @t select 'A' ,  'A2' , 'A1'  , 500 
insert @t select 'A' ,  'A2' , 'A2'  , 600 
insert @t select 'B' ,  'B1' , 'B1'  , 600 
insert @t select 'B' ,  'B1' , 'B2'  , 800select case when grouping(县)=1 then N'合计' else 县 end 县,
       case when grouping(乡)=0 then 乡
            when grouping(乡)=1 and grouping(县)=0 then 县+N'合计' 
            else N'合计' end 乡,
       case when grouping(村)=0 then 村
            when grouping(村)=1 and grouping(乡)=0 and grouping(县)=0 then 乡+N'合计'
            when grouping(村)=1 and grouping(乡)=1 and grouping(县)=0 then N'合计' 
            when grouping(村)=1 and grouping(乡)=1 and grouping(县)=1 then N'合计' end 村,
       sum(人口数) 人口数
from @t
group by 县,乡,村
with rollup/*
县          乡            村            人口数
---------- ------------ ------------ -----------
A          A1           A1           500
A          A1           A2           600
A          A1           A1合计         1100
A          A2           A1           500
A          A2           A2           600
A          A2           A2合计         1100
A          A合计          合计           2200
B          B1           B1           600
B          B1           B2           800
B          B1           B1合计         1400
B          B合计          合计           1400
合计         合计           合计           3600(12 行受影响)
*/

Answer 2

谢谢您的帮助，表中增加了一个性别字段，如下：
县，乡，村，性别，人口数
A   A1  A1   男    300
A   A1  A1   女    200 
A   A1 A2   男    350
A   A1  A2   女    250 
A   A2  A1   男    250
A   A2  A1   女    250
A   A2  A2   男    360
A   A2  A2   女    240
B   B1  B1   男    320
B   B1  B1   女    280
B   B2  B1   男    420
B   B2  B1   女    380查询统计结果显示为：
县    乡   村   性别 人口数
合计 合计 合计  合计  3600
合计 合计 合计   男   2000 
合计 合计 合计   女   1600
A    合计 合计   合计 2200
A    合计 合计   男   1360 
A    合计 合计   女   860 
A    A1   合计   合计 1100
A    A1   合计   男   650
A    A1   合计   女   550
A    A1   A1     合计 500
A    A1   A1     男   300
A    A1   A1     女   200
A    A1   A2     合计 600
A    A1   A2     男   350
A    A1   A2     女   250
A    A2   合计   合计 1100
A    A2   合计   男   610
A    A2   合计   女   500
A    A2   A1     合计  500
A    A2   A1      男   250
A    A2   A1      女   250
A    A2   A2     合计  600  
A    A2   A2     男    360
A    A2   A2     女    240
B    合计 合计   合计 2200
B    合计 合计   男   1360 
B    合计 合计   女   860 
B    B1   合计   合计 1100
B    B1   合计   男   650
B    B1   合计   女   550
B    B1   B1     合计 500
B    B1   B1     男   300
B    B1   B1     女   200
B    B2   合计   合计  800
B    B2   合计   男  420
B    B2   合计   女  380
B    B2    B1     合计 800
B    B2    B1   男    420
B    B2    B1   女    380
相当于先按县、乡、村、性别分组统计，有什么好方法？
谢谢：）

Answer 3

declare @t table(县 nvarchar(10),乡 varchar(10),村 varchar(10),人口数 int,xb varchar(10))
insert @t select 'A' ,  'A1' , 'A1'  , 500 ,'男'
insert @t select 'A' ,  'A1' , 'A2'  , 600 ,'男' 
insert @t select 'A' ,  'A2' , 'A1'  , 500 ,'女'
insert @t select 'A' ,  'A2' , 'A2'  , 600 ,'女'
insert @t select 'B' ,  'B1' , 'B1'  , 600 ,'男'
insert @t select 'B' ,  'B1' , 'B2'  , 800,'女'select case when grouping(县)=1 then N'合计' else 县 end 县,
       case when grouping(乡)=0 then 乡
            when grouping(乡)=1 and grouping(县)=0 then 县+N'合计' 
            else N'合计' end 乡,
       case when grouping(村)=0 then 村
            when grouping(村)=1 and grouping(乡)=0 and grouping(县)=0 then 乡+N'合计'
            when grouping(村)=1 and grouping(乡)=1 and grouping(县)=0 then N'合计' 
            when grouping(村)=1 and grouping(乡)=1 and grouping(县)=1 then N'合计' end 村,
       case when grouping(xb)=0 then xb  else '合计' end 合计,
       sum(人口数) 人口数
from @t
group by 县,乡,村,xb
with rollup
order by grouping(县) desc,县,grouping(乡) desc,乡,grouping(xb) desc,xb
/*县          乡            村            合计         人口数         
---------- ------------ ------------ ---------- ----------- 
合计         合计           合计           合计         3600
A          A合计          合计           合计         2200
A          A1           A1           合计         500
A          A1           A2           合计         600
A          A1           A1合计         合计         1100
A          A1           A2           男          600
A          A1           A1           男          500
A          A2           A1           合计         500
A          A2           A2           合计         600
A          A2           A2合计         合计         1100
A          A2           A2           女          600
A          A2           A1           女          500
B          B合计          合计           合计         1400
B          B1           B1           合计         600
B          B1           B2           合计         800
B          B1           B1合计         合计         1400
B          B1           B1           男          600
B          B1           B2           女          800（所影响的行数为 18 行）
*/

Answer 4

declare @t table(县 nvarchar(10),乡 varchar(10),村 varchar(10),人口数 int,xb varchar(10))
insert @t select 'A' ,  'A1' , 'A1'  , 500 ,'男'
insert @t select 'A' ,  'A1' , 'A2'  , 600 ,'男' 
insert @t select 'A' ,  'A2' , 'A1'  , 500 ,'女'
insert @t select 'A' ,  'A2' , 'A2'  , 600 ,'女'
insert @t select 'B' ,  'B1' , 'B1'  , 600 ,'男'
insert @t select 'B' ,  'B1' , 'B2'  , 800,'女'select case when grouping(县)=1 then N'合计' else 县 end 县,
       case when grouping(乡)=0 then 乡
            when grouping(乡)=1 and grouping(县)=0 then 县+N'合计' 
            else N'合计' end 乡,
       case when grouping(村)=0 then 村
            when grouping(村)=1 and grouping(乡)=0 and grouping(县)=0 then 乡+N'合计'
            when grouping(村)=1 and grouping(乡)=1 and grouping(县)=0 then N'合计' 
            when grouping(村)=1 and grouping(乡)=1 and grouping(县)=1 then N'合计' end 村,
       case when grouping(xb)=0 then xb  else '合计' end xb,
       sum(人口数) 人口数
from @t
group by 县,乡,村,xb
with rollup
order by grouping(县) desc,县,grouping(乡) desc,乡,grouping(xb) desc,xb
/*县          乡            村            xb         人口数         
---------- ------------ ------------ ---------- ----------- 
合计         合计           合计           合计         3600
A          A合计          合计           合计         2200
A          A1           A1           合计         500
A          A1           A2           合计         600
A          A1           A1合计         合计         1100
A          A1           A2           男          600
A          A1           A1           男          500
A          A2           A1           合计         500
A          A2           A2           合计         600
A          A2           A2合计         合计         1100
A          A2           A2           女          600
A          A2           A1           女          500
B          B合计          合计           合计         1400
B          B1           B1           合计         600
B          B1           B2           合计         800
B          B1           B1合计         合计         1400
B          B1           B1           男          600
B          B1           B2           女          800（所影响的行数为 18 行）
*/

Answer 5

rollup改为cube试试...
declare @t table(县 nvarchar(10),乡 varchar(10),村 varchar(10),人口数 int,xb varchar(10))
insert @t select 'A' ,  'A1' , 'A1'  , 500 ,'男'
insert @t select 'A' ,  'A1' , 'A2'  , 600 ,'男' 
insert @t select 'A' ,  'A2' , 'A1'  , 500 ,'女'
insert @t select 'A' ,  'A2' , 'A2'  , 600 ,'女'
insert @t select 'B' ,  'B1' , 'B1'  , 600 ,'男'
insert @t select 'B' ,  'B1' , 'B2'  , 800,'女'select case when grouping(县)=1 then N'合计' else 县 end 县,
       case when grouping(乡)=0 then 乡
            when grouping(乡)=1 and grouping(县)=0 then 县+N'合计' 
            else N'合计' end 乡,
       case when grouping(村)=0 then 村
            when grouping(村)=1 and grouping(乡)=0 and grouping(县)=0 then 乡+N'合计'
            when grouping(村)=1 and grouping(乡)=1 and grouping(县)=0 then N'合计' 
            when grouping(村)=1 and grouping(乡)=1 and grouping(县)=1 then N'合计' end 村,
       case when grouping(xb)=0 then xb  else '合计' end 合计,
       sum(人口数) 人口数
from @t
group by 县,乡,村,xb
with cube
order by grouping(县) desc,县,grouping(乡) desc,乡,grouping(xb) desc,xb
/*
县          乡            村            合计         人口数
---------- ------------ ------------ ---------- -----------
合计         合计           合计           合计         3600
合计         合计           A1           合计         1000
合计         合计           A2           合计         1200
合计         合计           B1           合计         600
合计         合计           B2           合计         800
合计         合计           B1           男          600
合计         合计           A2           男          600
合计         合计           合计           男          1700
合计         合计           A1           男          500
合计         合计           合计           女          1900
合计         合计           A1           女          500
合计         合计           A2           女          600
合计         合计           B2           女          800
合计         A1           A1           合计         500
合计         A1           A2           合计         600
合计         A1           NULL         合计         1100
合计         A1           A2           男          600
合计         A1           A1           男          500
合计         A1           NULL         男          1100
合计         A2           A1           合计         500
合计         A2           A2           合计         600
合计         A2           NULL         合计         1100
合计         A2           A2           女          600
合计         A2           A1           女          500
合计         A2           NULL         女          1100
合计         B1           B1           合计         600
合计         B1           B2           合计         800
合计         B1           NULL         合计         1400
合计         B1           B1           男          600
合计         B1           NULL         男          600
合计         B1           NULL         女          800
合计         B1           B2           女          800
A          A合计          合计           合计         2200
A          A合计          A1           合计         1000
A          A合计          A2           合计         1200
A          A合计          合计           男          1100
A          A合计          A2           男          600
A          A合计          A1           男          500
A          A合计          A1           女          500
A          A合计          A2           女          600
A          A合计          合计           女          1100
A          A1           A1           合计         500
A          A1           A2           合计         600
A          A1           A1合计         合计         1100
A          A1           A2           男          600
A          A1           A1           男          500
A          A1           A1合计         男          1100
A          A2           A1           合计         500
A          A2           A2           合计         600
A          A2           A2合计         合计         1100
A          A2           A2           女          600
A          A2           A1           女          500
A          A2           A2合计         女          1100
B          B合计          B1           合计         600
B          B合计          B2           合计         800
B          B合计          合计           合计         1400
B          B合计          合计           男          600
B          B合计          B1           男          600
B          B合计          B2           女          800
B          B合计          合计           女          800
B          B1           B2           合计         800
B          B1           B1合计         合计         1400
B          B1           B1           合计         600
B          B1           B1           男          600
B          B1           B1合计         男          600
B          B1           B1合计         女          800
B          B1           B2           女          800(67 行受影响)*/

Answer 6

谢谢您的及时回复，有个问题补充一下：县、乡、村存在匹配(隶属)关系，如下：
县    乡   村   性别 人口数 
合计 合计 合计  合计  3600 
合计 合计 合计   男   2000  
合计 合计 合计   女   1600 
A    合计 合计   合计 2200 
A    合计 合计   男   1360  
A    合计 合计   女   860  
A    A1   合计   合计 1100 
A    A1   合计   男   650 
A    A1   合计   女   550 
A    A1   A1     合计 500 
A    A1   A1     男   300 
A    A1   A1     女   200 
A    A1   A2     合计 600 
A    A1   A2     男   350 
A    A1   A2     女   250 
非常感谢！！！

Answer 7

B    合计 合计   合计 2200 
B    合计 合计   男   1360  
B    合计 合计   女   860  
B    B1   合计   合计 1100 
B    B1   合计   男   650 
B    B1   合计   女   550 
B    B1   B1     合计 500 
B    B1   B1     男   300 
B    B1   B1     女   200 
B    B2   合计   合计  800 
B    B2   合计   男  420 
B    B2   合计   女  380 
B    B2    B1     合计 800 
B    B2    B1   男    420 
B    B2    B1   女    380 

Answer 8

B    合计 合计   合计 2200 
B    合计 合计   男   1360  
B    合计 合计   女   860  
B    B1   合计   合计 1100 
B    B1   合计   男   650 
B    B1   合计   女   550 
B    B1   B1     合计 500 
B    B1   B1     男   300 
B    B1   B1     女   200 
B    B2   合计   合计  800 
B    B2   合计   男  420 
B    B2   合计   女  380 
B    B2    B1     合计 800 
B    B2    B1   男    420 
B    B2    B1   女    380 谢谢：）