问一个很常见，但是有难度的SQL语句，如何合并记录？在线等100分

Question

补充：学科即subjectid放在一个数组里面，是可以知道的；但是成绩里不一定都有该学科

Answer 1

create table t_score(id int,stuent_id int,subject_id int,score int)insert into t_score select 1,201001,1,80
insert into t_score select 2,201002,1,90
insert into t_score select 3,201003,1,100
insert into t_score select 4,201001,2,80
insert into t_score select 5,201001,3,85
insert into t_score select 6,201002,2,86
insert into t_score select 7,201004,3,87
--需要一张subject_id与subject_name对照表t_subject
create table t_subject(subject_id int,subject_name varchar(20))insert into t_subject select 1,'语文'
insert into t_subject select 2,'数学'
insert into t_subject select 3,'物理'
insert into t_subject select 4,'化学'
declare @s varchar(8000)
set @s = 'select stuent_id'
select @s = @s +','+subject_name+'=max(case when subject_id = '+rtrim(subject_id)+' then score end)'
from t_subject order by subject_id
set @s = @s + ' from t_score group by stuent_id order by stuent_id'
exec(@s)

Answer 2

SQL Server 2000是不支持数组的，所以学科要么放在表中，要么固定下来。

Answer 3

select stuent_id as 学号,
sum(case subject_id when 1 then score else 0 end)as 语文,
sum(case subject_id when 2 then score else 0 end)as 数学,
sum(case subject_id when 3 then score else 0 end)as 物理,
sum(case subject_id when 4 then score else 0 end)as 化学
from t
group by stuent_id order by stuent_id

Answer 4

create table t_score(id int,stuent_id int,subject_id int,score int)
insert into t_score select 1,201001,1,80
insert into t_score select 2,201002,1,90
insert into t_score select 3,201003,1,100
insert into t_score select 4,201001,2,80
insert into t_score select 5,201001,3,85
insert into t_score select 6,201002,2,86
insert into t_score select 7,201004,3,87--如果已经明确了学科id与学科名称的对应关系，用如下查询select
    学号 = stuent_id,
    语文 = max(case subject_id when 1 then score end),
    数学 = max(case subject_id when 2 then score end),
    物理 = max(case subject_id when 3 then score end),
    化学 = max(case subject_id when 4 then score end)
from
    t_score
group by
    stuent_id
order by
    stuent_id

Answer 5

libin_ftsafe(子陌红尘)，虽然我还没看结果，但是很感谢你的热情，我马上测试一下！其实我的学科是读出来的，即每次考试应该考哪些，是个字符串：1,2,3,5,6

Answer 6

libin_ftsafe(子陌红尘):你前面那个好象就可以了，后面又补充的这段过程是什么意思？

Answer 7

select
stuent_id,
語文=max(case when subject_id = 1 then score end),
數學=max(case when subject_id = 2 then score end),
物理=max(case when subject_id = 3 then score end),
化學=max(case when subject_id = 4 then score end)
from t_score
group by stuent_id
order by stuent_id

Answer 8

如果学科在表里，如我最早给出的方式，那么：---------------------------------------------------------------------
create table t_score(id int,stuent_id int,subject_id int,score int)insert into t_score select 1,201001,1,80
insert into t_score select 2,201002,1,90
insert into t_score select 3,201003,1,100
insert into t_score select 4,201001,2,80
insert into t_score select 5,201001,3,85
insert into t_score select 6,201002,2,86
insert into t_score select 7,201004,3,87
--需要一张subject_id与subject_name对照表t_subject
create table t_subject(subject_id int,subject_name varchar(20))insert into t_subject select 1,'语文'
insert into t_subject select 2,'数学'
insert into t_subject select 3,'物理'
insert into t_subject select 4,'化学'
declare @s varchar(8000)
set @s = 'select stuent_id'
select @s = @s +','+subject_name+'=max(case when subject_id = '+rtrim(subject_id)+' then score end)'
from t_subject 
--增加以下条件
where subject_id in(1,2,3,5,6)
order by subject_idset @s = @s + ' from t_score group by stuent_id order by stuent_id'
exec(@s)

Answer 9

