mysql - 在 mySQL 查询中使用条件

Question

我需要在我的 mySQL 查询中有一个基于查询结果的条件。

SELECT 返回给我 IVC_Recipient，我想使用此数据来制定条件。但它不起作用。

这是查询:

SELECT 
    KDX_Id, 
    KDX_Name, 
    KDX_FirstName, 
    KDX_Company, 
    BOO_Id, 
    DEB_Date, 
    IFNULL(SUM(IVC_Sum + IVC_Taxes), 0) AS DEB_Total,
    DATEDIFF('2018-07-10', DEB_Date) AS DEB_Nb_Days,
    IVC_Recipient 
FROM ___Debtors 
JOIN ___Invoices 
    ON ___Invoices.IVC_Id = ___Debtors.DEB_InvoiceId 
JOIN ___Bookings 
    ON ___Bookings.BOO_Id = ___Invoices.IVC_BookingId


IF(IVC_Recipient = 'guest') {
    JOIN ___Kardex 
        ON ___Kardex.KDX_Id = ___Bookings.BOO_GuestId
}

IF(IVC_Recipient = 'company') {
    JOIN ___Kardex 
        ON ___Kardex.KDX_Id = ___Bookings.BOO_CompanyId
}    

请问我在这里遗漏了什么？

谢谢。

Answer 1

您可以使用CASE 表达式:

SELECT 
    KDX_Id, 
    KDX_Name, 
    KDX_FirstName, 
    KDX_Company, 
    BOO_Id, 
    DEB_Date, 
    IFNULL(SUM(IVC_Sum + IVC_Taxes), 0) AS DEB_Total,  -- there is no GROUP BY
    DATEDIFF('2018-07-10', DEB_Date) AS DEB_Nb_Days,
    IVC_Recipient 
FROM ___Debtors 
JOIN ___Invoices 
    ON ___Invoices.IVC_Id = ___Debtors.DEB_InvoiceId 
JOIN ___Bookings 
    ON ___Bookings.BOO_Id = ___Invoices.IVC_BookingId
JOIN ___Kardex 
        ON ___Kardex.KDX_Id = (CASE 
             WHEN IVC_Recipient = 'guest' THEN  ___Bookings.BOO_GuestId
             WHEN IVC_Recipient = 'company' THEN ___Bookings.BOO_CompanyId
             END)