再出一道 SQL 题:

[playyuer]

二维表 T(F1,F2,F3,F4,F5,F6,F7) 表示如下关系:
-------------------------------------------------------------
│ 学生ID │ 学生姓名 │ 课程ID │ 课程名称 │ 成绩 │
│ S3 │ 王五 │ K4 │ 政治 │ 53 │
│ S1 │ 张三 │ K1 │ 数学 │ 61 │
│ S2 │ 李四 │ K3 │ 英语 │ 88 │
│ S1 │ 张三 │ K4 │ 政治 │ 77 │
│ S2 │ 李四 │ K4 │ 政治 │ 67 │
│ S3 │ 王五 │ K2 │ 语文 │ 90 │
│ S3 │ 王五 │ K1 │ 数学 │ 55 │
│ S1 │ 张三 │ K2 │ 语文 │ 81 │
│ S4 │ 赵六 │ K2 │ 语文 │ 59 │
│ S1 │ 张三 │ K3 │ 英语 │ 37 │
│ S2 │ 李四 │ K1 │ 数学 │ 81 │
│ .... │ │ │ │ │
│ .... │ │ │ │ │
-------------------------------------------------------------

按平均分从高到低,列印:
名次,学生id,学生姓名,平均成绩
1 , ....
2 , ....
注意: 不考虑并列情况,认为所有人平均分都不相等!

[N_chow]

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 Rank

DROP TABLE #tmp

[N_chow]

是 ORDER BY [s成績] DESC

[N_chow]

呃~~好像不對，總共有几門課程? 是不是每名學生的所有課程的成績都記錄在此表內??

[j9988]

不考虑缺考的情况.
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

[Chiff]

select AAA.名次,AAA.学生ID,BBB.学生姓名,AAA.平均成绩 from
(
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

[Yang_]

SELECT 1+ISNULL((SELECT COUNT(*) FROM
(
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

[playyuer]

要求:
一剑封喉
一针见血
一句搞掂

[Yang_]

playyuer(小干部儿):
你怎么老出这么麻烦的呢？

[coolsky]

select 学生ID,avg(成绩) as 成绩 from t group by 学生ID order by 成绩
，利用 N_chow(一劍飄香) 的方法加上位此，存到tmp中，读取。

[Chiff]

select 名次=count(*),min(a.学生ID),min(a.学生姓名),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.平均成绩

[Chiff]

select 名次=count(*),min(a.学生ID),min(a.学生姓名),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(*)

[playyuer]

select count(*)-1,a.学生ID,max(a.学生姓名),max(a.f)
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(*)

[Chiff]

playyuer(小干部儿) 我的对吗？

[playyuer]

Yang_(扬帆破浪) 不愧是 ★★ !

[frice]

真是高手如云啊!