22,207
社区成员
发帖
与我相关
我的任务
分享
--辅助SQL(包括数据初始化,可直接执行看结果更清晰)
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
IF OBJECT_ID('[tb_item]') IS NOT NULL DROP TABLE [tb_item]
--奖金项表
create table tb_item (itemid nvarchar(50),name nvarchar(50))
insert into tb_item
select '1','A奖金' union all
select '2','B奖金' union all
select '3','C奖金'
--select * from tb_item
--奖金信息表
create table tb (itemid nvarchar(50),cid nvarchar(50),amount decimal)
insert into tb
select '1','c1',50 union all
select '2','c1',30 union all
select '3','c1',20 union all
select '1','c2',25 union all
select '2','c2',10
--select * from tb
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + name from tb_item group by name
exec('
select * from (select x.cid,x.amount,i.name from tb x
inner join tb_item i on x.itemid=i.itemid) tb_r pivot (max(amount) for name in ('+@sql+')) t')
--目前以上语句已经能实现以下结果
select 'c1' as cid,'50' as A奖金,'30' as B奖金,'20' as C奖金 union all
select 'c2','25','10',null
--需要达到的目标结果
select 'c1' as cid,'50' as A奖金,'30' as B奖金,'20' as C奖金,'100' as total union all
select 'c2','25','10','0','35'
--奖金项表
create table tb_item (itemid nvarchar(50),name nvarchar(50))
insert into tb_item
select '1','A奖金' union all
select '2','B奖金' union all
select '3','C奖金'
--select * from tb_item
--奖金信息表
create table tb (itemid nvarchar(50),cid nvarchar(50),amount decimal)
insert into tb
select '1','c1',50 union all
select '2','c1',30 union all
select '3','c1',20 union all
select '1','c2',25 union all
select '2','c2',10
select * from
(select x.cid,sum(x.amount) amount,isnull(i.name,'合计') name
from tb x
inner join
tb_item i on x.itemid=i.itemid
group by x.cid,i.name with rollup) tb_r
pivot (max(amount) for name in (A奖金,B奖金,C奖金,合计)) t
--where cid is not null
order by cid desc
drop table tb_item,tb
/*************
cid A奖金 B奖金 C奖金 合计
-------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
c2 25 10 NULL 35
c1 50 30 20 100
NULL NULL NULL NULL 135
(3 行受影响)
--奖金项表
create table tb_item (itemid nvarchar(50),name nvarchar(50))
insert into tb_item
select '1','A奖金' union all
select '2','B奖金' union all
select '3','C奖金'
--select * from tb_item
--奖金信息表
create table tb (itemid nvarchar(50),cid nvarchar(50),amount decimal)
insert into tb
select '1','c1',50 union all
select '2','c1',30 union all
select '3','c1',20 union all
select '1','c2',25 union all
select '2','c2',10
--select * from tb
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + name from tb_item group by name
select @sql = @sql + ',合计'
exec('
select * from (select x.cid,sum(x.amount) amount,isnull(i.name,''合计'') name from tb x
inner join tb_item i on x.itemid=i.itemid group by x.cid,i.name with rollup) tb_r pivot (max(amount) for name in ('+@sql+')) t where cid is not null')
drop table tb_item,tb
/**********
cid A奖金 B奖金 C奖金 合计
-------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
c1 50 30 20 100
c2 25 10 NULL 35
(2 行受影响)