oracle有三个表假设为A,B,C。其中A包括字段id,name,time等。B表为表A和C的关系表,包括字段aid,cid,w等,其中aid为A的id,cid为C的id,w是排序字段,基本是一个aid对应多个cid,而一个cid可能同时被多个aid对应。比如
aid cid w
------------
1 1 22
1 2 12
2 1 33
2 3 4 现在想先通过A中的time排序取出前10条A的id,然后去B里面找出对应这10个aid的cid,不过不是全部查出,而是每个aid只查出w最大的前3个cid,即返回10*3=30条记录。请问怎么写sql语句?是否必须用游标,谢谢
aid cid w
------------
1 1 22
1 2 12
2 1 33
2 3 4 现在想先通过A中的time排序取出前10条A的id,然后去B里面找出对应这10个aid的cid,不过不是全部查出,而是每个aid只查出w最大的前3个cid,即返回10*3=30条记录。请问怎么写sql语句?是否必须用游标,谢谢
解决方案 »
- 创建数据库实例报错:ORA-03113:end-of-file on communication channel
- 怎样lock table让别的事务查询表都不可以
- 高分请教 oracle 异常的捕获问题
- Oracle 到底有沒有 GUI 介面,好似SQL Server Studio Managment 的....
- Oracle无级联更新?那应该怎么自己实现?
- 超难(SQL)
- truncate
- oracle817的问题
- sql plus中提示"ORA-12541:TNS 没有监听器" 但tns listener 可以正常启动!
- oracle9在linux9上的安装问题
- 请教:all_all_tables left outer join all_tab_comments 速度奇慢,如何调优,谢谢!
- oracle新手求救,怎样可以不使用用户名.表名查询表?
from
(select aid,cid,w,
ROW_NUMBER() OVER (PARTITION BY aid ORDER BY w desc) as rn
from B
where aid in
(select aid
from (select aid,ROW_NUMBER() OVER (ORDER BY time desc) as rn from A )
where rn<=10))
where rn<=3
select t.aid, t.cid, t.w, t.rownum rn
from (select b.aid, b.cid, b.w
from b
where b.aid in (select a.aid
from (select a.* from a order by a.time)
where rownum <= 10)
order by b.aid, w desc) t
)select td.aid, td.cid, t.w
from temp_data td,
(select td.aid, max(td.rn) max_rn from temp_Data td group by td.aid) t
where td.aid = t.aid
and (td.rn = t.max_rn or td.rn = t.max_rn - 1 or td.rn = t.max_rn - 2)--如果没有三条记录就取两条或一条
CREATE TABLE A(
ID NUMBER,
NAME VARCHAR2(10),
TIME DATE);CREATE TABLE B
(
aid NUMBER,
cid NUMBER,
w NUMBER
);
INSERT INTO A VALUES (1,'A',SYSDATE-1);
INSERT INTO A VALUES (2,'B',SYSDATE-2);
INSERT INTO A VALUES (3,'C',SYSDATE-3);
INSERT INTO A VALUES (4,'D',SYSDATE-4);INSERT INTO B VALUES (1,2,5);
INSERT INTO B VALUES (1,2,4);
INSERT INTO B VALUES (1,3,3);
INSERT INTO B VALUES (1,2,2);
INSERT INTO B VALUES (1,2,1);
INSERT INTO B VALUES (2,3,8);
INSERT INTO B VALUES (2,2,7);
INSERT INTO B VALUES (2,2,6);
INSERT INTO B VALUES (2,3,3);
INSERT INTO B VALUES (3,2,1);
INSERT INTO B VALUES (3,2,2);
INSERT INTO B VALUES (3,3,3);
INSERT INTO B VALUES (4,2,1);
INSERT INTO B VALUES (4,2,2);
INSERT INTO B VALUES (4,3,3);
WITH TOP2_OF_A AS
(
---- 取出A中time最大的2个ID
SELECT ID
FROM (SELECT *
FROM A
ORDER BY A.TIME DESC)
WHERE ROWNUM <= 2 ---- 这里决定取 A中的time排序取出前2条A的id
)SELECT R.AID,
R.CID,
R.W
FROM (SELECT B.*,
ROW_NUMBER() OVER(PARTITION BY B.AID ORDER BY B.W DESC) AS R_NUM
FROM B
WHERE B.AID IN (SELECT *
FROM TOP2_OF_A)) R
WHERE R.R_NUM <= 3 ----这里决定取W最大的前3个cid
select *
from(
select a.*,b.*,
rank() over(order by a.time asc) an,--a行号
row_number() over(partition by b.aid order by b.aid,b.w asc) bn--b行号
from a,
b
where a.id = b.aid(+)
)
where an<11
and bn<4
and aid is not null