我有一个类似以下的查询:
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.cCxA只是一个别名,您可以随意命名.
The CxA is just an alias and you can name it whatever you like.
更多推荐
通过使用CTE简化SQL语句
发布评论