tablename 的主健是id ,historytablename:
id name history
1 name1 1
1 name2 2
1 name3 3
2 name4 1
2 name5 2
3 name6 1
3 name7 2
3 name8 3
3 name9 4
4 name10 1
5 name11 1
6 name12 1怎么查询得到最大history的前3条纪录:
id name history
1 name3 3
2 name5 2
3 name9 4用这样的sql显然是达不到要求了:SELECT a.id,a.name
FROM (SELECT ROWNUM AS rnt,aa.* FROM tablename aa ) a ,
(SELECT id, max(history) AS history from tablename group by id) b
WHERE
a.id=c.id
AND a.history = c.history
AND a.rnt >= 1
AND a.rnt <= 3
ORDER BY a.id
id name history
1 name1 1
1 name2 2
1 name3 3
2 name4 1
2 name5 2
3 name6 1
3 name7 2
3 name8 3
3 name9 4
4 name10 1
5 name11 1
6 name12 1怎么查询得到最大history的前3条纪录:
id name history
1 name3 3
2 name5 2
3 name9 4用这样的sql显然是达不到要求了:SELECT a.id,a.name
FROM (SELECT ROWNUM AS rnt,aa.* FROM tablename aa ) a ,
(SELECT id, max(history) AS history from tablename group by id) b
WHERE
a.id=c.id
AND a.history = c.history
AND a.rnt >= 1
AND a.rnt <= 3
ORDER BY a.id
SELECT ROWNUM AS rnt,a.id,a.name
FROM (SELECT aa.* FROM tablename aa ) a ,
(SELECT id, max(history) AS history from tablename group by id) b
WHERE
a.id=b.id
AND a.history = b.history
)
where rnt >= 1
AND rnt <= 3
ORDER BY id或者下面这样:
select * from (
select a.*,row_number() over(partition by id order by history desc) rn,
dense_rank() over(order by id) dr
from tablename
)where rn =1 and dr >=1 and dr <=3
(select id,max(history) from table_name group by id)
where rownum = 3
是 <= 3select * from
(select id,max(history) from table_name group by id)
where rownum <= 3
from (
select a.*,
row_number() over(partition by id order by history) rk
from score)
where rk<4
id name history
1 name1 1
1 name2 2
1 name3 3
2 name4 1
2 name5 2
3 name6 1
3 name7 2
3 name8 3
3 name9 4
4 name10 1
5 name11 1
6 name12 1怎么查询得到最大history的前3条纪录:
id name history
1 name3 3
2 name5 2
3 name9 4
根据对楼主的理解,SQL如下。
select b.*
from (select a.id,a.name,a.history
from (select id,name,history,row_number()
over(partition by id order by history desc) as num from tablename) a
where a.num=1) b
where b.rownum<=3