有配额的人数统计

Question

我有一个SQL Server表FarmerGroups，其中的数据如下

Farmer ID | Farmer Name | PROD_EST | Season
----------+-------------+----------+--------
001       | FRANK MENA  | 560      |  3
003       | KEVIN B ROSE| 127      |  2

我需要清点第三季prod_est在以下范围内的农民：

0-100 Tons
100 - 250 Tons
250 - 500 Tons
500 - 1000 Tons
1000 - 5000 Tons
5000 - 10000 Tons
10000 Plus Tons

输出示例：

COUNT | RANGE
------+-------
  100 | 0-100
   50 | 100-250

Answer 1

; WITH cteGroup as (
    SELECT ProdEst 
        , CASE WHEN ProdEst  <= 100 AND ProdEst  >= 0 
               THEN 'G1'
            WHEN ProdEst  <= 250  AND ProdEst  > 100 
               THEN 'G2'
            WHEN ProdEst  <= 500  AND ProdEst  > 250 
               THEN 'G3'
            -- ...
            WHEN ProdEst  <= 10000  AND ProdEst  > 5000 
               THEN 'G6'
            ELSE 'G7' END as Grp
    FROM FarmerGroups 
    WHERE Season = 3
)SELECT Count(*) as [COUNT]
    , CASE WHEN Grp = 'G1' THEN '0-100 Tons'
        WHEN Grp = 'G2' THEN '100-250 Tons'
        WHEN Grp = 'G3' THEN '250-500 Tons'
        -- ...
        WHEN Grp = 'G7' THEN '10000 Plus Tons'
    END as [RANGE]
FROM cteGroup
GROUP BY Grp