复杂统计,请高手帮忙,谢谢!
我想完成一下统计:
乡镇代码 村代码 地类 面积
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语句,但总是得不到要求的结果,请高手帮忙.多谢.
[解决办法]
-- 测试数据
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
[解决办法]
--借用老大数据
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 行）