复杂统计,请高手帮忙,谢谢!

Question

我想完成一下统计:
  乡镇代码   村代码   地类     面积 
       20        1   有林地    0.4
       10        2   非林地    0.7
      10         1    有林地    0.1
      10         1    非林地     0.4
       30          1   有林地     0.3
 统计结果:
乡镇代码          地类     面积
合计              合计       1.9 //按地类合计
                  有林地     0.8
                  非林地     1.1
 10                 合计       1.2  //分镇统计/按地类
                  有林地      0.1
                  非林地      1.1
 20                  合计       0.4
                   有林地      0.4
 30                 合计        0.3
                   有林地      0.3
 10                合计       1.2  //分10镇统计/在分村统计
                  有林地      0.1
                  非林地      1.1
 1                 合计       0.5
                   有林地     0.1
                    非林地    0.4
 2                  合计      0.7
                     非林地   0.7
 20                 合计       0.4 //分20镇统计/在分村统计
                   有林地      0.4
 1                   合计       0.4
                    有林地      0.4
 30                 合计        0.3 //分30镇统计/在分村
                     有林地      0.3
  1                  合计       0.4
                      有林地      0.4
  以上统计比较罗嗦,按乡镇名和村名排序统计,我试图通过CASE语句和ROLLUP语句,但总是得不到要求的结果,请高手帮忙.多谢.  
                 
 
  

Answer 1

-- 测试数据
DECLARE @tb TABLE(
乡镇代码 varchar(10),
村代码 varchar(10),
地类 nvarchar(10),
面积 decimal(10,1))
INSERT @tb
SELECT 20, 1, N'有林地', 0.4 UNION ALL
SELECT 10, 2, N'非林地', 0.7 UNION ALL
SELECT 10, 1, N'有林地', 0.1 UNION ALL
SELECT 10, 1, N'非林地', 0.4 UNION ALL
SELECT 30, 1, N'有林地', 0.3-- 统计
SELECT
乡镇代码 = CASE
WHEN IsGroup = 0 THEN 乡镇代码
ELSE N'' END,
地类,
面积
FROM(
SELECT
乡镇代码 = CASE
WHEN GROUPING(地类) = 0 THEN N''
ELSE N'合计' END,
地类 = CASE
WHEN GROUPING(地类) = 0 THEN 地类
ELSE N'合计' END,
面积 = SUM(面积),
IsGroup = 0,
ord0 = 1,
ord1 = NULL, ord2 = NULL, ord3 = GROUPING(地类), ord4= 地类
FROM @tb
GROUP BY 地类 WITH ROLLUP UNION ALL SELECT
乡镇代码,
地类= CASE
WHEN GROUPING(地类) = 0 THEN 地类
ELSE N'合计' END,
面积 = SUM(面积),
IsGroup = 1 - GROUPING(地类),
ord0 = 2,
ord1 = 乡镇代码, ord2 = NULL, ord3 = GROUPING(地类), ord4= 地类
FROM @tb
GROUP BY 乡镇代码, 地类 WITH ROLLUP
HAVING GROUPING(乡镇代码) = 0 UNION ALL SELECT
乡镇代码,
地类= CASE
WHEN GROUPING(地类) = 0 THEN 地类
ELSE N'合计' END,
面积 = SUM(面积),
IsGroup = 1 - GROUPING(地类),
ord0 = 3,
ord1 = 乡镇代码, ord2 = NULL, ord3 = GROUPING(地类), ord4= 地类
FROM @tb
GROUP BY 乡镇代码, 地类 WITH ROLLUP
HAVING GROUPING(乡镇代码) = 0
UNION ALL
SELECT
乡镇代码 = 村代码,
地类 = CASE
WHEN GROUPING(地类) = 0 THEN 地类
ELSE N'合计' END,
面积 = SUM(面积),
IsGroup = 1 - GROUPING(地类),
ord0 = 3,
ord1 = 乡镇代码, ord2 = 村代码, ord3 = GROUPING(地类), ord4= 地类
FROM @tb
GROUP BY 乡镇代码, 村代码, 地类 WITH ROLLUP
HAVING GROUPING(乡镇代码) = 0
AND GROUPING(村代码) = 0
)A
ORDER BY ord0, ord1, ord2, ord3 DESC, ord4

Answer 2

