declare @temp table ( score int ) insert into @temp(score) values(100); insert into @temp(score) values(85); insert into @temp(score) values(50); insert into @temp(score) values(92); insert into @temp(score) values(36); insert into @temp(score) values(89);--MAX VALUE WITH MaxV AS ( SELECT score FROM @temp AS T WHERE NOT EXISTS (SELECT * FROM @temp WHERE score > T.score) ) ,MinV AS ( SELECT score FROM @temp AS T WHERE NOT EXISTS (SELECT * FROM @temp WHERE score < T.score) ) SELECT X.score AS MaxValue, I.score AS MinValue FROM MaxV AS X,MinV AS IMaxValue MinValue ----------- ----------- 100 36(1 row(s) affected)
create table #t([studentID] int,[score] int) insert #t select 1,72 union all select 2,67 union all select 3,54 union all select 4,90 union all select 5,53 union all select 6,79 union all select 7,88select (select top 1 [score] from #t order by [score] desc) as 最高分, (select top 1 [score] from #t order by [score]) as 最低分drop table #t/* 最高分 最低分 ----------- ----------- 90 53 */
不用max,min,那就用group by+order by?
if object_id('tab') is not null drop table tab go create table tab([studentID] int,[score] int) insert tab select 1,72 union all select 2,67 union all select 3,54 union all select 4,90 union all select 5,53 union all select 6,79 union all select 7,88select (select top 1 [score] from tab order by [score] desc group by 课程) as 最高分, (select top 1 [score] from tab order by [score] group by 课程) as 最低分 into #tab_课程 from tab select 最高分,最低分 from #tab_课程--drop table tab --drop table #tab_课程1
(
score int
)
insert into @temp(score) values(100);
insert into @temp(score) values(85);
insert into @temp(score) values(50);
insert into @temp(score) values(92);
insert into @temp(score) values(36);
insert into @temp(score) values(89);--MAX VALUE
WITH MaxV AS
(
SELECT score FROM @temp AS T
WHERE NOT EXISTS (SELECT * FROM @temp WHERE score > T.score)
)
,MinV AS
(
SELECT score FROM @temp AS T
WHERE NOT EXISTS (SELECT * FROM @temp WHERE score < T.score)
)
SELECT
X.score AS MaxValue,
I.score AS MinValue
FROM MaxV AS X,MinV AS IMaxValue MinValue
----------- -----------
100 36(1 row(s) affected)
create table #t([studentID] int,[score] int)
insert #t
select 1,72 union all
select 2,67 union all
select 3,54 union all
select 4,90 union all
select 5,53 union all
select 6,79 union all
select 7,88select
(select top 1 [score] from #t order by [score] desc) as 最高分,
(select top 1 [score] from #t order by [score]) as 最低分drop table #t/*
最高分 最低分
----------- -----------
90 53
*/
if object_id('tab') is not null
drop table tab
go
create table tab([studentID] int,[score] int)
insert tab
select 1,72 union all
select 2,67 union all
select 3,54 union all
select 4,90 union all
select 5,53 union all
select 6,79 union all
select 7,88select
(select top 1 [score] from tab order by [score] desc group by 课程) as 最高分,
(select top 1 [score] from tab order by [score] group by 课程) as 最低分
into #tab_课程 from tab select 最高分,最低分 from #tab_课程--drop table tab
--drop table #tab_课程1
课程表tbl_test_course:主键(ID),学生表外键(std_id)课程编号(sn),课程名称(name),课程学分(credit ),课程讲师(teacher)基于课程进行统计(列表显示字段:课程编号、课程名称、平均分数、最低分数、最高分数、及格率)要求:在成绩管理存储过程中,最低分数、最高分数的计算不允许使用系统函数(包括max、min),通过为每一个课程建立临时表的方式实现该功能。我用的是sqlserver2005