我想知道我是否可以在case语句的最后一行中添加总数:

SELECT
    CASE
        WHEN weight <= 10 THEN "very small"
        WHEN weight > 10 AND weight <= 30 THEN "small"
        WHEN weight > 30 AND weight <= 50 THEN "medium"
        WHEN weight > 50 AND weight <= 85 THEN "large"
        WHEN weight > 85 THEN "very large"
        END AS dog_weights, count(weight) as count
FROM dogs
GROUP BY dog_weights;

输出表
| Dog_Weights | Count |
|-------------|-------|
| Very Small  | 20    |
| Small       | 20    |
| Medium      | 40    |
| Large       | 20    |

所需表格
| Dog_Weights | Count |
|-------------|-------|
| Very Small  | 20    |
| Small       | 20    |
| Medium      | 40    |
| Large       | 20    |
| Total       | 100   |

我可以用一个子查询吗?谢谢。

最佳答案

MySQL中最简单的方法是使用rollup

SELECT (CASE WHEN weight <= 10 THEN 'very small'
             WHEN weight > 10 AND weight <= 30 THEN 'small'
             WHEN weight > 30 AND weight <= 50 THEN 'medium'
             WHEN weight > 50 AND weight <= 85 THEN 'large'
             WHEN weight > 85 THEN 'very large'
         END) AS dog_weights,
        count(*) as count
FROM dogs
GROUP BY dog_weights WITH rollup;

要获取TOTAL,我认为您需要一个子查询:
假设weight永远不会NULL,则可以执行以下操作:
SELECT COALESCE(dog_weights, 'total') as dog_weights, count
FROM (SELECT (CASE WHEN weight <= 10 THEN 'very small'
                   WHEN weight > 10 AND weight <= 30 THEN 'small'
                   WHEN weight > 30 AND weight <= 50 THEN 'medium'
                   WHEN weight > 50 AND weight <= 85 THEN 'large'
                   WHEN weight > 85 THEN 'very large'
               END) AS dog_weights,
              count(*) as count
      FROM dogs
      GROUP BY dog_weights WITH rollup
     ) d

10-05 23:09
查看更多