查询未返回所有预期行。

Question

SELECT p.INVOICE_PAYMENT_ID,i.INVOICE_ID,i.INVOICE_NUMBER,i.CLINIC_ID,
    i.INVOICE_DATE,i.PAYMENT_RECEIVED_DATE, i.AMOUNT,
    i.CURRENT_CHARGE,i.AMOUNT_RECEIVED,i.MONTH,i.YEAR,i.COMPANY_ID,
    i.COMPANY_NAME,c.NAME,p.NOTE,i.NOTES
FROM test1 i 
LEFT JOIN test2 c ON (i.CLINIC_ID=c.CLINIC_ID)
LEFT JOIN test3 p ON p.INVOICE_ID=i.INVOICE_ID  
WHERE i.MONTH=11 AND i.YEAR=2011                     
    AND  p.INVOICE_PAYMENT_ID IN (
        SELECT MAX(INVOICE_PAYMENT_ID) 
        FROM test3 WHERE INVOICE_ID=i.INVOICE_ID 
    )

如果内部查询没有值，则不会返回test3中的所有相应行。

我该如何解决这个问题？

- isree

---

如果内部查询没有值，则test3中没有相应的行。你期望会发生什么？ - ean5533

@jinesh确实，你期望什么？（我假设内部查询是具有“max”的查询）。 - aF.

Answer 1

将内部查询从where子句移至join条件：

SELECT p.INVOICE_PAYMENT_ID,i.INVOICE_ID,i.INVOICE_NUMBER,i.CLINIC_ID,
    i.INVOICE_DATE,i.PAYMENT_RECEIVED_DATE, i.AMOUNT,
    i.CURRENT_CHARGE,i.AMOUNT_RECEIVED,i.MONTH,i.YEAR,i.COMPANY_ID,
    i.COMPANY_NAME,c.NAME,p.NOTE,i.NOTES
FROM test1 i 
LEFT JOIN test2 c ON (i.CLINIC_ID=c.CLINIC_ID)
LEFT JOIN test3 p ON p.INVOICE_ID=i.INVOICE_ID and p.INVOICE_PAYMENT_ID = (
        SELECT MAX(INVOICE_PAYMENT_ID) 
        FROM test3 WHERE INVOICE_ID=i.INVOICE_ID)
WHERE i.MONTH=11 AND i.YEAR=2011

- Michał Powaga

---

作为一个小调整，如果你期望在test1的每一行中总是有test2的记录（也就是说每个发票行总是有一个c.NAME），那么你可能希望将该连接改为INNER JOIN test2 c...，因为通常它会更有效率 :) - Simon at My School Portal

Answer 2

您可以更改实际的where子句：

... AND 
    p.INVOICE_PAYMENT_ID IN 
    (SELECT MAX(INVOICE_PAYMENT_ID) 
     FROM test3 WHERE INVOICE_ID=i.INVOICE_ID )

作者：

... AND
(
    not exists 
    (SELECT MAX(INVOICE_PAYMENT_ID) 
    FROM test3 WHERE INVOICE_ID=i.INVOICE_ID ) 
 OR
    p.INVOICE_PAYMENT_ID IN 
    (SELECT MAX(INVOICE_PAYMENT_ID) 
     FROM test3 WHERE INVOICE_ID=i.INVOICE_ID )
)

即使这个月和条件没有数据，它也会返回test3。

- dani herrera