我有一个类似以下的查询:
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 by表达式中使用查询该表CalculatedValue
(CASE WHEN ... THEN ... ELSE ... END)
CalculatedValue
不幸的是,这不起作用,因为group by在创建CTE
group by
CTE
还有什么其他方法可以使我不重复CASE WHEN...这么多次?
CASE WHEN...
使用CROSS APPLY,可用于定义别名字段,然后引用它们:
CROSS APPLY
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仅仅是一个别名,不管你喜欢,你可以将其命名。
CxA