求助，sql语句，包含三个表联接和列转行

三个表，学生表，成绩表，补考成绩表，
学生表 
stu_id  stu_name  class_id
1             张三          1-1
2             李四          1-1
3             王五          1-2

成绩表
stu_id    subject   score   
1              java       77
1              c++        80
2              java       67
2              c++        58
3              java       56
3              c++        85

补考成绩表
stu_id    subject   score
2            c++          78
3            java          82

查询三个表得到学生的成绩和补考成绩
结果如下
stu_id      stu_name     java_score     c++_score     scores(总成绩)   java补考成绩    c++补考成绩
1                 张三              77                      80                156                       
2                 李四              67                      58                125                                                   78
3                 王五              56                      85                 141                 82

求问这个sql语句怎么写

8 个解决方案

#1

DECLARE @SQL NVARCHAR(MAX)
SET @SQL='SELECT T1.stu_id,T2.stu_name'
SELECT @SQL=@SQL+',ISNULL(SUM(CASE WHEN Stype=1 AND [subject]='''+subject+'''THEN[score]END),0)['+subject+']'
FROM 成绩表 GROUP BY subject
SET @SQL=@SQL+',ISNULL(SUM([score]),0)[scores(总成绩)]'
SELECT @SQL=@SQL+',ISNULL(SUM(CASE WHEN Stype=2 AND [subject]='''+subject+'''THEN[score]END),0)['+subject+'补考成绩]'
FROM 补考成绩表 GROUP BY subject
SET @SQL=@SQL+'FROM(SELECT *,1 Stype FROM 成绩表 UNION ALL SELECT *,2 FROM 补考成绩表)T1
JOIN 学生表 T2 ON T1.stu_id=T2.stu_id
GROUP BY T1.stu_id,T2.stu_name
ORDER BY T1.stu_id'
EXEC(@SQL)

#2

写了30分钟总算完全实现你的结果了

if object_id('学生表') is not null
drop table 学生表
go
create table 学生表(stu_id int,stu_name varchar(100),class_id varchar(100))
insert into 学生表
select 1 stu_id,'张三' stu_name ,'1-1' class_id
union all
select 2 stu_id,'李四' stu_name ,'1-1' class_id
union all
select 3 stu_id,'王五' stu_name ,'1-2' class_id
-------------------------------
if object_id('成绩表') is not null
drop table 成绩表
go 
create table 成绩表(stu_id int,subject varchar(100),score int) 
insert into 成绩表  
select 1  stu_id,            'java'  subject,     77 score
union all
select 1  stu_id,            'c++'  subject,     80 score
union all
select 2  stu_id,            'java'  subject,     67 score
union all
select 2  stu_id,            'c++'   subject,     58 score
union all
select 3  stu_id,            'java' subject,     56 score
union all
select 3  stu_id,            'c++'   subject,    85 score
------------------------------------- 
if object_id('补考成绩表') is not null
drop table 补考成绩表
go 
create table 补考成绩表(stu_id int,subject varchar(100),score int) 
insert into 补考成绩表    
select 2  stu_id,          'c++' subject,         78 score
union all
select 3  stu_id,         'java' subject,         82 score
  

--取成绩表中的科目
declare @subject varchar(100)
declare @subject_Renname varchar(100)
declare @subject_Renname1 varchar(100)
select @subject = ISNULL(@subject+',','') + tb1.subject 
from 
(
	select distinct QUOTENAME(subject) subject from 成绩表
)tb1

--字段重命名
select @subject_Renname = ISNULL(@subject_Renname+',','') + tb1.subject 
from 
(
	select distinct QUOTENAME(subject) +' ['+subject+'_score]' subject from 成绩表
)tb1
print @subject_Renname

--字段重命名后字段名
select @subject_Renname1 = ISNULL(@subject_Renname1+',','') + tb1.subject 
from 
(
	select distinct QUOTENAME(subject+'_score') subject from 成绩表
)tb1
print @subject_Renname1

--取补考成绩表中的科目名
declare @BKsubject varchar(100)
declare @BKsubject_Renname varchar(100)
declare @BKsubject_Renname1 varchar(100)
select @BKsubject = ISNULL(@BKsubject+',','') + tb1.subject 
from 
(
	select distinct QUOTENAME(subject) subject from 补考成绩表
)tb1
 
--字段重命名
 select @BKsubject_Renname = ISNULL(@BKsubject_Renname+',','') + tb1.subject 
from 
(
	select distinct QUOTENAME(subject) +' ['+subject+'补考成绩]' subject from 补考成绩表
)tb1
print @BKsubject_Renname

--字段重命名后字段名
select @BKsubject_Renname1 = ISNULL(@BKsubject_Renname1+',','') + tb1.subject 
from 
(
	select distinct QUOTENAME(subject+'补考成绩') subject from 补考成绩表
)tb1
print @BKsubject_Renname1


 declare @sql nvarchar(max)
 set @sql = '	select 
					tb4.stu_name
					,'+@subject_Renname1+',tb2.scores总成绩
					,'+@BKsubject_Renname1+' from 
					(
						select stU_ID,'+@subject_Renname+' 
						from 成绩表
						pivot 
						(max(score) for subject in ('+@subject+')) as pvt
					) tb1
					left join 
					(
						select stu_id
								,SUM(score) scores总成绩
						from 成绩表
						group by stu_id
					)tb2 on tb1.stu_id = tb2.stu_id
					left join 
					(
						select	stU_ID,'+@BKsubject_Renname+' 
						from 补考成绩表
						pivot
						(max(score) for subject in ('+@BKsubject+') )as pvt
					)tb3 on tb1.stu_id = tb3.stu_id
					left join 学生表 tb4 on tb1.stu_id = tb4.stu_id
				'
				print @sql
				exec(@sql)

#3

stu_name    c++_score   java_score  scores总成绩   c++补考成绩     java补考成绩
----------- ----------- ----------- ----------- ----------- -----------
张三        80          77          157         NULL        NULL
李四        58          67          125         78          NULL
王五        85          56          141         NULL        82

(3 行受影响)

#4

这里 http://datamachine.iteye.com/admin/blogs/2217205讨论了JAVA+SQL下的动态行转列实现方法，具体到这个问题可用这样写：

A1：执行SQL关联3个表取数，要用left join确保学生是完整的；
A2：按stu_id分组；
A3：按科目分组；
A4：创建结果序表；
A5-B5：循环stu_id分组，根据subject分组动态添加结果记录，A4最终结果为：

#5

if object_id('student') is not null
drop table student
go
create table student(stu_id int,stu_name varchar(100),class_id varchar(100))
insert into student
select 1 stu_id,'张三' stu_name ,'1-1' class_id
union all
select 2 stu_id,'李四' stu_name ,'1-1' class_id
union all
select 3 stu_id,'王五' stu_name ,'1-2' class_id
-------------------------------
if object_id('score') is not null
drop table score
go 
create table score(stu_id int,subject varchar(100),score int) 
insert into score  
select 1  stu_id,            'java'  subject,     77 score
union all
select 1  stu_id,            'c++'  subject,     80 score
union all
select 2  stu_id,            'java'  subject,     67 score
union all
select 2  stu_id,            'c++'   subject,     58 score
union all
select 3  stu_id,            'java' subject,     56 score
union all
select 3  stu_id,            'c++'   subject,    85 score
------------------------------------- 
if object_id('reScore') is not null
drop table reScore
go 
create table reScore(stu_id int,subject varchar(100),score int) 
insert into reScore    
select 2  stu_id,          'c++' subject,         78 score
union all
select 3  stu_id,         'java' subject,         82 score
 
 if object_id('tempdb..#tmp') is not null
 drop table #tmp
 select a.stu_id,a.stu_name,b.subject+'_score' as subject,b.score,1 as gp
 into #tmp
 from student a
 join score b on a.stu_id=b.stu_id
 union all
select a.stu_id,a.stu_name,'scores(总成绩)',sum(b.score),2
 from student a
 join score b on a.stu_id=b.stu_id
 group by a.stu_id,a.stu_name
 union all
 select a.stu_id,a.stu_name,b.subject+'_补考成绩', b.score,3
 from student a
 join rescore b on a.stu_id=b.stu_id

  declare @sql varchar(max)
select    @sql=  isnull(@sql+',','')+  quotename([subject],'[')
from #tmp group by subject,gp order by gp
set @sql='select * from (select stu_id,stu_name,[subject],score from #tmp ) a
pivot(max(score) for subject in('+@sql+'))p order by stu_id'
exec( @sql) 

#6

SELECT 
st.stu_id,
st.stu_name,
c.subject AS subjects,
c.score AS scores,
cs.cs AS Counts,
se.subject,
se.score 
INTO #StuScore
FROM StudentTable  st 
LEFT JOIN (SELECT c.stu_id,SUM(c.score)AS cs FROM Csore c GROUP BY c.stu_id)cs ON st.stu_id=cs.stu_id
LEFT JOIN Csore c ON c.stu_id=st.stu_id
LEFT JOIN Supplementary  se ON cs.stu_id=se.stu_id AND se.subject=c.subject

--定义变量
DECLARE @a VARCHAR(200),
        @b VARCHAR(200)
SELECT @a = Isnull (@a + ',', '') + Quotename(subjects)
FROM   (SELECT DISTINCT subjects
        FROM   #StuScore)AS A

SELECT @b = Isnull(@b + '', '')+'补考'
FROM   (SELECT DISTINCT subjects
        FROM   #stuscore)AS A

DECLARE @sqls VARCHAR(200)
SET @sqls='
with cet as (
select * from #StuScore as  t
pivot  
(  
max(scores)  
for subjects in (' + @a + ')  
) as r
),cet1 as (
select Stu_id,' + @b + ' from (
select Stu_id,' + @a + ' from #stuscore
pivot (
max(score)
for subjects in (' + @a + ')
)as r
)a
)
select 
stu_id,
stu_name,' 
+ @a + ' 
from cet
left join cet1 on cet.Stu_id=cet1.Stu_ID 
' 
EXEC (@sqls)

可以两次列转行

#7

select 
a.stu_id,a.stu_name,
b.java_score,b.[c++_score],b.java_score+b.[c++_score] [scores(总成绩)],
case when c.[java补考成绩] is null or c.[java补考成绩]=0 then '' else c.[java补考成绩]end [java补考成绩],
case when c.[c++补考成绩] is null or c.[c++补考成绩]=0 then '' else c.[c++补考成绩]end [c++补考成绩]
from 学生表 a
left join 
  (select stu_id,
          sum(case subject when 'java' then score else 0 end) java_score,
          sum(case subject when 'c++' then score else 0 end) [c++_score]
   from 成绩表 group by stu_id
   )b on a.stu_id =b.stu_id 
left join
  (select stu_id,
          cast(sum(case subject when 'java' then score else 0 end)as varchar) [java补考成绩],
          cast(sum(case subject when 'c++' then score else 0 end)as varchar) [c++补考成绩]
   from 补考成绩表 group by stu_id
  )c on a.stu_id =c.stu_id

 

#8

简洁一下~

select 
a.stu_id,a.stu_name,
b.java_score,b.[c++_score],b.java_score+b.[c++_score] [scores(总成绩)],
isnull(c.[java补考成绩],'') [java补考成绩],
isnull(c.[c++补考成绩],'') [c++补考成绩]
from 学生表 a
left join 
  (select stu_id,
          sum(case subject when 'java' then score else 0 end) java_score,
          sum(case subject when 'c++' then score else 0 end) [c++_score]
   from 成绩表 group by stu_id
   )b on a.stu_id =b.stu_id 
left join
  (select stu_id,
          cast(sum(case subject when 'java' then score else null end)as varchar) [java补考成绩],
          cast(sum(case subject when 'c++' then score else null end)as varchar) [c++补考成绩]
   from 补考成绩表 group by stu_id
  )c on a.stu_id =c.stu_id