create table 期末成绩表
(
序号 varchar(10),
学生ID varchar(20),
学生姓名 varchar(20),
课程ID varchar(20),
课程名称 varchar(20),
成绩 decimal,
教师ID varchar(20),
教师名称 varchar(20)
)
go
insert into 期末成绩表 select '10001','ST001','学生1','CE001','课程1',94,'TC001','教师1'
union all select '10002','ST002','学生2','CE001','课程1',83,'TC001','教师1'
union all select '10003','ST003','学生3','CE001','课程1',72,'TC001','教师1'
union all select '10004','ST004','学生4','CE001','课程1',91,'TC001','教师1'
union all select '10005','ST005','学生5','CE001','课程1',73,'TC001','教师1'
union all select '10006','ST001','学生1','CE002','课程2',68,'TC002','教师2'
union all select '10007','ST002','学生2','CE002','课程2',96,'TC002','教师2'
union all select '10008','ST003','学生3','CE002','课程2',86,'TC002','教师2'
union all select '10009','ST004','学生4','CE002','课程2',99,'TC002','教师2'
union all select '10010','ST005','学生5','CE002','课程2',83,'TC002','教师2'
union all select '10011','ST001','学生1','CE003','课程3',90,'TC003','教师3'
union all select '10012','ST002','学生2','CE003','课程3',80,'TC003','教师3'
union all select '10013','ST003','学生3','CE003','课程3',63,'TC003','教师3'
union all select '10014','ST004','学生4','CE003','课程3',99,'TC003','教师3'
union all select '10015','ST005','学生5','CE003','课程3',87,'TC003','教师3'
go查询第个科目的最高分及最低分,格式如下
:课程ID,课程名称,最高分,学生ID,学生姓名,最低分,学生ID,学生姓名,教师ID,教师名称
谢谢各位了
(
序号 varchar(10),
学生ID varchar(20),
学生姓名 varchar(20),
课程ID varchar(20),
课程名称 varchar(20),
成绩 decimal,
教师ID varchar(20),
教师名称 varchar(20)
)
go
insert into 期末成绩表 select '10001','ST001','学生1','CE001','课程1',94,'TC001','教师1'
union all select '10002','ST002','学生2','CE001','课程1',83,'TC001','教师1'
union all select '10003','ST003','学生3','CE001','课程1',72,'TC001','教师1'
union all select '10004','ST004','学生4','CE001','课程1',91,'TC001','教师1'
union all select '10005','ST005','学生5','CE001','课程1',73,'TC001','教师1'
union all select '10006','ST001','学生1','CE002','课程2',68,'TC002','教师2'
union all select '10007','ST002','学生2','CE002','课程2',96,'TC002','教师2'
union all select '10008','ST003','学生3','CE002','课程2',86,'TC002','教师2'
union all select '10009','ST004','学生4','CE002','课程2',99,'TC002','教师2'
union all select '10010','ST005','学生5','CE002','课程2',83,'TC002','教师2'
union all select '10011','ST001','学生1','CE003','课程3',90,'TC003','教师3'
union all select '10012','ST002','学生2','CE003','课程3',80,'TC003','教师3'
union all select '10013','ST003','学生3','CE003','课程3',63,'TC003','教师3'
union all select '10014','ST004','学生4','CE003','课程3',99,'TC003','教师3'
union all select '10015','ST005','学生5','CE003','课程3',87,'TC003','教师3'
go查询第个科目的最高分及最低分,格式如下
:课程ID,课程名称,最高分,学生ID,学生姓名,最低分,学生ID,学生姓名,教师ID,教师名称
谢谢各位了
课程ID,
课程名称,
最高分= (select max(成绩) from 期末成绩表 t where t.课程名称=a.课程名称),
学生ID,
学生姓名,
最低分=(select min(成绩) from 期末成绩表 t where t.课程名称=a.课程名称),
学生ID,
学生姓名,
教师ID,
教师名称
from 期末成绩表 a
结果:
CE001 课程1 94 ST001 学生1 72 ST001 学生1 TC001 教师1
CE001 课程1 94 ST002 学生2 72 ST002 学生2 TC001 教师1
CE001 课程1 94 ST003 学生3 72 ST003 学生3 TC001 教师1
CE001 课程1 94 ST004 学生4 72 ST004 学生4 TC001 教师1
CE001 课程1 94 ST005 学生5 72 ST005 学生5 TC001 教师1
CE002 课程2 99 ST001 学生1 68 ST001 学生1 TC002 教师2
CE002 课程2 99 ST002 学生2 68 ST002 学生2 TC002 教师2
CE002 课程2 99 ST003 学生3 68 ST003 学生3 TC002 教师2
CE002 课程2 99 ST004 学生4 68 ST004 学生4 TC002 教师2
CE002 课程2 99 ST005 学生5 68 ST005 学生5 TC002 教师2
CE003 课程3 99 ST001 学生1 63 ST001 学生1 TC003 教师3
CE003 课程3 99 ST002 学生2 63 ST002 学生2 TC003 教师3
CE003 课程3 99 ST003 学生3 63 ST003 学生3 TC003 教师3
CE003 课程3 99 ST004 学生4 63 ST004 学生4 TC003 教师3
CE003 课程3 99 ST005 学生5 63 ST005 学生5 TC003 教师3
from (SELECT 成绩,学生ID,学生姓名,教师ID,教师名称,课程ID,课程名称 FROM # WHERE NOT EXISTS(SELECT 1 FROM # S WHERE S.课程ID=#.课程ID AND S.成绩>#.成绩))S
INNER JOIN (SELECT 成绩,学生ID,学生姓名,教师ID,教师名称,课程ID FROM # WHERE NOT EXISTS(SELECT 1 FROM # S WHERE S.课程ID=#.课程ID AND S.成绩<#.成绩))X
ON X.课程ID=S.课程ID
(
序号 varchar(10),
学生ID varchar(20),
学生姓名 varchar(20),
课程ID varchar(20),
课程名称 varchar(20),
成绩 decimal,
教师ID varchar(20),
教师名称 varchar(20)
)
go
insert into # select '10001','ST001','学生1','CE001','课程1',94,'TC001','教师1'
union all select '10002','ST002','学生2','CE001','课程1',83,'TC001','教师1'
union all select '10003','ST003','学生3','CE001','课程1',72,'TC001','教师1'
union all select '10004','ST004','学生4','CE001','课程1',91,'TC001','教师1'
union all select '10005','ST005','学生5','CE001','课程1',73,'TC001','教师1'
union all select '10006','ST001','学生1','CE002','课程2',68,'TC002','教师2'
union all select '10007','ST002','学生2','CE002','课程2',96,'TC002','教师2'
union all select '10008','ST003','学生3','CE002','课程2',86,'TC002','教师2'
union all select '10009','ST004','学生4','CE002','课程2',99,'TC002','教师2'
union all select '10010','ST005','学生5','CE002','课程2',83,'TC002','教师2'
union all select '10011','ST001','学生1','CE003','课程3',90,'TC003','教师3'
union all select '10012','ST002','学生2','CE003','课程3',80,'TC003','教师3'
union all select '10013','ST003','学生3','CE003','课程3',63,'TC003','教师3'
union all select '10014','ST004','学生4','CE003','课程3',99,'TC003','教师3'
union all select '10015','ST005','学生5','CE003','课程3',87,'TC003','教师3'
SELECT distinct S.课程ID,S.课程名称,S.成绩 As最高分,S.学生ID,S.学生姓名, X.成绩 As 最低分,X.学生ID,X.学生姓名,S.教师ID,S.教师名称
from (SELECT 成绩,学生ID,学生姓名,教师ID,教师名称,课程ID,课程名称 FROM # WHERE NOT EXISTS(SELECT 1 FROM # S WHERE S.课程ID=#.课程ID AND S.成绩>#.成绩))S
INNER JOIN (SELECT 成绩,学生ID,学生姓名,教师ID,教师名称,课程ID FROM # WHERE NOT EXISTS(SELECT 1 FROM # S WHERE S.课程ID=#.课程ID AND S.成绩<#.成绩))X
ON X.课程ID=S.课程ID
create table tb
(
序号 varchar(10),
学生ID varchar(20),
学生姓名 varchar(20),
课程ID varchar(20),
课程名称 nvarchar(20),
成绩 decimal,
教师ID varchar(20),
教师名称 nvarchar(20)
)
go
insert into tb select '10001','ST001','学生1','CE001',N'课程1',94,'TC001',N'教师1'
union all select '10002','ST002','学生2','CE001',N'课程1',83,'TC001',N'教师1'
union all select '10003','ST003','学生3','CE001',N'课程1',72,'TC001',N'教师1'
union all select '10004','ST004','学生4','CE001',N'课程1',91,'TC001',N'教师1'
union all select '10005','ST005','学生5','CE001',N'课程1',73,'TC001',N'教师1'
union all select '10006','ST001','学生1','CE002',N'课程2',68,'TC002',N'教师2'
union all select '10007','ST002','学生2','CE002',N'课程2',96,'TC002',N'教师2'
union all select '10008','ST003','学生3','CE002',N'课程2',86,'TC002',N'教师2'
union all select '10009','ST004','学生4','CE002',N'课程2',99,'TC002',N'教师2'
union all select '10010','ST005','学生5','CE002',N'课程2',83,'TC002',N'教师2'
union all select '10011','ST001','学生1','CE003',N'课程3',90,'TC003',N'教师3'
union all select '10012','ST002','学生2','CE003',N'课程3',80,'TC003',N'教师3'
union all select '10013','ST003','学生3','CE003',N'课程3',63,'TC003',N'教师3'
union all select '10014','ST004','学生4','CE003',N'课程3',99,'TC003',N'教师3'
union all select '10015','ST005','学生5','CE003',N'课程3',87,'TC003',N'教师3'
goSELECT A.课程ID,MAXCJ,MINCJ
FROM (
SELECT A.*,MAXCJ FROM TB AS A JOIN (
select 课程ID,MAX( 成绩) AS MAXCJ,MIN( 成绩) AS MINCJ from tb GROUP BY 课程ID
) B ON A.课程ID=B.课程ID AND A.成绩=MAXCJ) A
JOIN (
SELECT A.*,MINCJ FROM TB AS A JOIN (
select 课程ID,MAX( 成绩) AS MAXCJ,MIN( 成绩) AS MINCJ from tb GROUP BY 课程ID
) B ON A.课程ID=B.课程ID AND A.成绩=MINCJ) B
ON A.课程ID=B.课程IDdrop table tb
/*
课程ID MAXCJ MINCJ
-------------------- -------------------- --------------------
CE001 94 72
CE002 99 68
CE003 99 63
*/
select 课程ID,课程名称,最高分,学生ID=(select TOP 1 学生ID from 期末成绩表 where 成绩=最高分),学生姓名=(select top 1 学生姓名 from 期末成绩表 where 成绩=最高分),
最低分,学生ID=(select TOP 1 学生ID from 期末成绩表 where 成绩=最低分),学生姓名=(select top 1 学生姓名 from 期末成绩表 where 成绩=最低分),
教师ID=(select TOP 1 教师ID from 期末成绩表 where 成绩=最低分),教师名称=(select TOP 1 教师ID from 期末成绩表 where 成绩=最低分)
from(
select 课程ID,课程名称,最高分=max(成绩),最低分=min(成绩) from 期末成绩表 a group by 课程ID,课程名称)a
--针对可能出现的多人有最高分或最低分
select a.课程ID,a.课程名称,a.成绩 最高分,a.学生ID,a.学生姓名,b.成绩 最低分,b.学生ID,b.学生姓名,a.教师ID,a.教师名称
from
(select * from 成绩表 where 成绩=max(成绩)) a inner join
(select * from 成绩表 where 成绩=max(成绩)) b on a.课程ID =b.课程ID
from
(select * from 成绩表 group by 课程ID having 成绩=max(成绩) ) a inner join
(select * from 成绩表 group by 课程ID having 成绩=max(成绩)) b on a.课程ID =b.课程ID
from
(select * from 期末成绩表 d where 成绩=(select max(成绩) from 期末成绩表 c where d.课程ID=c.课程ID) ) a inner join
(select * from 期末成绩表 e where 成绩=(select min(成绩) from 期末成绩表 f where e.课程ID=f.课程ID) ) b on a.课程ID =b.课程ID
go
create table 期末成绩表
(
序号 varchar(10),
学生ID varchar(20),
学生姓名 varchar(20),
课程ID varchar(20),
课程名称 varchar(20),
成绩 decimal,
教师ID varchar(20),
教师名称 varchar(20)
)
go
insert into 期末成绩表 select '10001','ST001','学生1','CE001','课程1',94,'TC001','教师1'
union all select '10002','ST002','学生2','CE001','课程1',83,'TC001','教师1'
union all select '10003','ST003','学生3','CE001','课程1',72,'TC001','教师1'
union all select '10004','ST004','学生4','CE001','课程1',91,'TC001','教师1'
union all select '10005','ST005','学生5','CE001','课程1',73,'TC001','教师1'
union all select '10006','ST001','学生1','CE002','课程2',68,'TC002','教师2'
union all select '10007','ST002','学生2','CE002','课程2',96,'TC002','教师2'
union all select '10008','ST003','学生3','CE002','课程2',86,'TC002','教师2'
union all select '10009','ST004','学生4','CE002','课程2',99,'TC002','教师2'
union all select '10010','ST005','学生5','CE002','课程2',83,'TC002','教师2'
union all select '10011','ST001','学生1','CE003','课程3',90,'TC003','教师3'
union all select '10012','ST002','学生2','CE003','课程3',80,'TC003','教师3'
union all select '10013','ST003','学生3','CE003','课程3',63,'TC003','教师3'
union all select '10014','ST004','学生4','CE003','课程3',99,'TC003','教师3'
union all select '10015','ST005','学生5','CE003','课程3',87,'TC003','教师3'
union all select '10016','ST006','学生6','CE003','课程3',99,'TC003','教师3'--加一个并列第一
goselect * from 期末成绩表select 课程ID,课程名称
,最高分=max(成绩)
,学生ID=stuff((select ','+学生ID from 期末成绩表 where 课程ID=a.课程ID and 成绩=max(a.成绩) for xml path('')),1,1,'')
,学生姓名=stuff((select ','+学生姓名 from 期末成绩表 where 课程ID=a.课程ID and 成绩=max(a.成绩) for xml path('')),1,1,'')
,最低分=min(成绩)
,学生ID=stuff((select ','+学生ID from 期末成绩表 where 课程ID=a.课程ID and 成绩=min(a.成绩) for xml path('')),1,1,'')
,学生姓名=stuff((select ','+学生姓名 from 期末成绩表 where 课程ID=a.课程ID and 成绩=min(a.成绩) for xml path('')),1,1,'')
,教师ID,教师名称
from 期末成绩表 a
group by 课程ID,课程名称,教师ID,教师名称/*
课程ID 课程名称 最高分 学生ID 学生姓名 最低分 学生ID 学生姓名 教师ID 教师名称
CE001 课程1 94 ST001 学生1 72 ST003 学生3 TC001 教师1
CE002 课程2 99 ST004 学生4 68 ST001 学生1 TC002 教师2
CE003 课程3 99 ST004,ST006 学生4,学生6 63 ST003 学生3 TC003 教师3 --并列情况
(3 row(s) affected)
*/
最高分=(select max(成绩) from 期末成绩表 b where a.课程名称=b.课程名称),aa.学生ID,aa. 学生姓名,
最低分=(select min(成绩) from 期末成绩表 c where a.课程名称=c.课程名称),bb.学生ID,bb. 学生姓名
,a.教师ID,a.教师名称
from 期末成绩表 a
left join 期末成绩表 aa on a.课程名称=aa.课程名称 and aa.成绩=(select max(成绩)
from 期末成绩表 b where a.课程名称=b.课程名称)
left join 期末成绩表 bb on a.课程名称=bb.课程名称 and bb.成绩=(select min(成绩)
from 期末成绩表 b where a.课程名称=b.课程名称)
aa.成绩 最高分,aa.学生ID,aa. 学生姓名,
bb.成绩 最低分,bb.学生ID,bb. 学生姓名
,a.教师ID,a.教师名称
from 期末成绩表 a
left join 期末成绩表 aa on a.课程名称=aa.课程名称 and aa.成绩=(select max(成绩)
from 期末成绩表 b where a.课程名称=b.课程名称)
left join 期末成绩表 bb on a.课程名称=bb.课程名称 and bb.成绩=(select min(成绩)
from 期末成绩表 b where a.课程名称=b.课程名称)
csdn效率就是高,我开始看的时候才1楼,回完就20多了,还是我太慢ORZ
if object_id('fn_combineStr') is not null
drop function fn_combineStr
go
create function fn_combineStr(@courseID varchar(20),@score decimal,@flag tinyint)
returns varchar(1000)
as
begin
declare @s varchar(1000) select @s=isnull(@s+',','')+case @flag when 0 then 学生ID else 学生姓名 end
from 期末成绩表
where 课程ID=@courseID and 成绩=@score return @s
end
goselect 课程ID,课程名称
,最高分=max(成绩)
,学生ID=dbo.fn_combineStr(a.课程ID,max(a.成绩),0)
,学生姓名=dbo.fn_combineStr(a.课程ID,max(a.成绩),1)
,最低分=min(成绩)
,学生ID=dbo.fn_combineStr(a.课程ID,min(a.成绩),0)
,学生姓名=dbo.fn_combineStr(a.课程ID,min(a.成绩),1)
,教师ID,教师名称
from 期末成绩表 a
group by 课程ID,课程名称,教师ID,教师名称/*
课程ID 课程名称 最高分 学生ID 学生姓名 最低分 学生ID 学生姓名 教师ID 教师名称
CE001 课程1 94 ST001 学生1 72 ST003 学生3 TC001 教师1
CE002 课程2 99 ST004 学生4 68 ST001 学生1 TC002 教师2
CE003 课程3 99 ST004,ST006 学生4,学生6 63 ST003 学生3 TC003 教师3
(3 row(s) affected)
*/