取各学科平均分排名前10的学生 语句问题 到底哪里错了？？

[michaelgy]

学生考试， 一年考试N次， 取各学科平均分排名前10的学生

select a.* from
(
select studentid, avg(score) as score1, subject from tableA
group by studentid , subject
) a
where
(
select count(1) from (
select studentid, avg(score) as score1, subject from tableA
group by studentid , subject ）b
where a.studentid = b.studentid and a.subject = b.subject and b.score1> a.score1) < 10
)
order by a.subject , a.score1 desc

[sparrow_liu]

top ？ oracle能用么？

[fosjos]

select t.* from
(
select subject , studentid , avg(score) score
,row_number() over (partition by subject order by avg(score) desc) as num
from tb group by subject , studentid
)t
where num<=10

[dawugui]

各学科平均分排名前10的学生 ?

应该是各学科得分排名前10的学生吧?

select * from tb t
where score in 
(
  select top 10 score from tb where subject=t.subject order by score desc
)
order by subject , score desc

[dawugui]

--明白了,是一个人一年当中这个学科的平均分.

select t.* from 
(
  select subject , studentid , avg(score) score from tb group by subject , studentid 
) tb t
where score in 
(
  select top 10 score from (select subject , studentid , avg(score) score from tb group by subject , studentid) m where subject=t.subject order by score desc
)
order by subject , score desc

[tanjun132]

top oracle传说中不可以用吧

[doer_ljy]

fosjos的正解
使用partition by 按照科目分组在分组内使用over的Order by对成绩均值排序用rank给她们加上序号，然后在父查询中过滤掉rownum大于10的记录。

[doer_ljy]

fosjos 正解
使用rank over(...)为每个合计加上科目内的序号。
然后再外层循环中过滤掉序号大于10的。

[zjkk1011]

top?

[liuyann]

顶

[hz89cool]

row_number()是什么啊?OVER 是什么.他们怎么用啊?

[sohome]

学到东西

[liuyann]

顶

[wzjpsq]

up

[认真读书]

顶。。不知道。。网上搜一下，就全明白了

[HelloWorld_001]

[Quote=引用 9 楼 hz89cool 的回复:]
row_number()是什么啊?OVER 是什么.他们怎么用啊?
[/Quote]
这个是分析函数。
自己去查用法

[zgmeimei]

路过,顶一下,支持LZ

------------------------------------------------

给灌水区的兄弟们推荐一个养眼的网站，嘿嘿。
http://www.zgmeimei.cn

[pccastle]

ding

[pccastle]

哈哈··我也来··jf

[meiZiNick]

以后需再关注，现在先帮你顶一下