SQL中用于CASE命令的语法，具有多个WHEN值时如下：

huangapple
go评论138阅读模式
英文:

SQL Syntax for CASE command with multiple WHEN value

问题

TL/DR
在CASE级别的条件之后是否可以使用“IN”语法？

我的情况：

我正在编写一个具有多个WHEN值验证的SQL CASE语句。
CASE条件很复杂（而且很长），所以我不想在WHEN级别重复它。

这个方法有效：

  1. CASE
  2.    WHEN ( SELECT VALUE FROM Tab1 INNER JOIN Tab2 ON Tab1 ....非常长的语句) IN ('A','B','C') THEN 1
  3.    WHEN (与上面相同的非常长的语句) IN ('D','E','F') THEN 2
  4.    WHEN (与上面相同的非常长的语句) IN ... 等等
  5. END

我想将它变得更可读，但以下语法失败了：

  1. CASE ( SELECT VALUE FROM Tab1 INNER JOIN Tab2 ON Tab1 ....非常长的语句) 
  2.    WHEN IN ('A','B','C') THEN 1 -- 语法错误
  3.    WHEN 'D' OR 'E' OR 'F' THEN 2 -- 也会出现语法错误
  4. END

当然，我想避免在不同的WHEN中列出所有具有相同结果的值。
以下语法有效，但值列表很长：

  1. CASE ( SELECT VALUE FROM Tab1 INNER JOIN Tab2 ON Tab1 ....非常长的语句) 
  2.    WHEN 'A' THEN 1
  3.    WHEN 'B' THEN 1
  4.    WHEN 'C' THEN 1
  5.    WHEN 'D' THEN 2
  6.    WHEN 'E' THEN 2
  7.    WHEN 'F' THEN 2
  8. ....
  9. 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 :

  1. CASE
  2.    WHEN ( SELECT VALUE FROM Tab1 INNER JOIN Tab2 ON Tab1 ....very long statement) IN ('A','B','C') THEN 1
  3.    WHEN ( same very long statement as above) IN ('D','E','F') THEN 2
  4.    WHEN ( same very long statement as above) IN ... etc
  5. END

I would like to make it more readable as this, but syntax below fails

  1. CASE ( SELECT VALUE FROM Tab1 INNER JOIN Tab2 ON Tab1 ....very long statement) 
  2.    WHEN IN ('A','B','C') THEN 1 -- fails syntax error
  3.    WHEN 'D' OR 'E' OR 'F' THEN 2 -- also fails syntax error
  4. 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

  1. CASE ( SELECT VALUE FROM Tab1 INNER JOIN Tab2 ON Tab1 ....very long statement) 
  2.    WHEN 'A' THEN 1
  3.    WHEN 'B' THEN 1
  4.    WHEN 'C' THEN 1
  5.    WHEN 'D' THEN 2
  6.    WHEN 'E' THEN 2
  7.    WHEN 'F' THEN 2
  8. ....
  9. END

What can SQL do for me there ?

答案1

得分: 0

请使用以下查询方式，并将主查询与它进行CROSS JOIN：

  1. SELECT
  2.   base_query.other,
  3.   base_query.columns,
  4.   base_query.otherquery,
  5.   CASE
  6.     WHEN xcross.result IN ('A','B','C') THEN 1
  7.     WHEN xcross.result IN ('D','E','F') THEN 2
  8.     WHEN xcross.result IN ('G','H','I') THEN 3
  9.     ELSE NULL
  10.   END
  11. FROM other_table ot
  12. JOIN yet_other_table you ON ot.join_col = you.join_col
  13. CROSS JOIN (
  14.   SELECT val AS result FROM Tab1 INNER JOIN Tab2 ON Tab1 ....非常长的语句
  15. ) AS xcross
英文:

Formulate the long query as you did, and CROSS JOIN the main query with it:

  1. SELECT
  2.   base_query.other
  3. , base_query.columns
  4. , base_query.otherquery
  5. , CASE
  6.     WHEN xcross.result IN ('A','B','C') THEN 1
  7.     WHEN xcross.result IN ('D','E','F') THEN 2
  8.     WHEN xcross.result IN ('G','H','I') THEN 3
  9.     ELSE NULL
  10.   END
  11. FROM other_table ot
  12. JOIN yet_other_table you on ot.join_col = yot.join_col
  13. CROSS JOIN (
  14.   SELECT val AS result FROM Tab1 INNER JOIN Tab2 ON Tab1 ....very long statement
  15. ) AS xcross

huangapple
  • 本文由 发表于 2023年2月8日 18:09:03
  • 转载请务必保留本文链接：https://go.coder-hub.com/75384197.html
  • case
  • sql
  • sql-server
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定