SQL CASEWHEN优化

Question

根据当前表中某一字段值匹配子表中的其他字段

查询居然得一两秒?

能优化下？ 这J简直不能忍a

SQL如下：

SELECT
CASE
push.ALARM_TYPE
WHEN '1' THEN
( SELECT LL FROM ab.tag_number tag WHERE tag.TAG_NUMBER = push.TAG_NUMBER )
WHEN '2' THEN
( SELECT LL FROM ab.tag_number tag WHERE tag.TAG_NUMBER = push.TAG_NUMBER )
WHEN '3' THEN
( SELECT LL FROM ab.zd_toxic_combustible tag WHERE tag.TAG_NUMBER = push.TAG_NUMBER )
WHEN '4' THEN
( SELECT LL FROM ab.tag_number tag WHERE tag.TAG_NUMBER = push.TAG_NUMBER )
END AS "ll",
CASE
push.ALARM_TYPE
WHEN '1' THEN
( SELECT L FROM ab.tag_number tag WHERE tag.TAG_NUMBER = push.TAG_NUMBER )
WHEN '2' THEN
( SELECT L FROM ab.tag_number tag WHERE tag.TAG_NUMBER = push.TAG_NUMBER )
WHEN '3' THEN
( SELECT L FROM ab.zd_toxic_combustible tag WHERE tag.TAG_NUMBER = push.TAG_NUMBER )
WHEN '4' THEN
( SELECT L FROM ab.tag_number tag WHERE tag.TAG_NUMBER = push.TAG_NUMBER )
END AS "l",
CASE
push.ALARM_TYPE
WHEN '1' THEN
( SELECT H FROM ab.tag_number tag WHERE tag.TAG_NUMBER = push.TAG_NUMBER )
WHEN '2' THEN
( SELECT H FROM ab.tag_number tag WHERE tag.TAG_NUMBER = push.TAG_NUMBER )
WHEN '3' THEN
( SELECT H FROM ab.zd_toxic_combustible tag WHERE tag.TAG_NUMBER = push.TAG_NUMBER )
WHEN '4' THEN
( SELECT H FROM ab.tag_number tag WHERE tag.TAG_NUMBER = push.TAG_NUMBER )
END AS "h",
CASE
push.ALARM_TYPE
WHEN '1' THEN
( SELECT HH FROM ab.tag_number tag WHERE tag.TAG_NUMBER = push.TAG_NUMBER )
WHEN '2' THEN
( SELECT HH FROM ab.tag_number tag WHERE tag.TAG_NUMBER = push.TAG_NUMBER )
WHEN '3' THEN
( SELECT HH FROM ab.zd_toxic_combustible tag WHERE tag.TAG_NUMBER = push.TAG_NUMBER )
WHEN '4' THEN
( SELECT HH FROM ab.tag_number tag WHERE tag.TAG_NUMBER = push.TAG_NUMBER )
END AS "hh"
FROM
ab.zd_alarm_info_push push
WHERE
ALARM_STATE = '4'
ORDER BY
COLUMN2,
COLUMN1,
ALARM_TIME_START DESC,
ALARM_TYPE,
CHECK_TIME

Answer 1

你这查询算法有很大的问题，不慢才怪，下面代码供你参考，其它字段自己照着补充

SELECT
CASE
push.ALARM_TYPE
WHEN '1' THEN tag1.ll
WHEN '2' THEN tag1.ll
WHEN '3' THEN tag2.ll
WHEN '4' THEN tag3.ll
END AS "ll",
FROM ab.zd_alarm_info_push push
left join ab.tag_number tag1 on tag1.TAG_NUMBER = push.TAG_NUMBER
left join ab.zd_toxic_combustible tag2 on tag2.TAG_NUMBER = push.TAG_NUMBER
left join ab.tag_number tag3 on tag3.TAG_NUMBER = push.TAG_NUMBER
WHERE ALARM_STATE = '4'
ORDER BY ...