英文:
SQL Syntax for CASE command with multiple WHEN value
问题
TL/DR
在CASE级别的条件之后是否可以使用“IN”语法？
我的情况：
我正在编写一个具有多个WHEN值验证的SQL CASE语句。
CASE条件很复杂（而且很长），所以我不想在WHEN级别重复它。
这个方法有效：
CASE
WHEN ( SELECT VALUE FROM Tab1 INNER JOIN Tab2 ON Tab1 ....非常长的语句) IN ('A','B','C') THEN 1
WHEN (与上面相同的非常长的语句) IN ('D','E','F') THEN 2
WHEN (与上面相同的非常长的语句) IN ... 等等
END
我想将它变得更可读，但以下语法失败了：
CASE ( SELECT VALUE FROM Tab1 INNER JOIN Tab2 ON Tab1 ....非常长的语句)
WHEN IN ('A','B','C') THEN 1 -- 语法错误
WHEN 'D' OR 'E' OR 'F' THEN 2 -- 也会出现语法错误
END
当然，我想避免在不同的WHEN中列出所有具有相同结果的值。
以下语法有效，但值列表很长：
CASE ( SELECT VALUE FROM Tab1 INNER JOIN Tab2 ON Tab1 ....非常长的语句)
WHEN 'A' THEN 1
WHEN 'B' THEN 1
WHEN 'C' THEN 1
WHEN 'D' THEN 2
WHEN 'E' THEN 2
WHEN 'F' THEN 2
....
END
SQL能为我做什么？
英文:
TL/DR
Is it possible to use "IN" syntax after "WHEN" if the condition is at CASE level ?
My scenario :
I am writing a SQL CASE statement with multiple WHEN value validation.
The CASE condition is complex (and long) so i don't want to repeat it at WHEN level.
This works :
CASE
WHEN ( SELECT VALUE FROM Tab1 INNER JOIN Tab2 ON Tab1 ....very long statement) IN ('A','B','C') THEN 1
WHEN ( same very long statement as above) IN ('D','E','F') THEN 2
WHEN ( same very long statement as above) IN ... etc
END
I would like to make it more readable as this, but syntax below fails
CASE ( SELECT VALUE FROM Tab1 INNER JOIN Tab2 ON Tab1 ....very long statement)
WHEN IN ('A','B','C') THEN 1 -- fails syntax error
WHEN 'D' OR 'E' OR 'F' THEN 2 -- also fails syntax error
END
Of course i am trying to avoid listing all values with same outcome in different when
Syntax below works but very long list of values
CASE ( SELECT VALUE FROM Tab1 INNER JOIN Tab2 ON Tab1 ....very long statement)
WHEN 'A' THEN 1
WHEN 'B' THEN 1
WHEN 'C' THEN 1
WHEN 'D' THEN 2
WHEN 'E' THEN 2
WHEN 'F' THEN 2
....
END
What can SQL do for me there ?
答案1
得分: 0
请使用以下查询方式，并将主查询与它进行CROSS JOIN：
SELECT
base_query.other,
base_query.columns,
base_query.otherquery,
CASE
WHEN xcross.result IN ('A','B','C') THEN 1
WHEN xcross.result IN ('D','E','F') THEN 2
WHEN xcross.result IN ('G','H','I') THEN 3
ELSE NULL
END
FROM other_table ot
JOIN yet_other_table you ON ot.join_col = you.join_col
CROSS JOIN (
SELECT val AS result FROM Tab1 INNER JOIN Tab2 ON Tab1 ....非常长的语句
) AS xcross
英文:
Formulate the long query as you did, and CROSS JOIN the main query with it:
SELECT
base_query.other
, base_query.columns
, base_query.otherquery
, CASE
WHEN xcross.result IN ('A','B','C') THEN 1
WHEN xcross.result IN ('D','E','F') THEN 2
WHEN xcross.result IN ('G','H','I') THEN 3
ELSE NULL
END
FROM other_table ot
JOIN yet_other_table you on ot.join_col = yot.join_col
CROSS JOIN (
SELECT val AS result FROM Tab1 INNER JOIN Tab2 ON Tab1 ....very long statement
) AS xcross
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论