请问要在oracle数据库里查满足条件的前10条记录,sql该怎么写?

tager 2004-10-22 04:44:20
我的SQL是这以写的:
String sql = "select top 10 distinct username, sum(http) from PROXY_DAILY4USER_REPORT where year=" + year + "and month=" + month + " group by username order by sum(http) desc";

报错:

The source of this error is javax.servlet.ServletException: ORA-00923: 未找到预期 FROM 关键字

把 top 10 去掉就没错误,请问这个sql该怎写?
...全文
146 点赞 收藏 14
写回复
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
studyday 2004-10-22
select * from (select http,rank() over(partition by order by sum(http) desc) tk from yourtablename group by http
where tk < 10 ;
回复
studyday 2004-10-22
呵,应该是 select * from (select http,rank() over(order by sum(http) desc) from yourtablename group by http
where tk < 10 ;
回复
studyday 2004-10-22
select * from (select http,rank() over(partition by order by sum(http) desc) from yourtablename group by http
where tk < 10 ;
回复
tager 2004-10-22
谢谢大家,下面的我试了下面SQL,是正确的。


String sql ="SELECT * FROM ( SELECT C.*, ROWNUM AS NUMROW FROM ( "
+ 你的sql语句
+") C ) "
+ "WHERE NUMROW >0 AND NUMROW <= 10";
回复
tom2005 2004-10-22
rownum<11
回复
jnu_wen 2004-10-22
哦,写错了是rownum <= 10
回复
jnu_wen 2004-10-22
写rownum = 10吧
回复
XMAN197751 2004-10-22
直接在where后面家上条件rownum <=10 就行了。
回复
jinsfree 2004-10-22
select * from tablename where rownum<11
回复
tager 2004-10-22
谢谢,我在试!
回复
yeshucheng 2004-10-22
我也长见识了:)
回复
bluelamb 2004-10-22
select * from (select distinct username,rownum, sum(http) from PROXY_DAILY4USER_REPORT where year=" + year + "and month=" + month + " group by username order by sum(http) desc)
where rownum<=10
回复
dophin1003 2004-10-22
String sql ="SELECT * FROM ( SELECT C.*, ROWNUM AS NUMROW FROM ( "
+ 你的sql语句
+") C ) "
+ "WHERE NUMROW >0 AND NUMROW <= 10";
回复
java200408 2004-10-22
oracle 不支持Top的。top是sqlserver的。
oracle中用rownum实现。
代码如下:(如果不想排序两层就够了)
select * from(
select rownum myrownum,authorid,postnum from
(
select authorid,postnum from bbs_authors order by postnum desc

))
where myrownum between 10 and 20
回复
发动态
发帖子
Web 开发
创建于2007-09-28

7.9w+

社区成员

Java Web 开发
申请成为版主
社区公告
暂无公告