sql - 我正在尝试找到一种将 IN() 与 LIKE() 一起用于多种条件的方法
问题描述
我想避免做嵌套CASE THEN ELSE
语句。我想用IN()
,但不知道能不能和它连用LIKE()
。这将消除对深层嵌套 case 语句的需要,并且易于添加/删除/修改标准。
这就是我要的:
CASE
WHEN(dbo.OrderDetail.OrderNumber LIKE 'Kel%')
THEN 2
ELSE CASE
WHEN(dbo.OrderDetail.OrderNumber LIKE 'RMA%')
THEN 3
ELSE CASE
WHEN(dbo.OrderDetail.OrderNumber LIKE IN('DC%', 'BT%', 'HF%'))
THEN 5
ELSE NULL
END
END
END AS OrderType
这就是我所拥有的:
CASE
WHEN(dbo.OrderDetail.OrderNumber LIKE 'Kel%')
THEN 2
ELSE CASE
WHEN(dbo.OrderDetail.OrderNumber LIKE 'RMA%')
THEN 3
ELSE CASE
WHEN(dbo.OrderDetail.OrderNumber LIKE 'DC%')
THEN 5
ELSE CASE WHEN(dbo.OrderDetail.OrderNumber LIKE'BT%')
THEN 5
ELSE CASE WHEN(dbo.OrderDetail.OrderNumber LIKE 'HF%')
THEN 5
ELSE
NULL
END END END
END
END AS OrderType
解决方案
假设你给表起别名,你可以写得非常紧凑:
FROM dbo.OrderDetail as od
...
WHERE
CASE
WHEN od.OrderNumber LIKE 'Kel%' THEN 2
WHEN od.OrderNumber LIKE 'RMA%' THEN 3
WHEN od.OrderNumber LIKE 'DC%' THEN 5
WHEN od.OrderNumber LIKE 'BT%' THEN 5
WHEN od.OrderNumber LIKE 'HF%' THEN 5
END AS OrderType
尽管我认为几乎没有理由这样做,但可以组合产生相同值的案例:
CASE
WHEN od.OrderNumber LIKE 'Kel%' THEN 2
WHEN od.OrderNumber LIKE 'RMA%' THEN 3
WHEN od.OrderNumber LIKE 'DC%'
OR od.OrderNumber LIKE 'BT%'
OR od.OrderNumber LIKE 'HF%' THEN 5
END AS OrderType
由于您正在处理最左边的两个字符,因此您还有另一种选择:
CASE
WHEN od.OrderNumber LIKE 'Kel%' THEN 2
WHEN od.OrderNumber LIKE 'RMA%' THEN 3
WHEN LEFT(od.OrderNumber, 2) IN ('DC', 'BT', 'HF') THEN 5
END AS OrderType
推荐阅读
- visual-studio - 表达式必须通过 MPI 世界大小在数组中具有常量值错误
- python - 基于另一个数据框在数据框中创建新列
- java - 如何在wiremock中使用“优先级”
- linux - 从文件名中递归删除模式而不更改路径
- c# - Unity中基于网格/平铺移动+碰撞?
- javascript - Progress bar - jQuery to Pure Vanilla JS
- r - Sub-setting or arrange the data in R
- java - How can I add a TreeNode to a JTree constructed with the default constructor?
- java - How do I fix my java fx issues?
- java - How to define nested class constants?