select name,modedate from (select name,modedate from table
where (name='a')
order by modedate desc)
where rownum<6
union
select name,modedate from (select name,modedate from table
where (name='b')
order by modedate desc)
where rownum<6
union
select name,modedate from (select name,modedate from table
where (name='c')
order by modedate desc)
where rownum<6期待更好的方法
where (name='a')
order by modedate desc)
where rownum<6
union
select name,modedate from (select name,modedate from table
where (name='b')
order by modedate desc)
where rownum<6
union
select name,modedate from (select name,modedate from table
where (name='c')
order by modedate desc)
where rownum<6期待更好的方法
select a.学生name,a.课程name,a.成绩 from
(select 学生name,课程name,成绩,row_number()
over(partition by 课程name order by 成绩 desc) top3 from 你的表) a
where a.top3 <= 3
楼主的问题照改就是了!
现在需要查找name为‘A’,‘B',‘C'的各自最新的5条记录。select a.* from
(select a.*,row_number(partition by name,order by modedate) rn from a) a
where a.name in ('A','B','C') and a.rn<=5;
没有测试过,自己调试一下.
From (Select Tablename.*,
Row_Number() Over(Partition By Name Order By Modedate) Rk
From Tablename)
Where Rk <= 5
FROM (SELECT a.*,
Row_Number() Over(PARTITION BY a.NAME ORDER BY a.Modedate) Rn
FROM Tablename a)
WHERE Rn <= 5
AND NAME IN ('A', 'B', 'C')
OVER
(<Partition-clause>,<Order-by-Clause><Windowing Clause>)注:
argument有0~3个参数,可以是表达式
OVER 区分是聚集函数还是分析函数
Partition-clause 可选的分区子句,如果没有则将结果集视为一个单一的大区。分析函数主要就是作用于分区结果。
Order-by-Clause 可选,针对分组内的排序