在成绩管理存储过程中，最低分数、最高分数的计算不允许使用系统函数（包括
max、min），通过为每一个课程建立临时表的方式实现该功能。
------解决方案--------------------
难道要用冒泡排序 求最大的？？？？
------解决方案--------------------
不理解楼主的意思，存储过程中可以使用min 和max 呀
------解决方案--------------------
存储过程中可以使用min 和max
每一个课程建立临时表的方式实现最低分数、最高分数的计算？
------解决方案--------------------
- SQL code
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 VALUEWITH 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 MinValueFROM MaxV AS X,MinV AS IMaxValue MinValue----------- -----------100 36(1 row(s) affected)
------解决方案--------------------
- SQL code
create table #t([studentID] int,[score] int)insert #tselect 1,72 union allselect 2,67 union allselect 3,54 union allselect 4,90 union allselect 5,53 union allselect 6,79 union allselect 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?
------解决方案--------------------
- SQL code
if object_id('tab') is not nulldrop table tabgocreate table tab([studentID] int,[score] int)insert tab select 1,72 union allselect 2,67 union allselect 3,54 union allselect 4,90 union allselect 5,53 union allselect 6,79 union allselect 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