有两个表:
表A包含ID、姓名、Email等基本信息
表B包含每人人选的课和成绩(ID、课程、成绩),每个人可能有0到6条
【注意课程很多,远远大于6种】现在要输出的结构是:
ID、姓名、Email、课程1、成绩1、课程2、成绩2、课程3、成绩3、课程4、成绩4、课程5、成绩5、课程6、成绩6求教怎么写查询?
存储过程或者借助临时表都行!
表A包含ID、姓名、Email等基本信息
表B包含每人人选的课和成绩(ID、课程、成绩),每个人可能有0到6条
【注意课程很多,远远大于6种】现在要输出的结构是:
ID、姓名、Email、课程1、成绩1、课程2、成绩2、课程3、成绩3、课程4、成绩4、课程5、成绩5、课程6、成绩6求教怎么写查询?
存储过程或者借助临时表都行!
insert into test select 'A店','1',100
insert into test select 'A店','2',100
insert into test select 'A店','3',100
insert into test select 'B店','1',200
insert into test select 'B店','3',200
go
declare @sql varchar(8000)
set @sql='select 店面'
select @sql=@sql+',['+rtrim(产品)+']=sum(case 产品 when '''+rtrim(产品)+''' then 金额 else 0 end)'
from test group by 产品 order by 产品
set @sql=@sql+',总计=sum(金额) from test group by 店面'
exec(@sql)
go
create table B(ID int,课程 varchar(20),成绩 int)
insert A select 1,'Mike','[email protected]'
union all select 2,'Tom','[email protected]'
union all select 3,'Bill','[email protected]'insert B select 1,'数学',98
union all select 1,'语文',88
union all select 1,'英语',99
union all select 1,'政治',89
union all select 1,'历史',89
union all select 1,'美术',90union all select 2,'数学',77
union all select 2,'语文',69
union all select 2,'英语',96
union all select 2,'历史',89
union all select 2,'美术',69union all select 3,'数学',87
union all select 3,'语文',69
union all select 3,'英语',88
union all select 3,'政治',100
union all select 3,'历史',98
union all select 3,'美术',99declare @s varchar(4000)
set @s=''
select @s=@s+',max(case bh when '''+rtrim(bh)+''' then 成绩 else 0 end) as '+课程+''
from (select *,bh=(select count(1) from B where ID=b1.ID and 课程<=b1.课程) from B b1)t group by bh,课程select @s='select A.ID,A.姓名,A.Email,'+stuff(@s,1,1,'')+' from A inner join
(select *,bh=(select count(1) from B where ID=b1.ID and 课程<=b1.课程) from B b1)t
on A.ID=t.ID group by
A.ID,A.姓名,A.Email'
exec(@s)/*
1 Mike [email protected] 89 90 98 99 88 89
2 Tom [email protected] 89 69 77 96 69 0
3 Bill [email protected] 98 99 87 88 69 100
*/
set @s=''
select @s=@s+',max(case bh when '''+rtrim(bh)+''' then 课程 else null end) as '+课程+'
,max(case bh when '''+rtrim(bh)+''' then 成绩 else null end) as 成绩'
from (select *,bh=(select count(1) from B where ID=b1.ID and 课程<=b1.课程) from B b1)t group by bh,课程select @s='select A.ID,A.姓名,A.Email,'+stuff(@s,1,1,'')+' from A inner join
(select *,bh=(select count(1) from B where ID=b1.ID and 课程<=b1.课程) from B b1)t
on A.ID=t.ID group by
A.ID,A.姓名,A.Email'
exec(@s)1 Mike [email protected] 历史 89 美术 90 数学 98 英语 99 语文 88 政治 89
2 Tom [email protected] 历史 89 美术 69 数学 77 英语 96 语文 69 NULL NULL
3 Bill [email protected] 历史 98 美术 99 数学 87 英语 88 语文 69 政治 100
1 Mike [email protected] 历史 89 美术 90 数学 98
2 Tom [email protected] 化学 89 数学 69
3 Bill [email protected] 美术 99 数学 87 英语 88 语文 69 政治 100
set @s=''
select @s=@s+',max(case bh when '''+rtrim(bh)+''' then 课程 else null end) as 课程'+rtrim(bh)+'
,max(case bh when '''+rtrim(bh)+''' then 成绩 else null end) as 成绩'+rtrim(bh)+''
from (select *,bh=(select count(1) from B where ID=b1.ID and 课程<=b1.课程) from B b1)t group by bhselect @s='select A.ID,A.姓名,A.Email,'+stuff(@s,1,1,'')+' from A inner join
(select *,bh=(select count(1) from B where ID=b1.ID and 课程<=b1.课程) from B b1)t
on A.ID=t.ID group by
A.ID,A.姓名,A.Email'exec(@s)1 Mike [email protected] 美术 90 数学 98 政治 89 NULL NULL NULL NULL NULL NULL
2 Tom [email protected] 历史 89 美术 69 数学 77 NULL NULL NULL NULL NULL NULL
3 Bill [email protected] 历史 98 美术 99 数学 87 英语 88 语文 69 政治 100
create table B(ID int,课程 varchar(20),成绩 int)
insert A select 1,'Mike','[email protected]'
union all select 2,'Tom','[email protected]'
union all select 3,'Bill','[email protected]'insert B select 1,'数学',98
union all select 1,'政治',89
union all select 1,'美术',90union all select 2,'数学',77
union all select 2,'美术',69union all select 3,'数学',87
union all select 3,'语文',69
union all select 3,'英语',88
union all select 3,'政治',100
union all select 3,'历史',98
union all select 3,'美术',99declare @s varchar(8000)
set @s=''
select @s=@s+',max(case bh when '''+rtrim(bh)+''' then 课程 else null end) as 课程'+rtrim(bh)+'
,max(case bh when '''+rtrim(bh)+''' then 成绩 else null end) as 成绩'+rtrim(bh)+''
from (select *,bh=(select count(1) from B where ID=b1.ID and 课程<=b1.课程) from B b1)t group by bhselect @s='select A.ID,A.姓名,A.Email,'+stuff(@s,1,1,'')+' from A inner join
(select *,bh=(select count(1) from B where ID=b1.ID and 课程<=b1.课程) from B b1)t
on A.ID=t.ID group by
A.ID,A.姓名,A.Email'exec(@s)drop table A,B/*
1 Mike [email protected] 美术 90 数学 98 政治 89 NULL NULL NULL NULL NULL NULL
2 Tom [email protected] 美术 69 数学 77 NULL NULL NULL NULL NULL NULL NULL NULL
3 Bill [email protected] 历史 98 美术 99 数学 87 英语 88 语文 69 政治 100
*/
sum(case 表B.课程 when '课程1' then 表B.成绩 else 0 end) as 课程1,
sum(case 表B.课程 when '课程2' then 表B.成绩 else 0 end) as 课程2,
sum(case 表B.课程 when '课程3' then 表B.成绩 else 0 end) as 课程3,
………………………………
sum(case 表B.课程 when '课程n' then 表B.成绩 else 0 end) as 课程n,
from 表A,表B
where 表A.id=表B.id
group by 表A.id,表B.name
order by 表A.id