34,591
社区成员
发帖
与我相关
我的任务
分享
use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
地区 char(10),
国家 char(10),
书名 char(10),
价格 int
)
go
--插入测试数据
insert into tb select '一山','KOR','java',20
union all select '一山','KOR','java',10
union all select '一山','KOR','vb',10
union all select '上海','CHINA','java',10
union all select '上海','CHINA','java',30
union all select '上海','CHINA','vb',10
union all select '因拉','印度','java',20
union all select '迪拉','印度','vb',10
union all select '沙拉','沙特','java',10
go
--代码实现
declare @sql varchar(max)
select @sql=isnull(@sql+',','')+rtrim(国家)+'=(case 国家 when '''+rtrim(国家)+''' then 价格 else 0 end)'
from ( select distinct 国家 from tb )t
exec('select 地区,书名,'+@sql+',统计=价格 from (
select 地区,国家,书名,价格=sum(价格) from tb group by 地区,国家,书名 )t')
/*测试结果
地区 书名 CHINA KOR 沙特 印度 统计
---------------------------------------------------
迪拉 vb 0 0 0 10 10
沙拉 java 0 0 10 0 10
上海 java 40 0 0 0 40
上海 vb 10 0 0 0 10
一山 java 0 30 0 0 30
一山 vb 0 10 0 0 10
因拉 java 0 0 0 20 20
(7 行受影响)
*/
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([地区])+'=max(case when [地区]='+quotename([地区],'''')+' then [价格] else 0 end)'
from tb group by [地区]
exec('select [地区]'+@s+',[统计]=sum([价格]) from tb group by [地区] with rollup
'
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([地区])+'=max(case when [地区]='+quotename([地区],'''')+' then [价格] else 0 end)'
from tb group by[地区] with rollup
exec('select [地区]'+@s+',[统计]=sum([价格]) from tb group by [地区]'
create table #temp
(
地区 nvarchar(10),
国家 nvarchar(10),
书名 nvarchar(10),
价格 int
)
insert #temp
select '一山', 'KOR', 'java', '20' union all
select '一山', 'KOR', 'java', '10' union all
select '一山', 'KOR', 'vb', '10' union all
select '上海', 'CHINA', 'java', '10' union all
select '上海', 'CHINA', 'java', '30' union all
select '上海', 'CHINA', 'vb', '10' union all
select '因拉', '印度', 'java', '20' union all
select '迪拉', '印度', 'vb', '10' union all
select '沙拉', '沙特', 'java', '10'
go
--SQL:仅做参考
select
地区,
书名,
[KOR] = ISNULL([KOR], 0),
[CHINA] = ISNULL([CHINA], 0),
[印度] = ISNULL([印度], 0),
[沙特] = ISNULL([沙特], 0),
统计 = ISNULL([KOR], 0) + ISNULL([CHINA], 0) + ISNULL([印度], 0) + ISNULL([沙特], 0)from
(select 地区,国家, 书名,价格 = SUM(价格) from #temp group by 地区,国家,书名) a
pivot
(max(价格) for 国家 in([KOR], [CHINA], [印度], [沙特])) b
order by 地区, 书名
/*
地区 书名 KOR CHINA 印度 沙特 统计
迪拉 vb 0 0 10 0 10
沙拉 java 0 0 0 10 10
上海 java 0 40 0 0 40
上海 vb 0 10 0 0 10
一山 java 30 0 0 0 30
一山 vb 10 0 0 0 10
因拉 java 0 0 20 0 20
*/