--借用老大数据
DECLARE @tb TABLE(
乡镇代码 varchar(10),
村代码 varchar(10),
地类 nvarchar(10),
面积 decimal(10,1))
INSERT @tb
SELECT 20, 1, N'有林地', 0.4 UNION ALL
SELECT 10, 2, N'非林地', 0.7 UNION ALL
SELECT 10, 1, N'有林地', 0.1 UNION ALL
SELECT 10, 1, N'非林地', 0.4 UNION ALL
SELECT 30, 1, N'有林地', 0.3-- 统计
select (case when 地类 is null and isnull(村代码,'0')='0' then isnull(乡镇代码,'合计') when  地类 is null and isnull(村代码,'0')<>'0' then 村代码 else '' end)乡镇代码,isnull(地类,'合计')地类,面积 
from (select (case when 乡镇代码 is null and 村代码 is null then 0 else 2 end)id,
     乡镇代码,村代码,地类,sum(面积)面积 from @tb group by 乡镇代码,村代码,地类
     with cube having not(乡镇代码 is null and 村代码 is not null)
     union select 1,乡镇代码,'0' 村代码,地类,sum(面积)面积 from @tb 
     group by 乡镇代码,地类 with rollup having 乡镇代码 is not null
)aa order by id,isnull(乡镇代码,0),isnull(村代码,0),isnull(地类,'最') desc
（所影响的行数为 5 行）乡镇代码       地类         面积                                       
---------- ---------- ------------
合计         合计         1.9
           有林地        .8
           非林地        1.1
10         合计         1.2
           有林地        .1
           非林地        1.1
20         合计         .4
           有林地        .4
30         合计         .3
           有林地        .3
10         合计         1.2
           有林地        .1
           非林地        1.1
1          合计         .5
           有林地        .1
           非林地        .4
2          合计         .7
           非林地        .7
20         合计         .4
           有林地        .4
1          合计         .4
           有林地        .4
30         合计         .3
           有林地        .3
1          合计         .3
           有林地        .3（所影响的行数为 26 行）

Answer 3

谢谢wgzaaa(),zjcxc(邹建),问题解决了,非常感谢!!!!

Answer 4

DECLARE @tb1 TABLE(乡镇代码 int,村代码 int,乡镇名 nvarchar(10),村名 nvarchar(10))
INSERT @tb1 SELECT 10, 1, '小石',   '小砍' 
  UNION ALL SELECT 10, 2, '小石',   '围成'
  UNION ALL SELECT 20, 1, '南成',   '塘下'  
  UNION ALL SELECT 30, 1, '惠东',   '惠村'DECLARE @tb TABLE( 乡镇代码 int , 村代码 int ,地类 nvarchar(10), 面积 decimal(10,1)) 
INSERT @tb SELECT 20, 1, N'有林地', 0.4
 UNION ALL SELECT 10, 2, N'非林地', 0.7
 UNION ALL SELECT 10, 1, N'有林地', 0.1
 UNION ALL SELECT 10, 1, N'非林地', 0.4
 UNION ALL SELECT 30, 1, N'有林地', 0.3select (case when 地类 is null and isnull(aa.村代码,'0')='0' then isnull(乡镇名,'合计') when 地类 is null and isnull(aa.村代码,'0')<>'0' then 村名 else '' end)乡镇代码,isnull(地类,'合计')地类,面积 
from (
--select * from (
select (case when 乡镇代码 is null and 村代码 is null then 0 else 2 end)id,
     rtrim(乡镇代码)乡镇代码,rtrim(村代码)村代码,地类,sum(面积)面积 from @tb group by 乡镇代码,村代码,地类
     with cube having not(乡镇代码 is null and 村代码 is not null)
     union select 1,rtrim(乡镇代码)乡镇代码,'0' 村代码,地类,sum(面积)面积 from @tb 
     group by 乡镇代码,地类 with rollup having 乡镇代码 is not null
)aa left join (select * from @tb1 union select 乡镇代码,0,乡镇名,'' from @tb1 group by 乡镇代码,乡镇名) bb 
on (aa.乡镇代码=bb.乡镇代码 and isnull(aa.村代码,'0')=bb.村代码)
order by id,isnull(aa.乡镇代码,0),isnull(aa.村代码,0),isnull(地类,'最') desc
---------------
乡镇代码       地类         面积                                       
---------- ---------- ---------------------------------------- 
合计         合计         1.9
           有林地        .8
           非林地        1.1
小石         合计         1.2
           有林地        .1
           非林地        1.1
南成         合计         .4
           有林地        .4
惠东         合计         .3
           有林地        .3
小石         合计         1.2
           有林地        .1
           非林地        1.1
小砍         合计         .5
           有林地        .1
           非林地        .4
围成         合计         .7
           非林地        .7
南成         合计         .4
           有林地        .4
塘下         合计         .4
           有林地        .4
惠东         合计         .3
           有林地        .3
惠村         合计         .3
           有林地        .3（所影响的行数为 26 行）

Answer 5

可能错位对你的数据调用更有利，不妨将这句加点东西变成下面那句
isnull(aa.村代码,'0')<>'0' then 村名 else '' end)乡镇代码
isnull(aa.村代码,'0')<>'0' then '  '+村名 else '' end)乡镇代码