希望能达到如下效果
类型 分值
1 5
2 10
3 20姓名 类型
A 1
A 1
A 2
B 2
C 3
C 2~~~~~~~~~~~~~~~ 1 2 3 sum
A 10 10 20
B 10 10
C 10 20 30
类型 分值
1 5
2 10
3 20姓名 类型
A 1
A 1
A 2
B 2
C 3
C 2~~~~~~~~~~~~~~~ 1 2 3 sum
A 10 10 20
B 10 10
C 10 20 30
sum(case 类型 when 2 then 分值 else 0 end)[2],
sum(case 类型 when 3 then 分值 else 0 end)[3],sum(分值) [sum]
from (select b.*,a.分值 from tbB b left join tbA a on a.类型=b.类型)mm group by 姓名
insert into T1
select 1,5 union all
select 2,10 union all
select 3,20create table T2(name varchar(10),type int)
insert into T2
select 'A',1 union all
select 'A',1 union all
select 'A',2 union all
select 'B',2 union all
select 'C',3 union all
select 'C',2 --
--動態語句:
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when type='+ltrim(type)+' then num end) as ['+ltrim(type)+']'
from (select T2.*,isnull(T1.num,0) as num from T2 left join T1 on T2.type=T1.type) T
group by type
select @sql='select name'+@sql+',sum(num) as [sum] from (select T2.*,isnull(T1.num,0) as num from T2 left join T1 on T2.type=T1.type) T group by name '
exec(@sql)
/*
name 1 2 3 sum
---------- ----------- ----------- ----------- -----------
A 10 10 20
B 10 10
C 10 20 30
*/drop table T1,T2
drop table tb1
go
create table tb1(类型 int,分值 int)
insert into tb1(类型,分值) values(1, 5)
insert into tb1(类型,分值) values(2, 10)
insert into tb1(类型,分值) values(3, 20)
goif object_id('pubs..tb2') is not null
drop table tb2
gocreate table tb2(姓名 varchar(10),类型 int)
insert into tb2(姓名,类型) values('A', 1)
insert into tb2(姓名,类型) values('A', 1)
insert into tb2(姓名,类型) values('A', 2)
insert into tb2(姓名,类型) values('B', 2 )
insert into tb2(姓名,类型) values('C', 3)
insert into tb2(姓名,类型) values('C', 2)
godeclare @sql varchar(8000)
set @sql = 'select 姓名'
select @sql = @sql + ' , sum(case 类型 when ''' + cast(类型 as varchar) + ''' then 分值 else 0 end) [' + cast(类型 as varchar) + ']'
from (select distinct 类型 from (select tb2.*, tb1.分值 from tb2,tb1 where tb2.类型 = tb1.类型) t) as a
set @sql = @sql + ' ,sum(分值) as [sum] from (select tb2.*, tb1.分值 from tb2,tb1 where tb2.类型 = tb1.类型) t group by 姓名'
exec(@sql) drop table tb1,tb2/*
姓名 1 2 3 sum
---------- ----------- ----------- ----------- -----------
A 10 10 0 20
B 0 10 0 10
C 0 10 20 30
*/
A 1 5
A 1 10
A 2 10
B 2 10
B 2 30
C 3 5
C 2 30输出结果
~~~~~~~~~~~~~~~ 1 2 3 sum
A 15 10 25
B 40 40
C 30 5 35
create table T1(type int,num int)
insert into T1
select 1,5 union all
select 2,10 union all
select 3,20create table T2(name varchar(10),type int)
insert into T2
select 'A',1 union all
select 'A',1 union all
select 'A',2 union all
select 'B',2 union all
select 'C',3 union all
select 'C',2
select t2.name,sum(case when t1.type=1 then num else 0 end) as '1',
sum(case when t1.type=2 then num else 0 end) as '2',sum(case when t1.type=3 then num else 0 end) as '3',sum(num) as 分值 from t1,t2
where t1.type=t2.type
group by t2.name
insert into T1
select 1,5 union all
select 2,10 union all
select 3,20create table T2(name varchar(10),type int)
insert into T2
select 'A',1 union all
select 'A',1 union all
select 'A',2 union all
select 'B',2 union all
select 'C',3 union all
select 'C',2 /*
1 2 3 sum
A 10 10 20
B 10 10
C 10 20 30*/
select [类型]=a.[name],
[1]=sum(case a.type when 1 then b.num else 0 end),
[2]=sum(case a.type when 2 then b.num else 0 end),
[3]=sum(case a.type when 3 then b.num else 0 end),
[1]=sum(b.num)
from t2 a,t1 b where a.type=b.type group by a.[name]
drop table tb2
gocreate table tb2(姓名 varchar(10),类型 int,分值 int)
insert into tb2(姓名,类型,分值) values('A', 1, 5)
insert into tb2(姓名,类型,分值) values('A', 1, 10)
insert into tb2(姓名,类型,分值) values('A', 2, 10)
insert into tb2(姓名,类型,分值) values('B', 2, 10)
insert into tb2(姓名,类型,分值) values('B', 2, 30)
insert into tb2(姓名,类型,分值) values('C', 3, 5)
insert into tb2(姓名,类型,分值) values('C', 2, 30)
godeclare @sql varchar(8000)
set @sql = 'select 姓名'
select @sql = @sql + ' , sum(case 类型 when ''' + cast(类型 as varchar) + ''' then 分值 else 0 end) [' + cast(类型 as varchar) + ']'
from (select distinct 类型 from tb2 ) as a
set @sql = @sql + ' ,sum(分值) as [sum] from tb2 group by 姓名'
exec(@sql) drop table tb2/*
姓名 1 2 3 sum
---------- ----------- ----------- ----------- -----------
A 15 10 0 25
B 0 40 0 40
C 0 30 5 35
*/
insert into T1
select 'A',1,5 union all
select 'A',1,10 union all
select 'A',2,10 union all
select 'B',2,10 union all
select 'B',2,30 union all
select 'C',3,5 union all
select 'C',2,30
/*
输出结果
~~~~~~~~~~~~~~~ 1 2 3 sum
A 15 10 25
B 40 40
C 30 5 35
*/
select a.姓名,
[1]=sum(case a.类型 when 1 then a.分值 else 0 end),
[2]=sum(case a.类型 when 2 then a.分值 else 0 end),
[3]=sum(case a.类型 when 3 then a.分值 else 0 end),
[sum]=sum(a.分值)
from t1 a group by a.姓名drop table t1
create table T1(type int,num int)
insert into T1
select 1,5 union all
select 2,10 union all
select 3,20create table T2(name varchar(10),type int)
insert into T2
select 'A',1 union all
select 'A',1 union all
select 'A',2 union all
select 'B',2 union all
select 'C',3 union all
select 'C',2 declare @sql varchar(1000)
set @sql='select t2.name '
select @sql=@sql+ ', sum(case when t1.type='''+rtrim(t1.type)+ ''' then num else 0 end ) as 类型'+rtrim(t1.type)
from t1
set @sql=@sql+' ,sum(num) as 分值 from t1,t2 where t1.type=t2.type group by t2.name'
exec (@sql) name 类型1 类型2 类型3 分值
---------- ----------- ----------- ----------- -----------
A 10 10 0 20
B 0 10 0 10
C 0 10 20 30
create table t3(name varchar(10),type varchar(10),num int)
insert into t3 select 'A','1',5
insert into t3 select 'A','1',10
insert into t3 select 'A','2',10
insert into t3 select 'B','2',10
insert into t3 select 'B','2',30
insert into t3 select 'C','3',5
insert into t3 select 'C','2',30declare @sql varchar(1000)
set @sql='select name '
select @sql=@sql+' , sum(case when type='''+type+''' then num else 0 end ) as 类型'+rtrim(type)
from (select distinct type from t3 ) aaset @sql=@sql+' ,sum(num) as 分值 from T3 group by name'
exec(@sql) name 类型1 类型2 类型3 分值
---------- ----------- ----------- ----------- -----------
A 15 10 0 25
B 0 40 0 40
C 0 30 5 35