3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
如何实现“多层”交叉报表，即按多个字段交叉查询。
如何实现“多层”交叉报表，即按多个字段交叉查询。例如：
编号 地点 月份 价格
---- ---- ----------- -----------
A002 国内 10 200
A005 国内 10 500
A001 国外 10 100
A003 国外 10 300
A004 国外 10 400
A002 国内 11 200
A005 国内 11 500
A003 国外 11 300
A004 国外 11 400
希望能按
[编号] [国外_10月份], [国外_11月份],[国内_10月份],[国内_11月份]
的形式交叉统计 [价格],
即按照 [地点] 和 [月份] 多层交叉统计商品销售额。
测试数据如下：
Select 'A001' As [编号], '国外' As [地点], 10 As [月份], 100 As [价格]
Union
Select 'A002' As [编号], '国内' As [地点], 10 As [月份], 200 As [价格]
Union
Select 'A003' As [编号], '国外' As [地点], 10 As [月份], 300 As [价格]
Union
Select 'A004' As [编号], '国外' As [地点], 10 As [月份], 400 As [价格]
Union
Select 'A005' As [编号], '国内' As [地点], 10 As [月份], 500 As [价格]
Union
Select 'A002' As [编号], '国内' As [地点], 11 As [月份], 200 As [价格]
Union
Select 'A003' As [编号], '国外' As [地点], 11 As [月份], 300 As [价格]
Union
Select 'A004' As [编号], '国外' As [地点], 11 As [月份], 400 As [价格]
Union
Select 'A005' As [编号], '国内' As [地点], 11 As [月份], 500 As [价格]
Order By [月份],[地点],[编号]
编号 地点 月份 价格
---- ---- ----------- -----------
A002 国内 10 200
A005 国内 10 500
A001 国外 10 100
A003 国外 10 300
A004 国外 10 400
A002 国内 11 200
A005 国内 11 500
A003 国外 11 300
A004 国外 11 400
希望能按
[编号] [国外_10月份], [国外_11月份],[国内_10月份],[国内_11月份]
的形式交叉统计 [价格],
即按照 [地点] 和 [月份] 多层交叉统计商品销售额。
测试数据如下：
Select 'A001' As [编号], '国外' As [地点], 10 As [月份], 100 As [价格]
Union
Select 'A002' As [编号], '国内' As [地点], 10 As [月份], 200 As [价格]
Union
Select 'A003' As [编号], '国外' As [地点], 10 As [月份], 300 As [价格]
Union
Select 'A004' As [编号], '国外' As [地点], 10 As [月份], 400 As [价格]
Union
Select 'A005' As [编号], '国内' As [地点], 10 As [月份], 500 As [价格]
Union
Select 'A002' As [编号], '国内' As [地点], 11 As [月份], 200 As [价格]
Union
Select 'A003' As [编号], '国外' As [地点], 11 As [月份], 300 As [价格]
Union
Select 'A004' As [编号], '国外' As [地点], 11 As [月份], 400 As [价格]
Union
Select 'A005' As [编号], '国内' As [地点], 11 As [月份], 500 As [价格]
Order By [月份],[地点],[编号]
...全文
请发表友善的回复…
发表回复
SunWinter 2003-12-26
太感谢大家了，我没想到在case when中使用And, 我还准备用程序来实现呢。
现在就给分。再次感谢大家。
现在就给分。再次感谢大家。
erigido 2003-12-26
declare @t table(id nvarchar(20),area nvarchar(20),month nvarchar(20),price nvarchar(20))
insert into @t
Select 'A001' , '国外' , 10 , 100
Union all
Select 'A002' , '国内' , 10 , 200
Union all
Select 'A003' , '国外' , 10 , 300
Union
Select 'A004' , '国外' , 10 , 400
Union
Select 'A005' , '国内' , 10 , 500
Union
Select 'A002' , '国内' , 11 , 200
Union
Select 'A003' , '国外' , 11 , 300
Union
Select 'A004' , '国外' , 11 , 400
Union
Select 'A005' , '国内' , 11 , 500
select id,
sum (case when area='国外' and month= 10 then price else 0 end) as 国外_10月份,
sum (case when area='国外' and month= 11 then price else 0 end) as 国外_11月份,
sum (case when area='国内' and month= 10 then price else 0 end) as 国内_10月份,
sum (case when area='国内' and month= 11 then price else 0 end) as 国内_11月份
from @t
group by id
insert into @t
Select 'A001' , '国外' , 10 , 100
Union all
Select 'A002' , '国内' , 10 , 200
Union all
Select 'A003' , '国外' , 10 , 300
Union
Select 'A004' , '国外' , 10 , 400
Union
Select 'A005' , '国内' , 10 , 500
Union
Select 'A002' , '国内' , 11 , 200
Union
Select 'A003' , '国外' , 11 , 300
Union
Select 'A004' , '国外' , 11 , 400
Union
Select 'A005' , '国内' , 11 , 500
select id,
sum (case when area='国外' and month= 10 then price else 0 end) as 国外_10月份,
sum (case when area='国外' and month= 11 then price else 0 end) as 国外_11月份,
sum (case when area='国内' and month= 10 then price else 0 end) as 国内_10月份,
sum (case when area='国内' and month= 11 then price else 0 end) as 国内_11月份
from @t
group by id
azsoft 2003-12-26
declare @t table (编号 varchar(10),地点 varchar(10),月份 varchar(10),价格 varchar(10))
insert into @t
Select 'A001' As [编号], '国外' As [地点], 10 As [月份], 100 As [价格]
Union
Select 'A002' As [编号], '国内' As [地点], 10 As [月份], 200 As [价格]
Union
Select 'A003' As [编号], '国外' As [地点], 10 As [月份], 300 As [价格]
Union
Select 'A004' As [编号], '国外' As [地点], 10 As [月份], 400 As [价格]
Union
Select 'A005' As [编号], '国内' As [地点], 10 As [月份], 500 As [价格]
Union
Select 'A002' As [编号], '国内' As [地点], 11 As [月份], 200 As [价格]
Union
Select 'A003' As [编号], '国外' As [地点], 11 As [月份], 300 As [价格]
Union
Select 'A004' As [编号], '国外' As [地点], 11 As [月份], 400 As [价格]
Union
Select 'A005' As [编号], '国内' As [地点], 11 As [月份], 500 As [价格]
Order By [月份],[地点],[编号]
select 编号,
sum(case when 地点='国外' and 月份= 10 then 价格 else 0 end) as 国外_10月份,
sum(case when 地点='国外' and 月份= 11 then 价格 else 0 end) as 国外_11月份,
sum(case when 地点='国内' and 月份= 10 then 价格 else 0 end) as 国内_10月份,
sum(case when 地点='国内' and 月份= 11 then 价格 else 0 end) as 国内_11月份
from @t
group by 编号
bobohuang 2003-12-26
select 编号，
sum (case when 地点='国外' and 月份= 10 then 价格 else o end) as 国外_10月份,
sum (case when 地点='国外' and 月份= 11 then 价格 else o end) as 国外_11月份,
sum (case when 地点='国内' and 月份= 10 then 价格 else o end) as 国内_10月份,
sum (case when 地点='国内' and 月份= 11 then 价格 else o end) as 国内_11月份
from table
group by 编号
sum (case when 地点='国外' and 月份= 10 then 价格 else o end) as 国外_10月份,
sum (case when 地点='国外' and 月份= 11 then 价格 else o end) as 国外_11月份,
sum (case when 地点='国内' and 月份= 10 then 价格 else o end) as 国内_10月份,
sum (case when 地点='国内' and 月份= 11 then 价格 else o end) as 国内_11月份
from table
group by 编号
realgz 2003-12-26
select 编号，
sum (case when 地点='国外' and 月份= 10 then 价格 else o end) as 国外_10月份,
sum (case when 地点='国外' and 月份= 11 then 价格 else o end) as 国外_11月份,
sum (case when 地点='国内' and 月份= 10 then 价格 else o end) as 国内_10月份,
sum (case when 地点='国内' and 月份= 11 then 价格 else o end) as 国内_11月份
from table
group by 编号
sum (case when 地点='国外' and 月份= 10 then 价格 else o end) as 国外_10月份,
sum (case when 地点='国外' and 月份= 11 then 价格 else o end) as 国外_11月份,
sum (case when 地点='国内' and 月份= 10 then 价格 else o end) as 国内_10月份,
sum (case when 地点='国内' and 月份= 11 then 价格 else o end) as 国内_11月份
from table
group by 编号
realgz 2003-12-26
try:
select 编号，
sum (when 地点='国外' and 月份= 10 then 价格 else o end) as 国外_10月份,
sum (when 地点='国外' and 月份= 11 then 价格 else o end) as 国外_11月份,
sum (when 地点='国内' and 月份= 10 then 价格 else o end) as 国内_10月份,
sum (when 地点='国内' and 月份= 11 then 价格 else o end) as 国内_11月份
from table
group by 编号
select 编号，
sum (when 地点='国外' and 月份= 10 then 价格 else o end) as 国外_10月份,
sum (when 地点='国外' and 月份= 11 then 价格 else o end) as 国外_11月份,
sum (when 地点='国内' and 月份= 10 then 价格 else o end) as 国内_10月份,
sum (when 地点='国内' and 月份= 11 then 价格 else o end) as 国内_11月份
from table
group by 编号