select * from tab_1 where id in( select id from ( select id,count(b) from tab_2 where rownum<=10 group by id order by 2 desc ) )
select * from tab_1 where id in( select id from ( select id,count(b) cnt from tab_2 group by id order by cnt desc ) where rownum<=10 ) 把where rownum<=10 放在第二层select 中,一定行,试过的。
请问在哪个版本中调试通过的? 为什么我在805下查询却在order 前面提示缺少右括号 但在9i下可以通过select * from tab_1 where id in (select id from (select id,count(b) from tab_2 group by id order by count(b) desc) where rownum<11);
取表的前N條記錄 介紹取一表前N筆記錄的各种數据庫的寫法... 1. ORACLE SELECT * FROM TABLE1 WHERE ROWNUM<=N 2. INFORMIX SELECT FIRST N * FROM TABLE1 3. DB2 SELECT * ROW_NUMBER() OVER(ORDER BY COL1 DESC) AS ROWNUM WHERE ROWNUM<=N DB2 SELECT COLUMN FROM TABLE FETCH FIRST N ROWS ONLY 4. SQL SERVER SELECT TOP N * FROM TABLE1 5. SYBASE SELECT TOP N * FROM TABLE1 6. mysql: select * from table_name limit N
select * from web_answer_table where user_id in( select user_id from ( select user_id,count(*) from web_answer_table where rownum<=1 group by user_id ))order by score desc;
有二个表:tab_1(id varchar, name varchar) tab_2(id varchar,b number)
其中b是序列中的某一值(tab_2中id和b是一对多的关系)
现需查询出前10个对应b最多的name
Thanks
where id in(
select id from
(
select id,count(b) from tab_2
where rownum<=10
group by id
order by 2 desc
)
)
where id in(
select id from
(
select id,count(b) cnt from tab_2
group by id
order by cnt desc
)
where rownum<=10
)
把where rownum<=10 放在第二层select 中,一定行,试过的。
为什么我在805下查询却在order 前面提示缺少右括号
但在9i下可以通过select * from tab_1
where id in (select id from (select id,count(b) from tab_2
group by id
order by count(b) desc)
where rownum<11);
介紹取一表前N筆記錄的各种數据庫的寫法...
1. ORACLE
SELECT * FROM TABLE1 WHERE ROWNUM<=N
2. INFORMIX
SELECT FIRST N * FROM TABLE1
3. DB2
SELECT * ROW_NUMBER() OVER(ORDER BY COL1 DESC) AS ROWNUM WHERE ROWNUM<=N
DB2
SELECT COLUMN FROM TABLE FETCH FIRST N ROWS ONLY
4. SQL SERVER
SELECT TOP N * FROM TABLE1
5. SYBASE
SELECT TOP N * FROM TABLE1
6. mysql:
select * from table_name limit N
但不知ORDER BY后面能不能跟计算字段。关注中,谁来解答???
where user_id in(
select user_id from ( select user_id,count(*) from web_answer_table
where rownum<=1 group by user_id ))order by score desc;
把嵌套的那部分sql语句创建成一个视图