滚滚长江东流水,黄河入海不复返

:: :: :: ::

::

公告

怎样把记录合并到一条记录上来显示？

大家好
我有一个表，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 (两颗牙齿)

三级用户该版得分小于等于1000分，大于500分

回复于 2005-05-22 13:56:30 得分 5

用动态交叉表应可以实现，
参考一下：http://blog.csdn.net/fuxc/archive/2002/03/04/14528.aspx

2楼 zjcxc (邹建)

三级钻石用户该版得分小于等于800000分，大于500000分

回复于 2005-05-22 14:22:34 得分 70

--用动态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 (邹建)

三级钻石用户该版得分小于等于800000分，大于500000分

回复于 2005-05-22 14:26:30 得分 0

--测试

--测试数据
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)

二星用户该版得分小于等于30000分，大于10000分

回复于 2005-05-22 15:22:23 得分 20

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

5楼 chenqianlong (443)

四级用户该版得分小于等于2000分，大于1000分

回复于 2005-05-22 16:22:28 得分 0

6楼 softj (天地客人<最近很迷茫>)

一星用户该版得分小于等于10000分，大于5000分

回复于 2005-05-22 16:39:41 得分 5

--用动态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')

7楼 FlyNow (愛國者，拒絕日貨,誰敢管我，我是公務員)

一级用户该版得分小于等于100分

回复于 2005-05-22 17:58:30 得分 0

怎样把选得的结果放到一个临时表Ｔ里面去呀？

8楼 FlyNow (愛國者，拒絕日貨,誰敢管我，我是公務員)

一级用户该版得分小于等于100分

回复于 2005-05-22 18:06:53 得分 0

我还要把结果放到临时表里，那怎么办呀？

9楼 zjcxc (邹建)

三级钻石用户该版得分小于等于800000分，大于500000分

回复于 2005-05-22 18:13:23 得分 0

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 (邹建)

三级钻石用户该版得分小于等于800000分，大于500000分

回复于 2005-05-22 19:11:06 得分 0

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 ##

11楼 crystal_table ()

一级用户该版得分小于等于100分

回复于 2005-05-22 20:03:51 得分 0

12楼 sindia (乖乖兔)

一级用户该版得分小于等于100分

回复于 2005-05-22 21:12:40 得分 0

13楼 yaoyaomaomao (大熊)

一级用户该版得分小于等于100分

回复于 2005-05-23 10:40:21 得分 0

可以给邹大哥结分了,答案完全正确的.:)

14楼 FlyNow (愛國者，拒絕日貨,誰敢管我，我是公務員)

一级用户该版得分小于等于100分

回复于 2005-05-23 11:59:55 得分 0

服务器: 消息 156，级别 15，状态 1，行 6
在关键字 'from' 附近有语法错误。

15楼 zjcxc (邹建)

三级钻石用户该版得分小于等于800000分，大于500000分

回复于 2005-05-23 12:04:11 得分 0

--测试

--测试数据
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

posted on 2006-08-05 15:08 Steveson 阅读(298) 评论(0) 编辑收藏举报

刷新页面返回顶部