sql server中基于BETWEEN运算符编写case语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29204602/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Writing case statement based on BETWEEN operator in sql server
提问by SouravA
I have a CASEstatement, which looks like below:
我有一个CASE声明，如下所示：
SELECT CASE
WHEN <expression>> BETWEEN 0 AND 10 THEN '0-10'
WHEN <<expression>> BETWEEN 11 AND 20 THEN '11-20'
WHEN <<expression>> BETWEEN 21 AND 30 THEN '21-30'
...
This works great but it looks ugly.
这很好用，但看起来很丑。
I want to convert it to something which looks like below:
我想将其转换为如下所示的内容：
SELECT CASE <expression>>
WHEN BETWEEN 0 AND 10 THEN '0-10'
WHEN BETWEEN 11 AND 20 THEN '11-20'
WHEN BETWEEN 21 AND 30 THEN '21-30'
...
Obviously the second query looks better as I dont have to write that complex expression multiple times. But it is error-ing out.
显然，第二个查询看起来更好，因为我不必多次编写复杂的表达式。但它正在出错。
Incorrect syntax near the keyword 'between'.
Can someone please let me know what I am doing wrong here?
有人可以让我知道我在这里做错了什么吗？
UPDATE:
更新：
As pointed out, I have changed the query in my question to make the ranges exclusive.
正如所指出的，我已经更改了我的问题中的查询以使范围独占。
回答by jpw
What you want to is not possible as the syntax for the betweenstatement requires an expression to test as it's first parameter:
你想要的是不可能的，因为between语句的语法需要一个表达式来测试，因为它是第一个参数：
test_expression [ NOT ] BETWEEN begin_expression AND end_expression
So you're stuck with the first syntax.
所以你被第一种语法困住了。
On a side note, your ranges is a bit off as between uses inclusive ranges, but the case expressions are evaluated in order so the ranges are really:
附带说明一下，您的范围在使用包含范围之间有点偏离，但 case 表达式是按顺序计算的，因此范围实际上是：
WHEN <<expression>> BETWEEN 0 AND 10 THEN '0-10'
WHEN <<expression>> BETWEEN 11 AND 20 THEN '11-20'
WHEN <<expression>> BETWEEN 21 AND 30 THEN '21-30'
回答by Bohemian
Use a subquery, but also optimize the cases a bit more cleanly:
使用子查询，但也更干净地优化案例：
SELECT ...,
CASE
WHEN calc >= 100 THEN 'Over 100'
ELSE CONCAT(calc * 10, ' - ', calc * 10 + 9)
END AS range
FROM (SELECT *, FLOOR(<<expression>> / 10) as calc
FROM tableName) x
The FLOOR()function rounds down to the nearest integer below the supplied number. By first dividing your expressiin by 10, then flooring, then multiplying by 10, you get the start of the range of the expression. Eg, if the expression result is 63.4, 63.4 --> 6.34 --> 6 --> 60
该FLOOR()函数向下舍入到所提供数字以下最接近的整数。首先将您的表达式除以 10，然后取整，然后乘以 10，您将得到表达式范围的开始。例如，如果表达式结果是 63.4，63.4 --> 6.34 --> 6 --> 60
If there's no requirement for a top-range "Over somevalue", you could omit the CASE entirely, with just CONCAT(calc * 10, ' - ', calc * 10 + 9)which will handle values in any range.
如果不需要最高范围的“超过某个值”，则可以完全省略 CASE，仅使用CONCAT(calc * 10, ' - ', calc * 10 + 9)which 将处理任何范围内的值。
Note there is an error in your intended logic: The ranges should be 0-9, 10-19,... etc. This query addresses that too.
请注意，您的预期逻辑中存在错误：范围应为 0-9、10-19 等。此查询也解决了该问题。
回答by Giorgi Nakeuri
With CTE:
使用 CTE：
;WITH cte as
(
SELECT <expression>> AS Expression FROM TableName
)
SELECT CASE
WHEN Expression BETWEEN 0 AND 10 THEN '0-10'
WHEN Expression BETWEEN 10 AND 20 THEN '10-20'
WHEN Expression BETWEEN 20 AND 30 THEN '20-30'
END AS C
FROM cte
If Expression is always > 0 and intervals are sequential then you can even more shorten it:
如果 Expression 始终 > 0 并且间隔是连续的，那么您可以进一步缩短它：
SELECT CASE
WHEN Expression <= 10 THEN '0-10'
WHEN Expression <= 20 THEN '10-20'
WHEN Expression <= 30 THEN '20-30'
END AS C
FROM cte
回答by Dmitrij Kultasev
Use CROSS/OUTER APPLY for that
为此使用 CROSS/OUTER APPLY
DECLARE @a AS TABLE (
a int ,
b INT
);
INSERT INTO @a VALUES (1,2),(4,5),(4,1),(21,32),(32,21),(43,2);
SELECT CASE
WHEN expr BETWEEN 0 AND 10 THEN '0-10'
WHEN expr BETWEEN 11 AND 20 THEN '11-20'
WHEN expr BETWEEN 21 AND 30 THEN '21-30'
END
FROM @a
CROSS APPLY (SELECT CASE WHEN a > b THEN a ELSE b END expr) b
回答by Ilya Dorfman
In your particular case one can do the trick below. In general - yes - I am missing this option too.
with tst as (select end_n::bigint , start_n::bigint from (values(100,95),
(100,90),(100,80),(100,70),(100,60),(100,50),(100,40),(100,30))a(end_n,start_n))
select tst.*,(end_n - start_n)/10,
case (end_n - start_n)/10
when 0 then 'bucket_0_10'
when 1 then 'bucket_10_20'
when 2 then 'bucket_10_30'
else 'bucket_other'
end
from tst
在您的特定情况下，可以使用以下技巧。一般来说 - 是的 - 我也错过了这个选项。
与 tst as (select end_n::bigint , start_n::bigint from (values(100,95),
(100,90),(100,80),(100,70),(100,60),(100, 50),(100,40),(100,30))a(end_n,start_n))
select tst.*,(end_n - start_n)/10,
case (end_n - start_n)/10
when 0 then 'bucket_0_10'
when 1 然后 'bucket_10_20'
当 2 然后 'bucket_10_30'
否则 'bucket_other'
从 tst
结束
But it is static . Simple query with above trick and no "case" would make it dynamic
但它是静态的。使用上述技巧的简单查询并且没有“案例”将使其动态化
回答by Ajay2707
Case statement have 2 variation , both have different thoughs,
Case 语句有 2 个变体，两者都有不同的想法，
1. This is used when condition is dyanmically change and output also want to change
SELECT CASE
WHEN <<expression>> or condition1 THEN output1
WHEN <<expression>> or condition2 THEN output2
WHEN <<expression>> or condition3 THEN output3
WHEN <<expression>> or condition4 THEN output4
END
2. This is used when condition is same , only output is different same like swith case in c#.
SELECT CASE <<expression>>
when result1 then output1
when result2 then output2
when result3 then output3
when result4 then output4
END
For you question. Give the condition in each when statement.
对于你的问题。给出每个中的条件when statement。
SELECT CASE
WHEN <expression>> BETWEEN 0 AND 10 THEN '0-10'
WHEN <expression>> BETWEEN 10 AND 20 THEN '10-20'
WHEN <expression>> BETWEEN 20 AND 30 THEN '20-30'
SELECT CASE
WHEN 1 = 1 THEN 'YES' --NEED THE EXPRESSION OF EACH WHEN, IF RESULT IS TRUE, THEN "THEN STATMENT IS OUTPU ELSE NEXT WHEN STATEMENT
WHEN 2 = 2 THEN 'YES'
WHEN 1 = 2 THEN 'NO'
ELSE --DEFAULT VALUE IF ALLABOVE FAILED
'NA'
END
回答by Mudassir Hasan
You can try using subquery
您可以尝试使用子查询
SELECT CASE
WHEN ColName BETWEEN 0 AND 10 THEN '0-10'
WHEN ColName BETWEEN 10 AND 20 THEN '10-20'
WHEN ColName BETWEEN 20 AND 30 THEN '20-30'
...
FROM
(
SELECT <<expression>> as ColName
FROM tableName
) Z
