TSQL——在Where子句中使用派生的Select列

Is there a way in TSQL to do something like this:

TSQL是否有这样的方法:

select a,b,c,
case 
  when a=1 then 5
  when a=2 then 6
end as d
from some_table
where d=6

The actual case statement is really complex, so I'm trying to avoid repeating it in the where clause? Are there any tricks to do this?

实际的case语句非常复杂，所以我在where子句中尽量避免重复?有什么诀窍可以做到这一点吗?

(I think there's a trick in MySQL to use "having d=6").

(我认为MySQL中有一个技巧是使用“d=6”)。

6 个解决方案

#1

select
    a, b, c
from (
    select
        a, b, c,
        case 
          when a=1 then 5
          when a=2 then 6
        end as d
    from some_table
) as t
where d=6

#2

This is a good place to use CTEs, e.g.:

这是使用cte的好地方，例如:

WITH MassagedData (a, b, c, d) AS
(
    select a, b, c,
        case 
          when a=1 then 5
          when a=2 then 6
        end as d
    from some_table
)

SELECT a,b,c
FROM MassagedData
where d=6

#3

Make your posted query a sub-query and select from it where d = 6. As far as I know there is no way to reference a derived column in the same query.

让您的发布查询子查询并从中选择d = 6。就我所知，无法在同一个查询中引用派生列。

#4

I'm going to agree with AlexKuznetsov on this one, but I would also add that if your query is (no matter how much more complex) limiting in the WHERE clause cases that exist in the CASE, then those CASEs will never be returned and shouldn't be selected in the first place.

我要同意AlexKuznetsov这一个,但我也会增加,如果你查询(无论多么复杂得多)限制在WHERE子句例中存在的情况下,那么这种情况下永远都不会回来了,不应该选择在第一个地方。

For example, you're setting d to '6' where a is '2', then limiting to WHERE d = 6, so you could instead do:

例如，你将d设为'6'其中a是'2'，然后限制到d = 6，所以你可以这样做:

SELECT a,b,c,
    6 AS d
FROM some_table
WHERE a = 2

This will return the same results in a more optimized and clean fashion. This is why, IMHO, there's no point in being able to reference a derived column.

这将以更加优化和干净的方式返回相同的结果。这就是为什么，IMHO，不能引用派生列。

#5

Another option is to implement your case statement as a function. Especially good for conversion or calculation issues. What's nice about functions is that the 'business' logic is in one place and can easily be reused in other queries.

另一个选择是将case语句作为函数来实现。特别适合转换或计算问题。函数的好处在于，“业务”逻辑位于一个位置，可以很容易地在其他查询中重用。

-- sample code not tested

CREATE FUNCTION dbo.fn_MyConvertA(
    -- Add the parameters for the function here
    @a int
)
RETURNS int -- for example
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar as int

-- Add the T-SQL statements to compute the return value here
set @ResultVar = case when @a = 1 then 5 when @a = 2 then 6 else 10 end

-- Return the result of the function
RETURN @ResultVar

END
GO

-- now you case write your query
select a,b,c,  dbo.fn_MyConvertA(a) as d
from some_table          
where dbo.fn_MyConvertA(a)=6

#6

An alternative method to this is to use CROSS APPLY:

另一种方法是使用交叉应用:

select a,b,c,
from some_table
CROSS APPLY (SELECT case 
                    when a=1 then 5
                    when a=2 then 6
                    end) CxA(d)
where d=6

#1

select
    a, b, c
from (
    select
        a, b, c,
        case 
          when a=1 then 5
          when a=2 then 6
        end as d
    from some_table
) as t
where d=6

#2