假如有一个表如下
No Name Course Score0 Jack Math 60
2 Mary Geography 51
3 Jack Math 81
4 Jack Math 71
5 Mary Biology 90
7 Jack Biology 71我要求去掉Name和Course的重复项,但是Score最重复项中的最大值,就是说,执行某条select语句之后,得出的结果是(就是取Jack Math相同项中Score最大值):No Name Course Score2 Mary Geography 51
3 Jack Math 81
5 Mary Biology 90
7 Jack Biology 71请问这样的select语句怎样写啊?请大家帮帮忙。
No Name Course Score0 Jack Math 60
2 Mary Geography 51
3 Jack Math 81
4 Jack Math 71
5 Mary Biology 90
7 Jack Biology 71我要求去掉Name和Course的重复项,但是Score最重复项中的最大值,就是说,执行某条select语句之后,得出的结果是(就是取Jack Math相同项中Score最大值):No Name Course Score2 Mary Geography 51
3 Jack Math 81
5 Mary Biology 90
7 Jack Biology 71请问这样的select语句怎样写啊?请大家帮帮忙。
(select no from tab b
where a.name=b.name and a.Course=b.Course and a.score<b.score)
from tab a,
(select a1.Name||a1.Course as xxxx,max(a1.score) as max_score from tab a1
group by a1.Name||a1.Course) b
where a.name||a.Course =b.xxxx
and a.score=b.max_score
*
from
tab a
where
a.score = (
select
max(b.score)
from
tab b
where
b.name = a.name
and b.course = a.course
(
select Name,Course,max(Score) from tab group by Name,Course order by id
) maxScore, tab
where tab.Score = maxScore.Score
and tab.Name= maxScore.Name
and tab.Course= maxScore.Course
但是如果有重复项怎样去掉呢?
如果原表是
0 Jack Math 60
2 Mary Geography 51
3 Jack Math 81
4 Jack Math 71
5 Mary Biology 90
7 Jack Biology 71
9 Jack Math 81这样第3行和第9行就重复了,但楼上各位的sql都会把重复的选出来..结果会是
2 Mary Geography 51
3 Jack Math 81
5 Mary Biology 90
7 Jack Biology 71
9 Jack Math 81
即3和9只取一行,可以吗?
from
(select name,Course,max(Score) Score from table group by name,Course)a
(select * from table)b
where a.name=b.name and a.Score=b.Score
(
select *,row_number() over(partition by Name,Course,Score order by id)) rn from
(
select Name,Course,max(Score), from tab group by Name,Course order by id
) maxScore, tab
where tab.Score = maxScore.Score
and tab.Name = maxScore.Name
and tab.Course = maxScore.Course
)
where rn = 1可能写复杂了
MAX(No )KEEP (DENSE_RANK LAST ORDER BY Score),
--MAX(... )KEEP (DENSE_RANK LAST ORDER BY Score),
MAX(Score)KEEP (DENSE_RANK LAST ORDER BY Score) /*即max(Score),如果列多,形式相同效率高*/from t
group by Name, Course