mysql - 从子内连接选择引用父字段

Question

我有一个从表中选择记录并左连接其他两个表的查询。

这是查询

SELECT mainTest.lab_number_auto, mainTest.lab_number,
SUM(CASE WHEN test.lab_number = mainTest.lab_number
    THEN test.test_unit_cost ELSE 0 END) cost,
payment.totalPaid

FROM patient_main_test mainTest

LEFT JOIN patient_test test    
ON test.lab_number = mainTest.lab_number 

LEFT JOIN (
    SELECT SUM(CASE WHEN testpayment.lab_number = mainTest.lab_number THEN testpayment.amount_paid ELSE 0 END) totalPaid
    FROM patient_test_payment testpayment
    GROUP BY testpayment.lab_number
    ) AS payment
ON payment.lab_number = mainTest.lab_number 

WHERE mainTest.lab_number != ''
GROUP BY mainTest.lab_number    

运行此查询时，我收到此错误

MySQL said:
#1054 - Unknown column 'mainTest.lab_number' in 'field list' 

谢谢。

这是我创建的 fiddle 。 http://sqlfiddle.com/#!2/291c4/8

Answer 1

试试这个，如果我是对的，问题是您试图将左连接中的派生表视为相关子查询。感谢 sql fiddle...使帮助您变得更加容易。

SELECT mainTest.lab_number_auto, mainTest.lab_number,
SUM(CASE WHEN test.lab_number = mainTest.lab_number
    THEN test.test_unit_cost ELSE 0 END) cost,
(
    SELECT SUM(CASE WHEN testpayment.lab_number = mainTest.lab_number THEN testpayment.amount_paid ELSE 0 END) totalPaid
    FROM patient_test_payment testpayment
    Where lab_number = mainTest.lab_number
    GROUP BY testpayment.lab_number
    ) AS totalPaid

FROM patient_main_test mainTest

LEFT JOIN patient_test test    
ON test.lab_number = mainTest.lab_number 

WHERE mainTest.lab_number != ''
GROUP BY mainTest.lab_number