Oracle连接表

Question

我在执行oracle sql时遇到了问题，第一个代码示例在执行时工作正常，而第二个代码示例出现错误(‘无效数字’)，两者都应该产生相同的结果。请问第二个sql出了什么问题？请在这方面给我指点一下。

第一个SQL

    select a5.LOTID,COUNT(a5.stephandle) AS STEPHANDLE from LOTFLOW_D a5,

    (select distinct(LOTID),MAX(STEPHANDLE) AS STEPHANDLE from Lotmove_R
    where QTAP_GROUP In ('8685','8686','8687','8688','8689','8533','8532','2534','2533','8684','8690')
    and datadate>sysdate-3
    and priority<=5
    and SUBPLANID NOT LIKE '%RWK%' AND STEPHANDLE NOT LIKE '%8888%' AND STEPHANDLE LIKE '%.%.%'
    group by lotid) a6

    where a5.lotid=a6.lotid 
    and a5.STEPHANDLE > a6.STEPHANDLE AND A5.EQPTYPE NOT IN ('CUDDFINP','CUBDFINP','BDFINP','DDFINP','CUDFSEM','DFSEM','SORT','CUSORT','CUNPURGE','NPURGE','EDFINP','CUEDFINP')

    group by a5.lotid
    order by a5.lotid 

第一个sql的输出(结果正是我想要的)

LOTID    STEPHANDLE
_____    ___________
S8CN9      214
S7JY7      30
 ...      .....

第二个SQL

Select a5.lotid,a5.STEPHANDLE from 

(select lotid,COUNT(stephandle) as STEPHANDLE, EQPTYPE from LOTFLOW_D Group by lotid, EQPTYPE ) a5

left join

(select distinct(LOTID), MAX(STEPHANDLE) AS STEPHANDLE from Lotmove_R
where QTAP_GROUP In ('8685','8686','8687','8688','8689','8533','8532','2534','2533','8684','8690')
and datadate>sysdate-3
and priority<=5
and SUBPLANID NOT LIKE '%RWK%' AND STEPHANDLE NOT LIKE '%8888%' AND STEPHANDLE LIKE '%.%.%'
group by lotid) a6 on a5.lotid=a6.lotid

where a5.STEPHANDLE > a6.stephandle AND a5.EQPTYPE NOT IN ('CUDDFINP','CUBDFINP','BDFINP','DDFINP','CUDFSEM','DFSEM','SORT','CUSORT','CUNPURGE','NPURGE','EDFINP','CUEDFINP')

order by a5.lotid 

第二个出现错误(最大值( stephandle )的无效数字)，因为stephandle的值类似于1470.0.0400，我知道这不是一个数字，但它在使用第一个sql时工作得很好，并且满足了我的预期结果(在number中显示)，我尝试了count(stephandle)，但结果是错误的，我需要第二个sql格式在我的主sql上运行左连接

Answer 1

尝试在a5查询中使用TO_CHAR作为TO_CHAR(COUNT (stephandle))。这将在以后将您的值作为字符串进行比较，这很好(在第一个查询中也是如此)。

我应该指出的是，在where子句中引用任何a6列都会将左连接转换为内连接。如果您确实希望查询充当左连接，而不是

          ON a5.lotid = a6.lotid
WHERE     a5.STEPHANDLE > a6.stephandle

使用：

ON (a5.lotid = a6.lotid AND a5.STEPHANDLE > a6.stephandle)

我还详细地提到了这个问题作为这个问题的答案：Why can't you use OR or IN with a OUTER JOIN operation?

完整代码：

SELECT a5.lotid, a5.STEPHANDLE
FROM (  SELECT lotid, TO_CHAR (COUNT (stephandle)) AS STEPHANDLE, EQPTYPE
          FROM LOTFLOW_D
      GROUP BY lotid, EQPTYPE) a5
     LEFT JOIN (  SELECT DISTINCT (LOTID), MAX (STEPHANDLE) AS STEPHANDLE
                    FROM Lotmove_R
                   WHERE     QTAP_GROUP IN ('8685',
                                            '8686',
                                            '8687',
                                            '8688',
                                            '8689',
                                            '8533',
                                            '8532',
                                            '2534',
                                            '2533',
                                            '8684',
                                            '8690')
                         AND datadate > SYSDATE - 3
                         AND priority <= 5
                         AND SUBPLANID NOT LIKE '%RWK%'
                         AND STEPHANDLE NOT LIKE '%8888%'
                         AND STEPHANDLE LIKE '%.%.%'
                GROUP BY lotid) a6
         ON (a5.lotid = a6.lotid AND a5.STEPHANDLE > a6.stephandle)
WHERE a5.EQPTYPE NOT IN ('CUDDFINP',
                         'CUBDFINP',
                         'BDFINP',
                         'DDFINP',
                         'CUDFSEM',
                         'DFSEM',
                         'SORT',
                         'CUSORT',
                         'CUNPURGE',
                         'NPURGE',
                         'EDFINP',
                         'CUEDFINP')
ORDER BY a5.lotid

我希望这能有所帮助:)