(1)select 姓名,班级,成绩 from 表 a where (select COUNT(1) FROM 表 where 班级=a.班级 and 姓名>a.姓名)<=2
(2)select 姓名,班级,成绩 from 表 a where 姓名 in(select top 3 姓名 from 表 where 班级=a.班级 order by 成绩 desc)
(3)select 姓名,班级,成绩 from 表 a where not exsits(select 1 from 表 where 班级=a.班级 and 成绩>a.成绩 HAVING count(*)>=3)
set @classid=''
select @classid=@classid+convert(varchar,a.classparentid)+','
from(select distinct classparentid from uds_Class where classparentid<>-1) a
select @classid
select substring(@classid,charindex(',',@classid)+1,len(@classid))
declare @tempid int
while len(@classid)>0
begin
set @tempid =convert(int,substring(@classid,1,charindex(',',@classid)-1))
set @classid=substring(@classid,charindex(',',@classid)+1,len(@classid))
insert into #classid
select top 3 classid
from uds_Class
where ClassParentID=@tempid
end
select count(*) from uds_class where classparentid in (select * from #classid)