在 sql case 语句中使用比较符号 [英] Use comparison signs inside a sql case statement

问题描述

我正在寻找一种使用小于和大于符号在 sql 选择查询中构建 case 语句的方法.例如，我想根据一个变量选择一个排名:

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:

DECLARE @a INT
SET @a = 0

SELECT CASE 
         WHEN @a < 3 THEN 0
         WHEN @a = 3 THEN 1
         WHEN @a > 3 THEN 2
       END

我想把它写成:

DECLARE @a INT
SET @a = 0

SELECT CASE @a
         WHEN < 3 THEN 0
         WHEN 3 THEN 1
         WHEN > 3 THEN 2
       END

...但是 SQL 不允许我使用 <和 > 以这种方式表示.有没有一种方法可以做到这一点是 SQL 2005，或者我是否需要使用第一个中的代码.

...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 Server 是否必须为每个 CASE 语句运行计算.

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.

我正在寻找一个等效的 VB.NET case 语句:

I'm looking for a VB.NET case statement equivelent:

Select Case i
    Case Is < 100
        p = 1
    Case Is >= 100
        p = 2
End Select

也许在 SQL 中是不可能的，没关系，我只是想确认一下.

Maybe it's not possible in SQL and that's ok, I just want to confirm that.

推荐答案

您可以使用 SIGN 函数为

You can use the SIGN function as

DECLARE @a INT
SET @a = 0

SELECT CASE SIGN(@a - 3)
         WHEN -1 THEN 0
         WHEN 0 THEN 1
         WHEN 1 THEN 2
       END

如果 @a 小于 3，则 @a - 3 结果为负整数，其中 SIGN 返回 -1.

If @a is smaller than 3, then @a - 3 results in a negative int, in which SIGN returns -1.

如果 @a 为 3 或更大，则 SIGN 分别返回 0 或 1.

If @a is 3 or greater, then SIGN returns 0 or 1, respectively.

如果您想要的输出是 0、1 和 2，那么您可以进一步简化:

If the output you want is 0, 1 and 2, then you can simplify even more:

DECLARE @a INT
SET @a = 0

SELECT SIGN(@a - 3) + 1

这篇关于在 sql case 语句中使用比较符号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！