SELECT Rank=IDENTITY(INT,1,1),[學生ID],AVG([成績]) AS [s成績] INTO #tmp
FROM TableName
GROUP BY [學生ID]
ORDER BY [s成績]SELECT * FROM #tmp ORDER BY RankDROP TABLE #tmp
FROM TableName
GROUP BY [學生ID]
ORDER BY [s成績]SELECT * FROM #tmp ORDER BY RankDROP TABLE #tmp
select
(select count(*) from (
select f1,f2,avg(f5) as f from t group by f1,f2 ) b where f>=a.f ) as 名次,f1 as 学生id,f2 学生姓名,avg(f5) as 平均成绩 from t a group by f1,f2 order by f desc
(
select count(*) as 名次,min(a.学生ID) as 学生ID,a.平均成绩
(select 学生ID,平均成绩=sum(成绩)/count(*) from t) as a,
(select 学生ID,平均成绩=sum(成绩)/count(*) from t) as b,
where a.平均成绩 > b.平均成绩
group by a.平均成绩
) as AAA,
t as BBB
where AAA.学生ID = BBB.学生ID
(
SELECT [學生ID],[学生姓名],AVG([成績]) AS [s成績]
FROM TableName
GROUP BY [學生ID],[学生姓名]
) AS B
WHERE [s成績]>A.[s成績]
),0) AS RANK,
[學生ID],[学生姓名],[s成績]
FROM
(
SELECT [學生ID],[学生姓名],AVG([成績]) AS [s成績]
FROM TableName
GROUP BY [學生ID],[学生姓名]
) AS A
ORDER BY [s成績] DESC
你怎么老出这么麻烦的呢?
,利用 N_chow(一劍飄香) 的方法加上位此,存到tmp中,读取。
from
(select 学生ID,学生姓名,平均成绩=sum(成绩)/count(*)
from 表名 group by 学生ID,学生姓名) as a,
(select 学生ID,学生姓名,平均成绩=sum(成绩)/count(*)
from 表名 group by 学生ID,学生姓名) as b
where a.平均成绩> b.平均成绩
group by a.平均成绩
from
(select 学生ID,学生姓名,平均成绩=sum(成绩)/count(*)
from 表名 group by 学生ID,学生姓名) as a,
(select 学生ID,学生姓名,平均成绩=sum(成绩)/count(*)
from 表名 group by 学生ID,学生姓名) as b
where a.平均成绩> b.平均成绩
group by a.平均成绩
order by count(*)
from
(select distinct t.学生ID,t.学生姓名,(select avg(成绩) from t t1 where t1.学生id = t.学生id) as F
from T ) as a,
(select distinct t.学生ID,t.学生姓名,(select avg(成绩) from t t1 where t1.学生id = t.学生id) as F
from T ) as b
where a.f <= b.f
group by a.学生ID
order by count(*)