MySQL:FROM 子句中的 CASE 语句

Question

我想知道是否可以在 FROM 子句中放置一个 CASE 语句...在 MySQL 中？像这样的东西:

SELECT id1 FROM ref_table AS rt WHERE
EXISTS 
   (SELECT rt.id FROM 
      (CASE rt.table
         WHEN 'tbl1' THEN tbl_1
         WHEN 'tbl2' THEN tbl_2
         WHEN 'tbl3' THEN tbl_3
      END) AS tbl
   WHERE tbl.id2 = rt.id_table)

Answer 1

CASE 表达式的结果不能是表格，所以不，您不能使用这种语法。您可以使用 OR(或 CASE)和 3 个 EXISTS 子查询重写查询:

SELECT id1 FROM ref_table AS rt WHERE
CASE rt.table
    WHEN 'tbl1' THEN EXISTS (SELECT 1 FROM tbl_1 t WHERE t.id2 = rt.id_table)
    WHEN 'tbl2' THEN EXISTS (SELECT 1 FROM tbl_2 t WHERE t.id2 = rt.id_table)
    WHEN 'tbl3' THEN EXISTS (SELECT 1 FROM tbl_3 t WHERE t.id2 = rt.id_table)
END ;

更具可读性，使用OR:

SELECT id1 FROM ref_table AS rt WHERE
   rt.table = 'tbl1' AND EXISTS (SELECT 1 FROM tbl_1 t WHERE t.id2 = rt.id_table)
OR rt.table = 'tbl2' AND EXISTS (SELECT 1 FROM tbl_2 t WHERE t.id2 = rt.id_table)
OR rt.table = 'tbl3' AND EXISTS (SELECT 1 FROM tbl_3 t WHERE t.id2 = rt.id_table)
;