在mysql查询中使用条件

relj7zay 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(284)

我需要在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
}

请问我这里缺什么？
谢谢。

mysql conditional-statements

来源：https://stackoverflow.com/questions/51271887/use-condition-into-a-mysql-query

2条答案

按热度按时间

ajsxfq5m1#

你可以用 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)

赞(0）回复(0）举报 2021-06-21

hs1rzwqc2#

几乎所有的“如果”都可以表示为and，在这些情况下：

JOIN ___Kardex 
    ON (IVC_Recipient = 'guest' AND ___Kardex.KDX_Id = ___Bookings.BOO_GuestId)
    OR (IVC_Recipient = 'company' AND ___Kardex.KDX_Id = ___Bookings.BOO_CompanyId)

…但根据索引使用的工作方式，有时甚至可以更简单地联接并在where条件下对结果进行排序（尽管这将是我的最后手段“我们可以通过这种方式获得更快的速度”尝试）。

JOIN ___Kardex 
    ON  ___Kardex.KDX_Id IN (___Bookings.BOO_GuestId, ___Bookings.BOO_CompanyId)
WHERE (IVC_Recipient = 'guest' AND ___Kardex.KDX_Id = ___Bookings.BOO_GuestId)
    OR (IVC_Recipient = 'company' AND ___Kardex.KDX_Id = ___Bookings.BOO_CompanyId)

赞(0）回复(0）举报 2021-06-21

我来回答

在mysql查询中使用条件

2条答案

相关问题

热门标签

最新问答