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
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
解决方案 »
- Oracle 动态触发器
- 如何获取sql语句的绑定的变量
- 请教高手这个存储过程怎么调用?
- emca 报错 ocijdbc11找不到 求教
- SQL查询问题
- 关于trigger 的一个问题
- ORACAL入门从那开始啊
- 请教为什么在windows2000里的管理工具services里没有OracleoraHome90ManagementServer?
- windows下装好oracle8i,进入SQL PLUS提示协议适配器错误是怎么回事情?怎样改正。
- 不加表前缀访问其他用户数据?
- 在oracle存储过程中出现:PLS-00103:出现符号“DELETE”在需要下列之一时
- oracle 复杂查询,大牛挑战自己吧!来吧!!!
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
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