求 最终的结果为 排名第二的信息,结果为: AID NAME BNAME CNAME SOCRE 2 张三2 学号2 艺术二课 20 请问 sql语句怎么写?
select a.*, b.*, c.* from a,b,c where a.id = b.id and a.id = c.id and a.id = 2
A表中的AId不一定是连续的,不能以 where a.aid=2 这样查,这样的话太简单了。
select * from a left join b on a.bid = b.bid left join c on b.cid=c.cid where rownum<=3 and not exists ( select * from a left join b on a.bid = b.bid left join c on b.cid=c.cid where rownum<=2) 这样结果为什么是null的
要返回前面3行,同时前面2行还要不存在?你这不是为难ORACLE么?
select a.aid,a.name,b_name,cname,score from b,a, (select row_number() over(order by score desc) rn,c.* from C ) z where rn=2 and b.cid=z.cid and a.bid=b.bid
select aid,name,bname,score from ( select a.aid,a.name,b.bname,c.cname,c.score, rank()over(order by c.score) rank_num from a,b,c where a.bid=b.bid and b.cid=c.cid ) where rank_num=2
SELECT a.* FROM (SELECT a.aid aid,a.name NAME,b.bname bname,c.cname cname,c.score score,ROWNUM rn FROM a_table a,b_table b ,c_table c WHERE a.bid=b.bid AND b.cid=c.cid AND ROWNUM <=2 ORDER BY score) a WHERE a.rn>1
select * from a inner join b on a.aid=b.bid inner join c on a.aid=b.bid where a.name= 张三2
直接写ROWNUM可能会有问题,要嵌套一下。 SELECT * FROM (SELECT T.*, ROWNUM RN FROM (SELECT A.AID, A.NAME, B.BNAME, C.CNAME, C.SOCRE FROM A LEFT JOIN B ON B.BID = A.BID LEFT JOIN C ON C.CID = B.CID ORDER BY C.SCORE) T) T WHERE RN = 2
AID NAME BNAME CNAME SOCRE
2 张三2 学号2 艺术二课 20 请问 sql语句怎么写?
where a.id = b.id and a.id = c.id
and a.id = 2
from a left join b on a.bid = b.bid
left join c on b.cid=c.cid where rownum<=3 and not exists
( select *
from a left join b on a.bid = b.bid
left join c on b.cid=c.cid where rownum<=2)
这样结果为什么是null的
(select row_number() over(order by score desc) rn,c.* from C ) z
where rn=2 and b.cid=z.cid and a.bid=b.bid
select a.aid,a.name,b.bname,c.cname,c.score,
rank()over(order by c.score) rank_num
from a,b,c
where a.bid=b.bid and b.cid=c.cid
) where rank_num=2
WHERE a.bid=b.bid AND b.cid=c.cid AND ROWNUM <=2 ORDER BY score) a WHERE a.rn>1
inner join b
on a.aid=b.bid
inner join c
on a.aid=b.bid
where a.name= 张三2
SELECT *
FROM (SELECT T.*, ROWNUM RN
FROM (SELECT A.AID, A.NAME, B.BNAME, C.CNAME, C.SOCRE
FROM A
LEFT JOIN B
ON B.BID = A.BID
LEFT JOIN C
ON C.CID = B.CID
ORDER BY C.SCORE) T) T
WHERE RN = 2