Question

oracle一对多的表数据查询 ，多表显示对应的最后一条数据

有两张表A (aid,aname) B(bid,aid,bname)其中B表中的aid为A表中的值允许重复
AB 表关系为 一对多
数据
A
aid aname
1 abc
2 bcd

B
bid aid bname
1 1 bbbb
2 1 bbbcc
3 1 bbbddd
4 2 ccccc

我想得出
aid aname bid aid bname
1 abc 3 1 bbbddd
2 bcd 4 2 ccccc

就是取B表的最后一行数据，若果没有就为空，sql语句怎么写， 求指点

Answer 1

这样：

select A.aid,A.aname,B.bid,B.aid,B.bname
from A join B on A.aid = B.aid
where B.bid = (select max(bid) from B as c where A.aid = c.aid)

Answer 2

CREATE TABLE A (
aid INT,
anameVARCHAR2(10)
);

CREATE TABLE B (
bid INT,
aid INT,
bnameVARCHAR2(10)
);

INSERT INTO A
SELECT 1, 'abc'FROM dual UNION ALL
SELECT 2, 'bcd' FROM dual ;

INSERT INTO B
SELECT 1, 1, 'bbbb'FROM dual UNION ALL
SELECT 2, 1, 'bbbcc'FROM dual UNION ALL
SELECT 3, 1, 'bbbddd'FROM dual UNION ALL
SELECT 4, 2, 'ccccc'FROM dual ;

SELECT
distinct
A.aid,
A.aname,
AVG(bid)
KEEP (DENSE_RANK LAST ORDER BY bid)
OVER (PARTITION BY A.aid) AS bid,
AVG(B.aid)
KEEP (DENSE_RANK LAST ORDER BY bid)
OVER (PARTITION BY A.aid) AS bid,
MAX(B.bname)
KEEP (DENSE_RANK LAST ORDER BY bid)
OVER (PARTITION BY A.aid) AS bname
FROM
A LEFT JOIN B
ON (a.aid = b.aid);

查询结果：

AID ANAME BID BID BNAME
---------- -------------------- ---------- ---------- --------------------
1 abc 3 1 bbbddd
2 bcd 4 2 ccccc

Answer 3

select a1.aid,a1.aname,b1.bid,b1.aid,b1.name from A a1 ,B b1 where a1.aid=b1.aid