mysql - 如何在Oracle SQL中使用join和groupBY时获取最新值

Question

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;

表:工作订单

---------------------------------------------
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 个帐号并且只需要最新字段的字段

当我使用上述查询时，出现以下错误

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:

Answer 1

在SQL查询中你的第一个错误是加入第二个条件

因为B.ACCOUNTNUMBER应该检查一列而不是两列

为什么不使用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;