假如学生成绩表:姓名 班级 成绩
我现在写一个存储过程:要取出每个班里成绩为前三名的记录,如何实现?预期结果是:
张三 1 100
李四 1 98
王五 1 97
马六 2 99
赵七 2 99
钱八 2 90
孙九 3 95
。
。
。
我现在写一个存储过程:要取出每个班里成绩为前三名的记录,如何实现?预期结果是:
张三 1 100
李四 1 98
王五 1 97
马六 2 99
赵七 2 99
钱八 2 90
孙九 3 95
。
。
。
调试欢乐多
select top 3 *
from table_name
group by 班级
order by 成绩
AS
declare @strsql varchar(2000)
declare @class varchar(20)
set @strsql = ""
declare cs CURSOR for select class from scoretable group by class
open cs
FETCH NEXT FROM cs into @class
set @strsql=@strsql+"select top 3 * from scoretable order by score desc"
while (@@fetch_status = 0)
begin
set @strsql=@strsql+"uion select top 3 * from scoretable order by score desc"
FETCH NEXT FROM cs into @class
end
CLOSE cs
DEALLOCATE cs
exec(@strsql)
go
CREATE PROCEDURE gettop3scoretable
AS
declare @strsql varchar(2000)
declare @class varchar(20)
set @strsql = ""
declare cs CURSOR for select class from scoretable group by class
open cs
FETCH NEXT FROM cs into @class
set @strsql=@strsql+"select top 3 * from scoretable order by score desc"
while (@@fetch_status = 0)
begin
FETCH NEXT FROM cs into @class /*反过来*/
set @strsql=@strsql+"uion select top 3 * from scoretable order by score desc"
end
CLOSE cs
DEALLOCATE cs
exec(@strsql)
go
AS
declare @strsql varchar(2000)
declare @class varchar(20)
set @strsql = ""
declare cs CURSOR for select class from scoretable group by class
open cs
FETCH NEXT FROM cs into @class
set @strsql=@strsql+"select top 3 * from scoretable where class=@class order by score desc"
while (@@fetch_status = 0)
begin
FETCH NEXT FROM cs into @class
set @strsql=@strsql+"uion select top 3 * from scoretable where class=@class order by score desc"
end
CLOSE cs
DEALLOCATE cs
exec(@strsql)
go
from table_name
group by 班级
order by 成绩
正解,何必用存储过程和游标,简单问题复杂化
select top 3 *
from table_name
group by 班级
order by 成绩
正解,何必用存储过程和游标,简单问题复杂化
试试,肯定不可以实现的