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

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


...全文
103 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
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 编号

34,837

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