大家进来看看,有点难度噢

Question

create table #tt(province nvarchar(20),city nvarchar(20),town nvarchar(20),quantity int)
insert into #tt values('山东','济南','A县',10)
insert into #tt values('山东','济南','B县',20)
insert into #tt values('山东','济南','C县',30)
insert into #tt values('山东','烟台','A县',20)
insert into #tt values('山东','烟台','B县',20)
insert into #tt values('山东','淄博','A县',12)
insert into #tt values('山东','淄博','B县',22)
insert into #tt values('山东','淄博','C县',32)
insert into #tt values('山东','淄博','D县',22)
insert into #tt values('山东','青岛','A县',15)
insert into #tt values('山东','聊城','A县',20)
insert into #tt values('浙江','杭州','A县',14)
insert into #tt values('浙江','杭州','B县',14)
insert into #tt values('浙江','温州','A县',30)
insert into #tt values('浙江','宁波','A县',50)
insert into #tt values('福建','福州','A县',14)
insert into #tt values('福建','福州','B县',15)
insert into #tt values('福建','福州','C县',17)
insert into #tt values('福建','厦门','A县',30)
insert into #tt values('福建','厦门','B县',40)
--每20条记录为一个分界
insert into #tt values('福建','厦门','C县',35)
insert into #tt values('福建','厦门','D县',20)
insert into #tt values('福建','泉州','A县',50)
insert into #tt values('福建','泉州','B县',50)
insert into #tt values('福建','泉州','C县',50)
insert into #tt values('福建','泉州','D县',50)
insert into #tt values('福建','漳州','A县',30)
insert into #tt values('福建','南平','A县',50)
insert into #tt values('福建','龙岩','A县',30)
insert into #tt values('福建','龙岩','B县',30)
insert into #tt values('福建','宁德','A县',50)
insert into #tt values('福建','石狮','A县',50)
insert into #tt values('福建','石狮','B县',50)
insert into #tt values('广东','广州','A县',50)
insert into #tt values('广东','东莞','A县',30)
insert into #tt values('广东','东莞','B县',30)
insert into #tt values('广东','顺德','A县',50)
insert into #tt values('广东','顺德','B县',40)
go--显示处理
select id=identity(int,0,1),* into # from #tt 
--order by province,city,town --(如果表中记录没有排序好的,则要加上排序语句)
select province=case when id1=cnt1 then province else '' end
,city=case when id2=cnt2 then city else '' end
,town,quantity
from(
select province,city,town,quantity
,id1=(select count(*) from # where id/20=a.id/20 and province=a.province and id<=a.id)
,cnt1=((select count(*) from # where id/20=a.id/20 and province=a.province)+1)/2
,id2=(select count(*) from # where id/20=a.id/20 and province=a.province and city=a.city and id<=a.id)
,cnt2=((select count(*) from # where id/20=a.id/20 and province=a.province and city=a.city)+1)/2
from # a)a
drop table #
godrop table #tt/*--测试结果province             city                 town                 quantity    
-------------------- -------------------- -------------------- ----------- 
                                          A县                   10
                     济南                   B县                   20
                                          C县                   30
                     烟台                   A县                   20
                                          B县                   20
山东                                        A县                   12
                     淄博                   B县                   22
                                          C县                   32
                                          D县                   22
                     青岛                   A县                   15
                     聊城                   A县                   20
                     杭州                   A县                   14
浙江                                        B县                   14
                     温州                   A县                   30
                     宁波                   A县                   50
                                          A县                   14
                     福州                   B县                   15
福建                                        C县                   17
                     厦门                   A县                   30
                                          B县                   40
                     厦门                   C县                   35
                                          D县                   20
                                          A县                   50
                     泉州                   B县                   50
                                          C县                   50
                                          D县                   50
福建                   漳州                   A县                   30
                     南平                   A县                   50
                     龙岩                   A县                   30
                                          B县                   30
                     宁德                   A县                   50
                     石狮                   A县                   50
                                          B县                   50
                     广州                   A县                   50
                     东莞                   A县                   30
广东                                        B县                   30
                     顺德                   A县                   50
                                          B县                   40（所影响的行数为 38 行）
--*/