时间戳

首页 > 解决方案 > 在 MySql 中使用 IF 或 CASE 与 INNER JOIN

mysql - 在 MySql 中使用 IF 或 CASE 与 INNER JOIN

问题描述

我有 4 个不同的用户（投注者）表（糟糕的想法，但很久以前需要进行大规模的重新设计），我需要列出来自特定事件的用户。

我看过这篇文章，但不喜欢 MySQL 查询，其中 JOIN 取决于 CASE

我也看过类似的问题，但没有找到任何我可以使用的东西。

这是我的查询：

SELECT 
    sale_id, sale_punter_type, sale_refund, sale_timestamp, 
    sale_punter_type,


    AES_DECRYPT(punter_email, :keyPunter) AS punter_email,
    punter_firstname,
    punter_surname,
    AES_DECRYPT(punter_postcode, :keyPunter) AS punter_postcode,


    AES_DECRYPT(punter_checkout_email, :keyPunter) AS punter_checkout_email,
    punter_checkout_firstname,
    punter_checkout_surname,
    AES_DECRYPT(punter_checkout_postcode, :keyPunter) AS punter_checkout_postcode,


    AES_DECRYPT(punter_compo_email, :keyPunter) AS punter_compo_email,
    punter_compo_firstname,
    punter_compo_surname,


    AES_DECRYPT(punter_shop_email, :keyPunter) AS punter_shop_email,
    punter_shop_firstname,
    punter_shop_surname

FROM 
    sale 
INNER JOIN
    ticket_sold ON sale_id = ticket_sold_sale_no
INNER JOIN
    event ON event_id = ticket_sold_event_no
INNER JOIN 


    (CASE
        WHEN 
            sale_punter_type = 1 THEN punter ON punter_id = sale_punter_no 
        WHEN 
            sale_punter_type = 2 THEN punter_checkout ON punter_checkout_id = sale_punter_no 
        WHEN 
            sale_punter_type = 3 THEN punter_compo ON punter_compo_id = sale_punter_no 
        WHEN 
            sale_punter_type = 4 THEN punter_shop ON punter_shop_id = sale_punter_no 
    END) 


WHERE 
    event_id = :id

我尝试使用左连接：

SELECT 
    sale_id, sale_punter_type, sale_refund, sale_timestamp, 
    sale_punter_type,


    AES_DECRYPT(punter_email, :keyPunter) AS punter_email,
    punter_firstname,
    punter_surname,
    AES_DECRYPT(punter_postcode, :keyPunter) AS punter_postcode,


    AES_DECRYPT(punter_checkout_email, :keyPunter) AS punter_checkout_email,
    punter_checkout_firstname,
    punter_checkout_surname,
    AES_DECRYPT(punter_checkout_postcode, :keyPunter) AS punter_checkout_postcode,


    AES_DECRYPT(punter_compo_email, :keyPunter) AS punter_compo_email,
    punter_compo_firstname,
    punter_compo_surname,


    AES_DECRYPT(punter_shop_email, :keyPunter) AS punter_shop_email,
    punter_shop_firstname,
    punter_shop_surname

FROM 
    sale 
INNER JOIN
    ticket_sold ON sale_id = ticket_sold_sale_no
INNER JOIN
    event ON event_id = ticket_sold_event_no
INNER JOIN 
    punter ON punter_id = sale_punter_no 
LEFT JOIN 
    punter_checkout ON punter_checkout_id = sale_punter_no 
LEFT JOIN 
    punter_compo ON punter_compo_id = sale_punter_no 
LEFT JOIN 
    punter_shop ON punter_shop_id = sale_punter_no 
WHERE 
    event_id = :id

但它返回了具有相同 ID 但来自不同表的用户。

我不知道如何解释它，我需要类似的东西

IF sale_punter_no = 1 THEN INNER JOIN punter ON punter_id = sale_punter_no

希望这是有道理的，mysql可以做这样的事情

谢谢

标签： mysqlif-statementinner-join

解决方案

您可以LEFT JOIN在每个表上执行，如下所示。

但是，从您的示例中，您不清楚要从每个相关表中使用哪些列；您需要使用COALESCE()或类似的方法来相应地合并它们。在每一列前面加上它所属的表（或表别名）总是一个很好的做法。

这是查询：

SELECT 
    sale_id, sale_punter_type, sale_refund, sale_timestamp, 
    sale_punter_type,

    AES_DECRYPT(punter_email, :keyPunter) AS punter_email,
    punter_firstname,
    punter_surname,
    AES_DECRYPT(punter_postcode, :keyPunter) AS punter_postcode,

    AES_DECRYPT(punter_checkout_email, :keyPunter) AS punter_checkout_email,
    punter_checkout_firstname,
    punter_checkout_surname,
    AES_DECRYPT(punter_checkout_postcode, :keyPunter) AS punter_checkout_postcode,

    AES_DECRYPT(punter_compo_email, :keyPunter) AS punter_compo_email,
    punter_compo_firstname,
    punter_compo_surname,

    AES_DECRYPT(punter_shop_email, :keyPunter) AS punter_shop_email,
    punter_shop_firstname,
    punter_shop_surname

FROM sale 
JOIN ticket_sold ON sale_id = ticket_sold_sale_no
JOIN event ON event_id = ticket_sold_event_no

LEFT JOIN punter ON punter_id = sale_punter_no AND sale_punter_type = 1
LEFT JOIN punter_checkout ON punter_checkout_id = sale_punter_no AND sale_punter_type = 2
LEFT JOIN punter_compo ON punter_compo_id = sale_punter_no AND sale_punter_type = 3
LEFT JOIN punter_shop ON punter_shop_id = sale_punter_no AND sale_punter_type = 4
WHERE event_id = :id

推荐阅读