大家好
我有一个表,studentMark 学生成绩表
他的结构为:
自增ID ,学生ID, ,科目ID, 成绩分数 ,考试时间 ,考试标识ID
ID (int ),StudentID( int ), Kind(int ), Mark( float ) ,TestTime (DateTime), TestKindID(int)
1, ,20, ,1 ,90 ,2004-05-06 , 1
2, ,20, ,2 ,80 ,2004-05-06 , 1
3, ,20, ,3 ,70 ,2004-05-06 , 1
4, ,21, ,1 ,60 ,2004-05-06 , 1
5, ,21, ,2 ,70 ,2004-05-06 , 1
6, ,21, ,3 ,90 ,2004-05-06 , 1
7, ,23, ,1 ,50 ,2004-05-06 , 1
8, ,23, ,2 ,40 ,2004-05-06 , 1
9, ,23, ,3 ,20 ,2004-05-06 , 1
10, ,20, ,1 ,90 ,2004-02-26 , 2
11, ,20, ,2 ,80 ,2004-02-26 , 2
12, ,20, ,3 ,70 ,2004-02-26 , 2
13, ,21, ,1 ,60 ,2004-02-26 , 2
14, ,21, ,2 ,70 ,2004-02-26 , 2
14, ,21, ,3 ,90 ,2004-02-26 , 2
15, ,23, ,1 ,50 ,2004-02-26 , 2
16, ,23, ,2 ,40 ,2004-02-26 , 2
17, ,23, ,3 ,20 ,2004-02-26 , 2
我现在想用SQL 语句得到这样的结果,(也就是要把一次考试的所有科目成绩,和科目名称放在同一行来显示,而科目的多少是不定的,可能有N个)请问怎么做?
学生编号,科目,分数,科目,分数,科目,分数,考试标识
20 , 1 , 90 , 2 , 80 , 2 , 70 , 1
21 , 1 , 60 , 2 , 70 , 3 , 90 , 1
21 , 1 , 50 , 2 , 40 , 3 , 20 , 1
20 , 1 , 90 , 2 , 80 , 3 , 70 ,2
21 , 1 , 60 , 2 , 70 , 3 , 90 , 2
21 , 1 , 50 , 2 , 40 , 3 , 20, 2
如果科目很多话,就像下面这样一直增出来
21 , 1 , 50 , 2 , 40 , 3 ,13 , 4 ,55 , 5 ,66 ,6 ,20 ....... 2
用动态交叉表应可以实现,
参考一下:http://blog.csdn.net/fuxc/archive/2002/03/04/14528.aspx
--用动态sql语句
declare @s nvarchar(4000)
set @s=''
select @s=@s+',科目='+quotename(Kind,'''')
+',分数=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID from studentMark group by StudentID,TestKindID')
--测试
--测试数据
create table studentMark(ID int,StudentID int,Kind int,Mark float,TestTime DateTime,TestKindID int)
insert studentMark select 1, 20,1, 90,'2004-05-06',1
union all select 2, 20,2, 80,'2004-05-06',1
union all select 3, 20,3, 70,'2004-05-06',1
union all select 4, 21,1, 60,'2004-05-06',1
union all select 5, 21,2, 70,'2004-05-06',1
union all select 6, 21,3, 90,'2004-05-06',1
union all select 7, 23,1, 50,'2004-05-06',1
union all select 8, 23,2, 40,'2004-05-06',1
union all select 9, 23,3, 20,'2004-05-06',1
union all select 10, 20,1, 90,'2004-02-26',2
union all select 11, 20,2, 80,'2004-02-26',2
union all select 12, 20,3, 70,'2004-02-26',2
union all select 13, 21,1, 60,'2004-02-26',2
union all select 14, 21,2, 70,'2004-02-26',2
union all select 14, 21,3, 90,'2004-02-26',2
union all select 15, 23,1, 50,'2004-02-26',2
union all select 16, 23,2, 40,'2004-02-26',2
union all select 17, 23,3, 20,'2004-02-26',2
go
--查询
declare @s nvarchar(4000)
set @s=''
select @s=@s+',科目='+quotename(Kind,'''')
+',分数=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID
from studentMark
group by StudentID,TestKindID
order by StudentID,TestKindID')
go
--删除测试
drop table studentMark
/*--结果
StudentID 科目 分数 科目 分数 科目 分数 TestKindID
----------- ---- -------- ------ -------- ------ -------- ----------
20 1 90.0 2 80.0 3 70.0 1
20 1 90.0 2 80.0 3 70.0 2
21 1 60.0 2 70.0 3 90.0 1
21 1 60.0 2 70.0 3 90.0 2
23 1 50.0 2 40.0 3 20.0 1
23 1 50.0 2 40.0 3 20.0 2
--*/
create table studentmark(ID int,StudentID int,Kind int,Mark float,TestTime DateTime,TestKindID int)
insert into studentmark
select 1, 20, 1,90 ,'2004-05-06' , 1 union all
select 2, 20, 2,80 ,'2004-05-06' , 1 union all
select 3, 20, 3,70 ,'2004-05-06' , 1 union all
select 4, 21, 1,60 ,'2004-05-06' , 1 union all
select 5, 21, 2,70 ,'2004-05-06' , 1 union all
select 6, 21, 3,90 ,'2004-05-06' , 1 union all
select 7, 23, 1,50 ,'2004-05-06' , 1 union all
select 8, 23, 2,40 ,'2004-05-06' , 1 union all
select 9, 23, 3,20 ,'2004-05-06' , 1 union all
select 10, 20, 1,90 ,'2004-02-26' , 2 union all
select 11, 20, 2,80 ,'2004-02-26' , 2 union all
select 12, 20, 3,70 ,'2004-02-26' , 2 union all
select 13, 21, 1,60 ,'2004-02-26' , 2 union all
select 14, 21, 2,70 ,'2004-02-26' , 2 union all
select 15, 21, 3,90 ,'2004-02-26' , 2 union all
select 16, 23, 1,50 ,'2004-02-26' , 2 union all
select 17, 23, 2,40 ,'2004-02-26' , 2 union all
select 18, 23, 3,20 ,'2004-02-26' , 2
DECLARE @SQL VARCHAR(8000)
SET @SQL='select distinct StudentID '
SELECT @SQL= @SQL+','''+cast(kind as nvarchar(10))+''' as ['+cast(kind as nvarchar(10))+'],(select mark from studentmark where kind='''+cast(kind as nvarchar(10))+''' and TestKindID=a.TestKindID and StudentID=a.StudentID) as mark'+cast(kind as nvarchar(10))
from
(select distinct kind from studentmark) b
set @sql=@sql+',TestKindID from studentmark a'
print @sql
exec(@sql)
drop table studentmark
--用动态sql语句
declare @s nvarchar(4000)
set @s=''
select @s=@s+',科目='+quotename(Kind,'''')
+',分数=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID from studentMark group by StudentID,TestKindID')
declare @s nvarchar(4000)
set @s=''
select @s=@s+',科目='+quotename(Kind,'''')
+',分数=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID
into ## from studentMark
group by StudentID,TestKindID
order by StudentID,TestKindID')
select * from ##
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename(rtrim(Kind)+'科目')+'='+quotename(Kind,'''')
+','+quotename(rtrim(Kind+'分数')+'=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID
into ## from studentMark
group by StudentID,TestKindID
order by StudentID,TestKindID')
select * from ##
服务器: 消息 156,级别 15,状态 1,行 6
在关键字 'from' 附近有语法错误。
--测试
--测试数据
create table studentMark(ID int,StudentID int,Kind int,Mark float,TestTime DateTime,TestKindID int)
insert studentMark select 1, 20,1, 90,'2004-05-06',1
union all select 2, 20,2, 80,'2004-05-06',1
union all select 3, 20,3, 70,'2004-05-06',1
union all select 4, 21,1, 60,'2004-05-06',1
union all select 5, 21,2, 70,'2004-05-06',1
union all select 6, 21,3, 90,'2004-05-06',1
union all select 7, 23,1, 50,'2004-05-06',1
union all select 8, 23,2, 40,'2004-05-06',1
union all select 9, 23,3, 20,'2004-05-06',1
union all select 10, 20,1, 90,'2004-02-26',2
union all select 11, 20,2, 80,'2004-02-26',2
union all select 12, 20,3, 70,'2004-02-26',2
union all select 13, 21,1, 60,'2004-02-26',2
union all select 14, 21,2, 70,'2004-02-26',2
union all select 14, 21,3, 90,'2004-02-26',2
union all select 15, 23,1, 50,'2004-02-26',2
union all select 16, 23,2, 40,'2004-02-26',2
union all select 17, 23,3, 20,'2004-02-26',2
go
--查询
declare @s nvarchar(4000)
set @s=''
select @s=@s
+','+quotename(rtrim(Kind)+'科目')+'='+quotename(Kind,'''')
+','+quotename(rtrim(Kind)+'分数')+'=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID
into ## from studentMark
group by StudentID,TestKindID
order by StudentID,TestKindID')
select * from ##
drop table ##
go
--删除测试
drop table studentMark
我有一个表,studentMark 学生成绩表
他的结构为:
自增ID ,学生ID, ,科目ID, 成绩分数 ,考试时间 ,考试标识ID
ID (int ),StudentID( int ), Kind(int ), Mark( float ) ,TestTime (DateTime), TestKindID(int)
1, ,20, ,1 ,90 ,2004-05-06 , 1
2, ,20, ,2 ,80 ,2004-05-06 , 1
3, ,20, ,3 ,70 ,2004-05-06 , 1
4, ,21, ,1 ,60 ,2004-05-06 , 1
5, ,21, ,2 ,70 ,2004-05-06 , 1
6, ,21, ,3 ,90 ,2004-05-06 , 1
7, ,23, ,1 ,50 ,2004-05-06 , 1
8, ,23, ,2 ,40 ,2004-05-06 , 1
9, ,23, ,3 ,20 ,2004-05-06 , 1
10, ,20, ,1 ,90 ,2004-02-26 , 2
11, ,20, ,2 ,80 ,2004-02-26 , 2
12, ,20, ,3 ,70 ,2004-02-26 , 2
13, ,21, ,1 ,60 ,2004-02-26 , 2
14, ,21, ,2 ,70 ,2004-02-26 , 2
14, ,21, ,3 ,90 ,2004-02-26 , 2
15, ,23, ,1 ,50 ,2004-02-26 , 2
16, ,23, ,2 ,40 ,2004-02-26 , 2
17, ,23, ,3 ,20 ,2004-02-26 , 2
我现在想用SQL 语句得到这样的结果,(也就是要把一次考试的所有科目成绩,和科目名称放在同一行来显示,而科目的多少是不定的,可能有N个)请问怎么做?
学生编号,科目,分数,科目,分数,科目,分数,考试标识
20 , 1 , 90 , 2 , 80 , 2 , 70 , 1
21 , 1 , 60 , 2 , 70 , 3 , 90 , 1
21 , 1 , 50 , 2 , 40 , 3 , 20 , 1
20 , 1 , 90 , 2 , 80 , 3 , 70 ,2
21 , 1 , 60 , 2 , 70 , 3 , 90 , 2
21 , 1 , 50 , 2 , 40 , 3 , 20, 2
如果科目很多话,就像下面这样一直增出来
21 , 1 , 50 , 2 , 40 , 3 ,13 , 4 ,55 , 5 ,66 ,6 ,20 ....... 2
问题点数:
100、回复次数:
16
1楼 huobr (两颗牙齿)
回复于 2005-05-22 13:56:30 得分 5
![三级用户 该版得分小于等于1000分,大于500分](http://topic.csdn.net/Include/RankImages/user3.gif)
用动态交叉表应可以实现,
参考一下:http://blog.csdn.net/fuxc/archive/2002/03/04/14528.aspx
2楼 zjcxc (邹建)
回复于 2005-05-22 14:22:34 得分 70
![三级钻石用户 该版得分小于等于800000分,大于500000分](http://topic.csdn.net/Include/RankImages/diam3.gif)
--用动态sql语句
declare @s nvarchar(4000)
set @s=''
select @s=@s+',科目='+quotename(Kind,'''')
+',分数=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID from studentMark group by StudentID,TestKindID')
3楼 zjcxc (邹建)
回复于 2005-05-22 14:26:30 得分 0
![三级钻石用户 该版得分小于等于800000分,大于500000分](http://topic.csdn.net/Include/RankImages/diam3.gif)
--测试
--测试数据
create table studentMark(ID int,StudentID int,Kind int,Mark float,TestTime DateTime,TestKindID int)
insert studentMark select 1, 20,1, 90,'2004-05-06',1
union all select 2, 20,2, 80,'2004-05-06',1
union all select 3, 20,3, 70,'2004-05-06',1
union all select 4, 21,1, 60,'2004-05-06',1
union all select 5, 21,2, 70,'2004-05-06',1
union all select 6, 21,3, 90,'2004-05-06',1
union all select 7, 23,1, 50,'2004-05-06',1
union all select 8, 23,2, 40,'2004-05-06',1
union all select 9, 23,3, 20,'2004-05-06',1
union all select 10, 20,1, 90,'2004-02-26',2
union all select 11, 20,2, 80,'2004-02-26',2
union all select 12, 20,3, 70,'2004-02-26',2
union all select 13, 21,1, 60,'2004-02-26',2
union all select 14, 21,2, 70,'2004-02-26',2
union all select 14, 21,3, 90,'2004-02-26',2
union all select 15, 23,1, 50,'2004-02-26',2
union all select 16, 23,2, 40,'2004-02-26',2
union all select 17, 23,3, 20,'2004-02-26',2
go
--查询
declare @s nvarchar(4000)
set @s=''
select @s=@s+',科目='+quotename(Kind,'''')
+',分数=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID
from studentMark
group by StudentID,TestKindID
order by StudentID,TestKindID')
go
--删除测试
drop table studentMark
/*--结果
StudentID 科目 分数 科目 分数 科目 分数 TestKindID
----------- ---- -------- ------ -------- ------ -------- ----------
20 1 90.0 2 80.0 3 70.0 1
20 1 90.0 2 80.0 3 70.0 2
21 1 60.0 2 70.0 3 90.0 1
21 1 60.0 2 70.0 3 90.0 2
23 1 50.0 2 40.0 3 20.0 1
23 1 50.0 2 40.0 3 20.0 2
--*/
4楼 xluzhong (Ralph)
回复于 2005-05-22 15:22:23 得分 20
![二星用户 该版得分小于等于30000分,大于10000分](http://topic.csdn.net/Include/RankImages/star2.gif)
create table studentmark(ID int,StudentID int,Kind int,Mark float,TestTime DateTime,TestKindID int)
insert into studentmark
select 1, 20, 1,90 ,'2004-05-06' , 1 union all
select 2, 20, 2,80 ,'2004-05-06' , 1 union all
select 3, 20, 3,70 ,'2004-05-06' , 1 union all
select 4, 21, 1,60 ,'2004-05-06' , 1 union all
select 5, 21, 2,70 ,'2004-05-06' , 1 union all
select 6, 21, 3,90 ,'2004-05-06' , 1 union all
select 7, 23, 1,50 ,'2004-05-06' , 1 union all
select 8, 23, 2,40 ,'2004-05-06' , 1 union all
select 9, 23, 3,20 ,'2004-05-06' , 1 union all
select 10, 20, 1,90 ,'2004-02-26' , 2 union all
select 11, 20, 2,80 ,'2004-02-26' , 2 union all
select 12, 20, 3,70 ,'2004-02-26' , 2 union all
select 13, 21, 1,60 ,'2004-02-26' , 2 union all
select 14, 21, 2,70 ,'2004-02-26' , 2 union all
select 15, 21, 3,90 ,'2004-02-26' , 2 union all
select 16, 23, 1,50 ,'2004-02-26' , 2 union all
select 17, 23, 2,40 ,'2004-02-26' , 2 union all
select 18, 23, 3,20 ,'2004-02-26' , 2
DECLARE @SQL VARCHAR(8000)
SET @SQL='select distinct StudentID '
SELECT @SQL= @SQL+','''+cast(kind as nvarchar(10))+''' as ['+cast(kind as nvarchar(10))+'],(select mark from studentmark where kind='''+cast(kind as nvarchar(10))+''' and TestKindID=a.TestKindID and StudentID=a.StudentID) as mark'+cast(kind as nvarchar(10))
from
(select distinct kind from studentmark) b
set @sql=@sql+',TestKindID from studentmark a'
print @sql
exec(@sql)
drop table studentmark
6楼 softj (天地客人<最近很迷茫>)
回复于 2005-05-22 16:39:41 得分 5
![一星用户 该版得分小于等于10000分,大于5000分](http://topic.csdn.net/Include/RankImages/star1.gif)
--用动态sql语句
declare @s nvarchar(4000)
set @s=''
select @s=@s+',科目='+quotename(Kind,'''')
+',分数=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID from studentMark group by StudentID,TestKindID')
9楼 zjcxc (邹建)
回复于 2005-05-22 18:13:23 得分 0
![三级钻石用户 该版得分小于等于800000分,大于500000分](http://topic.csdn.net/Include/RankImages/diam3.gif)
declare @s nvarchar(4000)
set @s=''
select @s=@s+',科目='+quotename(Kind,'''')
+',分数=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID
into ## from studentMark
group by StudentID,TestKindID
order by StudentID,TestKindID')
select * from ##
10楼 zjcxc (邹建)
回复于 2005-05-22 19:11:06 得分 0
![三级钻石用户 该版得分小于等于800000分,大于500000分](http://topic.csdn.net/Include/RankImages/diam3.gif)
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename(rtrim(Kind)+'科目')+'='+quotename(Kind,'''')
+','+quotename(rtrim(Kind+'分数')+'=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID
into ## from studentMark
group by StudentID,TestKindID
order by StudentID,TestKindID')
select * from ##
14楼 FlyNow (愛國者,拒絕日貨,誰敢管我,我是公務員)
回复于 2005-05-23 11:59:55 得分 0
![一级用户 该版得分小于等于100分](http://topic.csdn.net/Include/RankImages/user1.gif)
服务器: 消息 156,级别 15,状态 1,行 6
在关键字 'from' 附近有语法错误。
15楼 zjcxc (邹建)
回复于 2005-05-23 12:04:11 得分 0
![三级钻石用户 该版得分小于等于800000分,大于500000分](http://topic.csdn.net/Include/RankImages/diam3.gif)
--测试
--测试数据
create table studentMark(ID int,StudentID int,Kind int,Mark float,TestTime DateTime,TestKindID int)
insert studentMark select 1, 20,1, 90,'2004-05-06',1
union all select 2, 20,2, 80,'2004-05-06',1
union all select 3, 20,3, 70,'2004-05-06',1
union all select 4, 21,1, 60,'2004-05-06',1
union all select 5, 21,2, 70,'2004-05-06',1
union all select 6, 21,3, 90,'2004-05-06',1
union all select 7, 23,1, 50,'2004-05-06',1
union all select 8, 23,2, 40,'2004-05-06',1
union all select 9, 23,3, 20,'2004-05-06',1
union all select 10, 20,1, 90,'2004-02-26',2
union all select 11, 20,2, 80,'2004-02-26',2
union all select 12, 20,3, 70,'2004-02-26',2
union all select 13, 21,1, 60,'2004-02-26',2
union all select 14, 21,2, 70,'2004-02-26',2
union all select 14, 21,3, 90,'2004-02-26',2
union all select 15, 23,1, 50,'2004-02-26',2
union all select 16, 23,2, 40,'2004-02-26',2
union all select 17, 23,3, 20,'2004-02-26',2
go
--查询
declare @s nvarchar(4000)
set @s=''
select @s=@s
+','+quotename(rtrim(Kind)+'科目')+'='+quotename(Kind,'''')
+','+quotename(rtrim(Kind)+'分数')+'=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID
into ## from studentMark
group by StudentID,TestKindID
order by StudentID,TestKindID')
select * from ##
drop table ##
go
--删除测试
drop table studentMark