如何避免SQL中SELECT、GROUP BY和ORDER BY中的函数重复

Question

我正在编写一个统计查询，其中值在SELECT,GROUP BY和ORDER BY中重复。必须重复相同的值会使读取查询和修改查询变得困难。

如何避免在下面的查询中重复FLOOR(COALESCE(LEN(Body), 0) / 100)3-4次。

SELECT FLOOR(COALESCE(LEN(Body), 0) / 100) * 100 as BodyLengthStart,
(FLOOR(COALESCE(LEN(Body), 0) / 100) + 1) * 100 - 1 as BodyLengthEnd,
COUNT(*) as MessageCount
FROM [Message]
GROUP BY FLOOR(COALESCE(LEN(Body), 0) / 100)
ORDER BY FLOOR(COALESCE(LEN(Body), 0) / 100)

查询的输出是消息的数量，按它们有多少个百位字符分组。

tbody> <<tr>

BodyLengthStart	BodyLengthEnd	MessageCount
0	99	130
100	199	76
200	299	36

使用CROSS APPLYs

SELECT  BodyLengthStart,
BodyLengthEnd,
COUNT(*)
FROM  [Message]
CROSS APPLY (
VALUES 
(FLOOR(COALESCE(LEN(Body), 0) / 100)) 
) a1(v)
CROSS APPLY (
VALUES
(v * 100, (v + 1) * 100 - 1)
) a2(BodyLengthStart, BodyLengthEnd)
GROUP BY BodyLengthStart,
BodyLengthEnd

一个选项可能是CTE (Common Table Expression)，类似于以下内容:

WITH x AS
(
SELECT FLOOR(COALESCE(LEN(Body), 0) / 100) AS BodyLength
FROM [Message]
)
SELECT BodyLength * 100 AS BodyLengthStart,
(BodyLength + 1) * 100 - 1 AS BodyLengthEnd,
COUNT(*) as MessageCount
FROM x
GROUP BY BodyLength
ORDER BY BodyLength 

作为旁注-如果在此之前的语句没有以分号(;)结束，则此语句将无法按预期工作。

使用子选择:

SELECT  BodyLengthStart,
BodyLengthEnd,
COUNT(*)
FROM (SELECT FLOOR(COALESCE(LEN(Body), 0) / 100) * 100 as BodyLengthStart,
(FLOOR(COALESCE(LEN(Body), 0) / 100) + 1) * 100 - 1 as BodyLengthEnd
FROM [Message]) as a
GROUP BY BodyLengthStart,
BodyLengthEnd

可以在FROM;之后定义SELECT;通过这种方式，您可以预先详细说明您的数据。

您可以使用一个通用的表表达式:

WITH cte AS 
(
SELECT FLOOR(COALESCE(LEN(Body), 0) / 100) * 100 as BodyLengthStart,
(FLOOR(COALESCE(LEN(Body), 0) / 100) + 1) * 100 - 1 as BodyLengthEnd
FROM [Message]
)
SELECT BodyLengthStart,BodyLengthEnd,COUNT(*)
FROM cte
GROUP BY BodyLengthStart,BodyLengthEnd