create table test(id int identity(1,1),stuent_id varchar(10),subject_id varchar(10),score int)
insert test select '201001','语文',80
union all select '201001','化学',80
union all select '201002','语文',90
union all select '201003','语文',100
union all select '201001','数学',80
union all select '201001','物理',85
union all select '201002','数学',86
union all select '201004','物理',87
select * from testDeclare @s  varchar(2000)
set @s=''
select  @s=@s+','+subject_id+'=isnull((select score  from  test where stuent_id=a.stuent_id  and subject_id='''+subject_id+'''),0)'  from  test  group  by  subject_id
set  @s='Select stuent_id '+@s+' from  test a  group  by  stuent_id'
print(@s)
exec(@s)
drop table test

Answer 10

哦，加条件已经没问题啦！
还有一个很重要的，如何加一个求和和排名，如果能解决，万分感谢！student  语文  数学 ... 历史   总分  排名

Answer 11

Declare @s  varchar(2000)
set @s=''select  @s=@s+','+subject_id+'=isnull((select score  from  test where stuent_id=a.stuent_id  and subject_id='''+subject_id+'''),0)'  from  test  group  by  subject_id
set  @s='Select stuent_id '+@s+',total=sum(score) from  test a  group  by  stuent_id order by total' exec(@s)
按总分顺序排列

Answer 12

zhaozhaozhaozhao：你也厉害啊！
sql语句写得这么熟练，很佩服你得的方法和 libin_ftsafe(子陌红尘）的有什么区别吗？能否讲解一下，谢谢

Answer 13

用printf(@s)一段一段打印看看,测试时经常用到

Answer 14

高手再请进，在上面的例子中：
如何存储过程的一个整型字符例如1,2,3,5,6，便于我在用where subjectid in(1,2,3,5,6)中使用，我怎么定义都不行啊，总是int和varchar转换错误
zhaozhaozhaozhao：我的数据库存放subjectid是个整型，所以我做了一下改动：SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GOALTER             PROCEDURE Score_Report
@subjectid int,@GradeName int
AS
Declare @s  varchar(2000)set @s=''select  @s=@s+','+'['+cast(subjectid as varchar)+']'+'=isnull((select score  from  s_c_test_item where st_barno=a.st_barno  and subjectid='''+cast(subjectid as varchar)+'''),0)' from  s_c_test_item  where subjectid  in (@subjectid) and s_c_test_id in (@GradeName) group  by  subjectid
set  @s='Select st_barno '+@s+',total=sum(score) from  s_c_test_item a  group  by  st_barno order by total' print @s
exec(@s)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GOexec Score_Report '1, 2, 3' ,2  --这里怎么传入1，2，3啊！！！！！在where subjectid  in (@subjectid)中必须要求@subjectid是个int啊！

Answer 15

--exec Score_Report '1, 2, 3' ,2  --这里怎么传入1，2，3啊！！！！！在where subjectid  in ---(@subjectid)中必须要求@subjectid是个int啊！
如果仅仅是这里有问题，可以改成
select  @s=@s+','+'['+cast(subjectid as varchar)+']'+'=isnull((select score  from  s_c_test_item where st_barno=a.st_barno  and subjectid='''+cast(subjectid as varchar)+'''),0)' from  s_c_test_item  where subjectid  in (' + @subjectid + ') and s_c_test_id in (@GradeName) group  by  subjectid
set  @s='Select st_barno '+@s+',total=sum(score) from  s_c_test_item a  group  by  st_barno order by total'

Answer 16

第1步：第一个表
id  stuent_id subject_id  score 
1    201001     1          80
2    201002     1          90
3    201003     1          100
4    201001     2          80
5    201001     3          85
6    201002     2          86
7    201004     3          87
假定表名称：student
第2步：
CREATE TABLE [student1] (
[学号] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[语文] [decimal](18, 0) NULL ,
[数学] [decimal](18, 0) NULL ,
[物理] [decimal](18, 0) NULL ,
[化学] [decimal](18, 0) NULL 
（或手工创建student1）第3步:
delete student1
go
INSERT INTO student1  (学号)  select distinct student_id from student
go
update student1
set 语文=student.score
from student where student1.学号=student.student_id and student.subject_id=1
go
update student1
set 数学=student.score
from student where student1.学号=student.student_id and student.subject_id=2
go
update student1
set 物理=student.score
from student where student1.学号=student.student_id and student.subject_id=3
go
update student1
set 化学=student.score
from student where student1.学号=student.student_id and student.subject_id=4
go