mysql，使用案例和变量压缩查询

Question

我有一个这样的查询:根据 CASE 选择对不同的表进行子查询。

SELECT
    ID,
    UNID,
    IDMENU,
    IDTYPE, ( CASE WHEN `IDMENU` = 1 THEN
            (
                SELECT
                    tableB.DESCRIPTION
                FROM
                    tableB
                WHERE
                    tableB.ID = tableA.ID
                    AND tableB.IDMENU = tableA.IDMENU
                    AND tableB.IDTYPE = tableA.IDTYPE)
                WHEN `IDMENU` = 2 THEN
                (
                    SELECT
                        tableC.DESCRIPTION
                    FROM
                        tableC
                    WHERE
                        tableC.ID = tableA.ID
                        AND tableC.IDMENU = tableA.IDMENU
                        AND tableC.IDTYPE = tableA.IDTYPE)
END) AS DESCRIPTION
FROM
    tableA

这可以更紧凑吗？我可以将 CASE 值保存到变量中，然后将该变量用作表名吗？我应该使用程序吗？

Answer 1

您可以使用 Left Join 代替子查询。 Left Join 将确保您的主(最左边)表中的所有行都出现。

SELECT
    tA.ID,
    tA.UNID,
    tA.IDMENU,
    tA.IDTYPE, 
    (CASE tA.IDMENU
       WHEN 1 THEN tB.DESCRIPTION
       WHEN 2 THEN tC.DESCRIPTION) AS DESCRIPTION 
FROM
    tableA AS tA 
LEFT JOIN tableB AS tB ON tB.ID = tA.ID AND 
                          tB.IDMENU = tA.IDMENU AND 
                          tB.IDTYPE = tA.IDTYPE 
LEFT JOIN tableC AS tC ON tC.ID = tA.ID AND 
                          tC.IDMENU = tA.IDMENU AND 
                          tC.IDTYPE = tA.IDTYPE