有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注意: desciption通常与table1列相关;这些是ui上的复选框,因此desciption= ui中的复选框名称。
table c(eid, groupid)我的问题是,我需要将数据从table1迁移到表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;错误出在哪里?
发布于 2017-02-24 13:02:53
您可以通过执行一系列联合来实现这一点:
(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表与您想要的即时输出有什么关系。
https://stackoverflow.com/questions/42430916
复制相似问题