在 sql case 语句中使用比较符号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9460328/
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
Use comparison signs inside a sql case statement
提问by Greg
I'm looking for a way to build case statements in a sql select query using less than and greater than signs. For example, I want to select a ranking based on a variable:
我正在寻找一种使用小于和大于符号在 sql select 查询中构建 case 语句的方法。例如，我想根据变量选择排名：
DECLARE @a INT
SET @a = 0
SELECT CASE
WHEN @a < 3 THEN 0
WHEN @a = 3 THEN 1
WHEN @a > 3 THEN 2
END
I'd like to write it as:
我想把它写成：
DECLARE @a INT
SET @a = 0
SELECT CASE @a
WHEN < 3 THEN 0
WHEN 3 THEN 1
WHEN > 3 THEN 2
END
...but SQL doesn't let me use the < and > signs in this way. Is there a way that I can do this is SQL 2005, or do I need to use the code like in the first one.
...但 SQL 不允许我以这种方式使用 < 和 > 符号。有没有一种方法可以做到这一点是 SQL 2005，或者我是否需要使用第一个中的代码。
The reason for only wanting the code there once is because it would make the code a lot more readable/maintainable and also because I'm not sure if SQL server will have to run the calculation for each CASE statement.
只想要代码一次的原因是因为它会使代码更具可读性/可维护性，而且我不确定 SQL Server 是否必须为每个 CASE 语句运行计算。
I'm looking for a VB.NET case statement equivelent:
我正在寻找一个 VB.NET case 语句等效：
Select Case i
Case Is < 100
p = 1
Case Is >= 100
p = 2
End Select
Maybe it's not possible in SQL and that's ok, I just want to confirm that.
也许这在 SQL 中是不可能的，没关系，我只是想确认一下。
回答by Jose Rui Santos
You can use the SIGNfunction as
您可以使用SIGN函数作为
DECLARE @a INT
SET @a = 0
SELECT CASE SIGN(@a - 3)
WHEN -1 THEN 0
WHEN 0 THEN 1
WHEN 1 THEN 2
END
If @ais smaller than 3, then @a - 3results in a negative int, in which SIGN returns -1.
如果@a小于 3，则@a - 3结果为负整数，其中 SIGN 返回 -1。
If @ais 3 or greater, then SIGN returns 0 or 1, respectively.
如果@a是 3 或更大，则 SIGN 分别返回 0 或 1。
If the output you want is 0, 1 and 2, then you can simplify even more:
如果您想要的输出是 0、1 和 2，那么您可以进一步简化：
DECLARE @a INT
SET @a = 0
SELECT SIGN(@a - 3) + 1
回答by Andriy M
Using SIGNas suggested by @Jose Rui Santosseems a nice workaround. An alternative could be to assign the expression an alias, use a subselect and test the expression (using its alias) in the outer select:
SIGN按照@Jose Rui Santos 的建议使用似乎是一个不错的解决方法。另一种方法是为表达式分配一个别名，使用子选择并在外部选择中测试表达式（使用其别名）：
SELECT
…,
CASE
WHEN expr < 3 THEN …
WHEN expr > 3 THEN …
END AS …
FROM (
SELECT
…,
a complex expression AS expr
FROM …
…
)
回答by Venkat
SELECT
CASE
WHEN ColumnName >=1 and ColumnName <=1 THEN 'Fail'
WHEN ColumnName >=6 THEN 'Pass'
ELSE 'Test'
END
FROM TableName
