如何在使用join和groupBY时获取Oracle SQL中的最新值
如何在使用join和groupBY时获取Oracle SQL中的最新值
提问于 2018-06-18 00:28:34
SELECT A.CreateDatetime,B.OrderNumber from WORKORDER A
join UPGD B on A.phonenumber=B.number
where B.ACCOUNTNUMBER in
(select ACCOUNTNUMBER,CreateDatetime
from UPGD group BY ACCOUNTNUMBER, CreateDatetime
HAVING COUNT(ACCOUNTNUMBER)=1
ORDER BY CreateDatetime desc)
and ROWNUM='1'
order by A.CreateDatetime desc;表: WORKORDER
---------------------------------------------
CreateDatetime ACCOUNTNUMBER phonenumber
6/17/2018 12345678 3423423534
6/16/2018 87655432 1232412344
5/20/2018 84534523 3424234234表: UPGD
---------------------------------------------
CreateDatetime ACCOUNTNUMBER phonenumber
6/17/2018 12345678 3423423534
6/17/2018 12345678 3423423534
6/16/2018 87655432 1232412344
5/20/2018 84534523 3424234234我正在尝试连接两个表，并获取只有1个Accountnumber且只需要最新字段的字段
当我使用上面的查询时，我得到下面的错误
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:回答 1
Stack Overflow用户
回答已采纳
发布于 2018-06-18 01:13:22
为什么不使用where子句而不是on条件
SELECT A.CreateDatetime,B.OrderNumber from WORKORDER as A
join UPGD B on A.phonenumber=B.number
where B.ACCOUNTNUMBER in
(select ACCOUNTNUMBER from UPGD group BY ACCOUNTNUMBER,CreateDatetime
HAVING COUNT(ACCOUNTNUMBER) = 1
ORDER BY CreateDatetime desc)
and ROWNUM='1'
order by A.CreateDatetime desc;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50898490
复制相关文章
相似问题
腾讯云开发者
