如何调试MySQL复杂的嵌套SQL语句

Question

有3个表如下，

table a ( eid, COLUMNA, COLUMNB, COLUMNC, COLUMND)
1001,1,1,0,1
1002,1,0,0,0


table b (id, description)
1, ABC
2, BCD
3,CDE
4,DEF

注意：描述通常与table1列有关；这些是UI上的复选框，因此desciption = ui中的复选框名称。

table c(eid, groupid)

我的问题是，我需要将数据从表1迁移到表3如下

1001,1
1001,2
1001,4
1002,1

以下是我的查询，但没有按照我的预期获得结果。

SELECT DISTINCT eid, id
    FROM (SELECT eid,
                 CASE
                    WHEN cola = 1
                    THEN
                       (SELECT id
                          FROM tableb
                         WHERE description = 'ABC')
                    WHEN cola = 0
                    THEN
                       -1
                 END
                    AS coln_a,
                 CASE
                    WHEN colb = 1
                    THEN
                       (SELECT id
                          FROM tableb
                         WHERE description = 'BCD')
                    WHEN colb = 0
                    THEN
                       -1
                 END
                    AS coln_b,
                 CASE
                    WHEN colc = 1
                    THEN
                       (SELECT id
                          FROM tableb
                         WHERE description = 'CDE')
                    WHEN colc = 0
                    THEN
                       -1
                 END
                    AS coln_c,
                 CASE
                    WHEN cold = 1
                    THEN
                       (SELECT id
                          FROM tableb
                         WHERE description = 'DEF')
                    WHEN cold = 0
                    THEN
                       -1
                 END
                    AS coln_d
            FROM tablea
           WHERE cola = 1 OR colb = 1 OR colc = 1 OR cold= 1) temp_t,
         tableb
   WHERE coln_a = id OR coln_b = id OR coln_c = id OR coln_d = id
ORDER BY eid, id;

错误是什么？

Answer 1

您通过做一系列工会来完成此操作：

(SELECT eid, 1 AS groupid FROM tablea WHERE COLUMNA = 1)
UNION ALL
(SELECT eid, 2 FROM tablea WHERE COLUMNB = 1)
UNION ALL
(SELECT eid, 3 FROM tablea WHERE COLUMNC = 1)
UNION ALL
(SELECT eid, 4 FROM tablea WHERE COLUMND = 1)

我看不出B表与您想要的即时输出有何关系。