在 MSSQL 中选择和分组编号范围

Question

SELECT
CASE
WHEN Vote BETWEEN 1 and 6 THEN '1-8'
WHEN Vote BETWEEN 7 and 8 THEN '7-8'
WHEN Vote BETWEEN 9 and 10 THEN '9-10'
END AS Vote_range,
COUNT(*) AS count
FROM CaseRatingVote
GROUP BY Vote_range
GO

如何在 MSSQL 中使其工作，我需要在不同数字范围内设置一个组。

Answer 1

在SELECT和GROUP BY中使用完全相同的表达式。

SELECT
CASE
WHEN Vote BETWEEN 1 and 6 THEN '1-8'
WHEN Vote BETWEEN 7 and 8 THEN '7-8'
WHEN Vote BETWEEN 9 and 10 THEN '9-10'
END AS Vote_range,
COUNT(*) AS count
FROM 
CaseRatingVote
GROUP BY 
CASE
WHEN Vote BETWEEN 1 and 6 THEN '1-8'
WHEN Vote BETWEEN 7 and 8 THEN '7-8'
WHEN Vote BETWEEN 9 and 10 THEN '9-10'
END

或者，作为替代方法，先计算分组列(不分组(，然后在外部范围内按该列分组。此方法更适合维护。

;WITH CTE AS
(
SELECT
CASE
WHEN Vote BETWEEN 1 and 6 THEN '1-8'
WHEN Vote BETWEEN 7 and 8 THEN '7-8'
WHEN Vote BETWEEN 9 and 10 THEN '9-10'
END AS Vote_range
FROM 
CaseRatingVote
)
SELECT
Vote_range,
COUNT(1)
FROM
CTE AS C
GROUP BY
C.Vote_range

Answer 2

SELECT SUM(CASE WHEN Vote < 7 THEN 1 ELSE 0 END) AS [1-6],
SUM(CASE WHEN Vote BETWEEN 7 AND 8 THEN 1 ELSE 0 END) AS [7-8],
SUM(CASE WHEN Vote BETWEEN 9 AND 10 THEN 1 ELSE 0 END) AS [9-10],
COUNT(*) AS count
FROM CaseRatingVote

这对我来说是最简单的方法。

Answer 3

您可以使用 CTE 执行此操作：

with a as (
SELECT
CASE
WHEN Vote BETWEEN 1 and 6 THEN '1-8'  --You may want to change this line!
WHEN Vote BETWEEN 7 and 8 THEN '7-8'
WHEN Vote BETWEEN 9 and 10 THEN '9-10'
END AS Vote_range
FROM CaseRatingVote
)
select  Vote_Range, count(*) as Row_Count
from    a
group by Vote_Range

Answer 4

将查询包装在派生表中，group by其结果：

select Vote_range, count(*) from
(
SELECT
CASE
WHEN Vote BETWEEN 1 and 6 THEN '1-8'
WHEN Vote BETWEEN 7 and 8 THEN '7-8'
WHEN Vote BETWEEN 9 and 10 THEN '9-10'
END AS Vote_range
FROM CaseRatingVote
) dt
GROUP BY Vote_range