f交叉表查询问题
我想把表
乡镇代码 村代码 地类 面积
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
以下略
............................... ....
可以通过 以下实现
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
现在我想通过另一个地名表, 交叉统计,把上面的表乡镇代码,村码改为乡镇名,村名.
地名表为:
乡镇名 村名 乡镇代码 村代码
小石 小砍 10 1
小石 围成 10 2
南成 塘下 20 1
惠东 惠村 30 1
新表结构为
统计单位 地类 面积
合计 1.9 //按地类合计
有林地 0.8
非林地 1.1
小石 合计 1.2 //分镇统计/按地类
有林地 0.1
非林地 1.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.3
DECLARE @tb1 TABLE(
乡镇代码 int,
村代码 int,
乡镇名 nvarchar(10),
村名 nvarchar(10))
INSERT @tb1
SELECT 10, 1, '小石 ', '小砍 ' UNION ALL
SELECT 10, 1, '小石 ', '围成 ' 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.3
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, '惠东 ', '惠村 '
select * from
(
select ' ' as 统计单位, '合计 ' as 地类,sum(面积) as 面积
from @tb a,@tb1 b
where a.乡镇代码=b.乡镇代码
and a.村代码=b.村代码
union all
select ' ' as 统计单位,地类,sum(面积)
from @tb a,@tb1 b
where a.乡镇代码=b.乡镇代码
and a.村代码=b.村代码
group by 地类
union all
select 乡镇名 as 统计单位, '合计 ' as 地类,sum(面积)
from @tb a,@tb1 b
where a.乡镇代码=b.乡镇代码
and a.村代码=b.村代码
group by 乡镇名
union all
select 乡镇名 as 统计单位,地类,sum(面积)
from @tb a,@tb1 b
where a.乡镇代码=b.乡镇代码
and a.村代码=b.村代码
group by 乡镇名,地类
) as t
order by 统计单位,case when 地类= '合计 ' then 0 else 1 end
-- 结果
统计单位 地类 面积
---------- ---------- ----------------------------------------
合计 1.9
非林地 1.1
有林地 .8
惠东 合计 .3
惠东 有林地 .3
南成 合计 .4
南成 有林地 .4
小石 合计 1.2
小石 非林地 1.1
小石 有林地 .1
（所影响的行数为 10 行）
[解决办法]
http://topic.csdn.net/t/20061010/21/5073353.html
[解决办法]
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.3
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, '惠东 ', '惠村 '
---借助一下临时表
If Object_ID( 'Tempdb..# ') Is Not Null
Drop Table #
Select * Into #
From (
Select ' ' As 统计单位,
Case When Grouping(地类)=1 Then '合计 '
Else IsNull(地类, ' ') End As '地类 ',
Sum(面积) As '面积 '
From @tb Group By 地类 With Rollup
Union All
Select
Case When Grouping(B.乡镇名)=1 Then '分乡镇合计 '
Else IsNull(B.乡镇名, ' ') End As '乡镇名 ',
Case When Grouping(A.地类)=1 Then '合计 '
Else IsNull(A.地类, ' ') End As '地类 ',
Sum(A.面积) As 面积
From (Select 乡镇代码,地类,Sum(面积) As 面积 From @tb Group By 乡镇代码,地类) As A,
(Select 乡镇代码,乡镇名 From @tb1 Group By 乡镇代码,乡镇名) As B
Where A.乡镇代码=B.乡镇代码
Group By B.乡镇名,A.地类 With Rollup
Union All
Select
Case When Grouping(B.村名)=1 Then '分乡镇再分村总计 '
Else IsNull(B.村名, ' ') End As '乡镇名 ',
Case When Grouping(A.地类)=1 Then '合计 '
Else IsNull(A.地类, ' ') End As '地类 ',
Sum(A.面积) As 面积
From (Select 乡镇代码,村代码,地类,Sum(面积) As 面积 From @tb Group By 乡镇代码,村代码,地类) As A,
(Select 乡镇代码,村代码,村名 From @tb1 Group By 乡镇代码,村代码,村名) As B
Where A.乡镇代码=B.乡镇代码 And A.村代码=B.村代码
Group By B.村名,A.地类 With Rollup
) T
Where 统计单位 <> '分乡镇合计 ' And 统计单位 <> '分乡镇再分村总计 '
Order By 统计单位,Case When 地类= '合计 ' Then 0 Else 1 End
---查询结果
Select Case When 地类= '合计 ' Then 统计单位 Else ' ' End As 统计单位,
地类,
面积
From #
---结果
/*
统计单位 地类 面积
---------------- ---------- ------------
合计 1.9
非林地 1.1
有林地 .8
惠村 合计 .3
有林地 .3
惠东 合计 .3
有林地 .3
南成 合计 .4
有林地 .4
塘下 合计 .4
有林地 .4
围成 合计 .7
非林地 .7
小砍 合计 .5
非林地 .4
有林地 .1
小石 合计 1.2
有林地 .1
非林地 1.1
（所影响的行数为 19 行）
*/
