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

liangruihh 2014-12-26 12:23:36

三个表，学生表，成绩表，补考成绩表，
学生表
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语句怎么写

...全文

528 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

砸死牛顿的苹果 2015-06-14

打赏
举报

简洁一下~

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

砸死牛顿的苹果 2015-06-14

打赏
举报

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

HelloWordGirl 2015-06-12

打赏
举报

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) 可以两次列转行

习惯性蹭分 2015-06-11

打赏
举报


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)

一头大葱 2015-06-11

打赏
举报

这里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最终结果为：

还在加载中灬 2014-12-26

打赏
举报

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)

frankl123 2014-12-26

打赏
举报

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 行受影响)

frankl123 2014-12-26

打赏
举报

写了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)

sql试题及答案，sql 行列转换，sql存储过程实例