mysql - 使用半连接从多个表中选择

Question

我有这个 MySQL 查询。它会产生一个错误，“字段列表”中的未知列“lp.amount”

SELECT l.*, SUM(lp.amount) AS landPayMonth, p.pName, u.uName, SUM(ci.amount) AS totAmnt, t.unitId FROM (SELECT distinct landlord_payment.id FROM landlord_payment) lp, (SELECT distinct cashIn.id FROM cashIn) ci, landlords l, properties p, units u, tenants t WHERE ci.tenantId=t.id AND l.id=lp.landlordId AND t.unitId=u.id AND u.propertyId=p.id AND p.landlordId=l.id AND STR_TO_DATE(ci.registeredTime, "%Y-%m")=STR_TO_DATE(CURDATE(), "%Y-%m")
GROUP BY l.id;

请帮帮我。

Answer 1

SELECT 
    l.*,SUM(lp.amount) AS landPayMonth,p.pName,u.uName,SUM(ci.amount) AS totAmnt,
    t.unitId
FROM
    (SELECT DISTINCT landlord_payment.id,landlord_payment.amount,landlord_payment.landlordId FROM landlord_payment) lp,
    (SELECT DISTINCT cashIn.id,cashIn.tenantId,cashIn.registeredTime,cashIn.amount FROM cashIn) ci,landlords l,properties p,units u,tenants t
WHERE
    ci.tenantId = t.id
        AND l.id = lp.landlordId
        AND t.unitId = u.id
        AND u.propertyId = p.id
        AND p.landlordId = l.id
        AND STR_TO_DATE(ci.registeredTime, '%Y-%m') = STR_TO_DATE(CURDATE(), '%Y-%m')
GROUP BY l.id;

您可以尝试上面的代码。

希望这对您有帮助。