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
解决方案 »
- 想去改变这个排序,谁有办法,大家一起学习学习。
- Oracel中删除表空间和用户的语法
- Oracle 统计问题
- 请教在ORACLE中多个JOB同时执行的效率和对数据库影响问题
- 我用exp导出的数据,用imp导入不进去
- 紧急求助,哪儿有ORACLE 9I简体中文版下载?
- Oracle9i中引进了merge这样一个命令操作,请问该命令的语法格式怎样,能否举个例子,谢谢?
- SQL语句错误吗?
- 请看看怎么安装出错?
- 关于单位工资统计的复合查询不会写,求各路大神支援,指点迷津
- BLOB/CLOB/LONG/LongRaw数据类型有什么区别呢?
- 为什么通过dblink update不同库中的表的where中使用自定义函数报错?
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