我想知道我是否可以在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