mysql，使用case和变量压缩查询

xpcnnkqh 于 2021-06-18 发布在 Mysql

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

我有这样一个查询：子查询是根据案例选择对不同的表进行的。

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

这个能再紧凑点吗？是否可以将大小写保存到变量中，然后将该变量用作表名？我应该使用程序吗？

mysql

来源：https://stackoverflow.com/questions/52964234/mysql-compact-a-query-using-case-and-variables

2条答案

按热度按时间

gijlo24d1#

您可以使用 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

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

qnyhuwrf2#

您可以在下面的查询中使用case

SELECT
   ID,
   UNID,
   IDMENU,
   IDTYPE,
   case 
     when IDMENU=1 then tableB.DESCRIPTION 
     when IDMENU=2 then tableC.DESCRIPTION 
   end as description
   FROM TableA 
   left join tableB on 
        tableB.ID = tableA.ID AND 
        tableB.IDMENU = tableA.IDMENU AND 
        tableB.IDTYPE = tableA.IDTYPE
   left join tableC on 
        tableC.ID = tableA.ID AND 
        tableC.IDMENU = tableA.IDMENU AND 
        tableC.IDTYPE = tableA.IDTYPE

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

我来回答

mysql，使用case和变量压缩查询

2条答案

相关问题

热门标签

最新问答