如何在MSSQL中实现按投票数值范围分组统计？

Got it, let's break down why your original query isn't working right in MSSQL and fix it up:

First, two quick issues to note with your initial code:

1. Alias can't be used in GROUP BY: MSSQL runs query clauses in a specific order (FROM → WHERE → GROUP BY → SELECT), so the Vote_range alias you define in the SELECT clause doesn't exist yet when the GROUP BY runs. That's why grouping by the alias fails.
2. Logical typo: Your first CASE checks for votes 1-6 but returns '1-8'—that's a mismatch. I'll correct that to '1-6' in the solutions below, since it aligns with the range condition.

Solution 1: Repeat the CASE Expression in GROUP BY #

This is the simplest fix—just duplicate your CASE logic in the GROUP BY clause so SQL can use the calculated value directly:

SELECT 
    CASE 
        WHEN Vote BETWEEN 1 and 6 THEN '1-6'
        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-6'
        WHEN Vote BETWEEN 7 and 8 THEN '7-8'
        WHEN Vote BETWEEN 9 and 10 THEN '9-10' 
    END 
GO

Solution 2: Use a CTE for Cleaner, Reusable Code #

If your range logic ever gets more complex, repeating the CASE can get messy. A CTE (Common Table Expression) lets you calculate the vote range first, then group on the precomputed value:

WITH VoteRangeCTE AS (
    SELECT 
        CASE 
            WHEN Vote BETWEEN 1 and 6 THEN '1-6'
            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 count
FROM VoteRangeCTE
GROUP BY Vote_range
GO

Solution 3: Use a Subquery (Alternative to CTE) #

If you prefer subqueries over CTEs, this works the same way—compute the range in an inner query, then group in the outer query:

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

All three solutions will correctly group your votes into the specified ranges and return the counts you need.

内容的提问来源于stack exchange，提问作者user2438077