通过使用CTE简化SQL语句 [英] simplify SQL statement by using CTE

问题描述

我有一个类似以下的查询:

I have a query like the following:

SELECT A.a, A.b, B.c,
(CASE WHEN ... THEN ... ELSE ... END) AS CalculatedValue,
B.d
FROM    dbo.TableA A INNER JOIN
        dbo.TableB B ON (...)
WHERE (CASE WHEN ... THEN ... ELSE ... END) BETWEEN @DayStart AND @DayEnd
GROUP BY A.a, (CASE WHEN ... THEN ... ELSE ... END), B.c

为避免多次重复相同的表达式:(CASE WHEN ... THEN ... ELSE ... END)我想定义一个CTE，并在select，where和group中使用表达式CalculatedValue

to avoid repeating many times the exact same expression: (CASE WHEN ... THEN ... ELSE ... END) I wanted to define a CTE and query such table using in the select, where and group by the expression CalculatedValue

不幸的是，这不起作用，因为在创建CTE

unfortunately this does not work because the select needs to already include the group by when creating the CTE

还有什么其他方法可以使我不重复CASE WHEN...这么多次?

is there any other way I could use to not repeat the CASE WHEN... so many times?

推荐答案

使用 CROSS APPLY ，可用于定义别名字段，然后引用它们:

Use CROSS APPLY, which can be used to define aliased fields and then refer to them:

SELECT A.a, 
       A.b, 
       B.c,
       CalculatedValue,
       B.d
FROM    
       dbo.TableA A 
INNER JOIN
        dbo.TableB B 
        ON (...)
CROSS APPLY 
        (SELECT (CASE WHEN ... THEN ... ELSE ... END)) CxA(CalculatedValue)
WHERE CalculatedValue BETWEEN @DayStart AND @DayEnd
GROUP BY A.a, CalculatedValue, B.c

CxA只是一个别名，您可以随意命名.

The CxA is just an alias and you can name it whatever you like.

这篇关于通过使用CTE简化SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！