通过使用CTE简化SQL语句

本文介绍了通过使用CTE简化SQL语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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.