导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

如何实现“多层”交叉报表,即按多个字段交叉查询。

SunWinter 2003-12-26 05:56:20
如何实现“多层”交叉报表,即按多个字段交叉查询。例如:

编号 地点 月份 价格
---- ---- ----------- -----------
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 [月份],[地点],[编号]


...全文
21 点赞 收藏 6
写回复
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
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
回复
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 编号
回复
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 编号
回复
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 编号
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告