求个sql

[chao121]

A表
id name
1 a1
2 a2

B表
id Aid create_time
1 1 2015-06-23 12:00:00
2 1 2015-06-22 12:00:00
3 2 2015-02-21 12:00:00
4 2 2015-01-21 12:00:00


a1 2015-06-23 12:00:00
a2 2015-02-21 12:00:00
怎么写sql 查出这样的值？ 获取B表里面最新时间的数据，A表和B表是一对多

求oracle的sql

[chengccy]

如果只是这两个字段,group by +max 就行了

WITH A(id,name) AS
(SELECT 1, 'a1' FROM DUAL
UNION ALL
SELECT 2, 'a2' FROM DUAL)
, B(id,Aid,create_time) AS 
(SELECT 1, 1, '2015-06-23 12:00:00' FROM DUAL UNION ALL
SELECT 2, 1, '2015-06-22 12:00:00' FROM DUAL UNION ALL
SELECT 3, 2, '2015-02-21 12:00:00' FROM DUAL UNION ALL
SELECT 4, 2, '2015-01-21 12:00:00' FROM DUAL)
SELECT A.NAME,MAX(B.CREATE_TIME) FROM A
LEFT JOIN B
ON B.AID=A.ID
GROUP BY A.NAME

[chengccy]

如果还需要其他不能聚合的字段,就只能order by +分析函数了

WITH A(id,name) AS
(SELECT 1, 'a1' FROM DUAL
UNION ALL
SELECT 2, 'a2' FROM DUAL)
, B(id,Aid,create_time) AS 
(SELECT 1, 1, '2015-06-23 12:00:00' FROM DUAL UNION ALL
SELECT 2, 1, '2015-06-22 12:00:00' FROM DUAL UNION ALL
SELECT 3, 2, '2015-02-21 12:00:00' FROM DUAL UNION ALL
SELECT 4, 2, '2015-01-21 12:00:00' FROM DUAL)
SELECT *
FROM   A
LEFT   JOIN (SELECT B.*,
                    ROW_NUMBER() OVER(PARTITION BY AID ORDER BY CREATE_TIME DESC) RN
             FROM   B) B
ON     B.AID = A.ID AND
       B.RN = 1

[碧水幽幽泉]

楼上给出的答案是正确的，方法有两种： 1.使用聚合函数：group by+max 2.使用分析函数：row_number()over+order by create_time desc

[chao121]

如果B表里面有很多条数据，2楼那样的写法还可行？

[chao121]

如果B表里面有很多条数据，2楼那样的写法还可行？

引用 1 楼 chengccy 的回复:

如果只是这两个字段,group by +max 就行了

WITH A(id,name) AS
(SELECT 1, 'a1' FROM DUAL
UNION ALL
SELECT 2, 'a2' FROM DUAL)
, B(id,Aid,create_time) AS 
(SELECT 1, 1, '2015-06-23 12:00:00' FROM DUAL UNION ALL
SELECT 2, 1, '2015-06-22 12:00:00' FROM DUAL UNION ALL
SELECT 3, 2, '2015-02-21 12:00:00' FROM DUAL UNION ALL
SELECT 4, 2, '2015-01-21 12:00:00' FROM DUAL)
SELECT A.NAME,MAX(B.CREATE_TIME) FROM A
LEFT JOIN B
ON B.AID=A.ID
GROUP BY A.NAME

[碧水幽幽泉]

引用 5 楼 chao121 的回复:

如果B表里面有很多条数据，2楼那样的写法还可行？[quote=引用 1 楼 chengccy 的回复:] 如果只是这两个字段,group by +max 就行了

WITH A(id,name) AS
(SELECT 1, 'a1' FROM DUAL
UNION ALL
SELECT 2, 'a2' FROM DUAL)
, B(id,Aid,create_time) AS 
(SELECT 1, 1, '2015-06-23 12:00:00' FROM DUAL UNION ALL
SELECT 2, 1, '2015-06-22 12:00:00' FROM DUAL UNION ALL
SELECT 3, 2, '2015-02-21 12:00:00' FROM DUAL UNION ALL
SELECT 4, 2, '2015-01-21 12:00:00' FROM DUAL)
SELECT A.NAME,MAX(B.CREATE_TIME) FROM A
LEFT JOIN B
ON B.AID=A.ID
GROUP BY A.NAME

[/quote] 这个方法也是可行的。

[manageroninstall]

两个表一关联取rownum不就完了