mysql - 根据 SQL 中的条件进行计数

Question

我想计算级别

输入

|id|levels|
|--| --- |
|1 |9    |
|2 |12   |
|3 |21   |
|4 |23   |
|5 |11   |
|6 |31   |
|7 |23   |
|8 |11   |
|9 |31   |

预期产出

|range        |count|
|-------------|-----|
|more than 10 | 8   |
|more than 20 | 5   |
|more than 30 | 2   |

目前，我正在单独编写查询以获取大于 10、20 和 30 的级别的计数。但是我怎样才能在一个实例中将它们全部获取？

标签： mysqlsqlcountunion

Answer 1

如果您希望每个范围有 1 行，则使用 3 个不同UNION ALL的查询和最终结果：

SELECT 'more than 10' AS `range`, COUNT(*) AS count FROM tablename WHERE levels > 10
UNION ALL
SELECT 'more than 20' AS `range`, COUNT(*) AS count FROM tablename WHERE levels > 20
UNION ALL
SELECT 'more than 30' AS `range`, COUNT(*) AS count FROM tablename WHERE levels > 30

使用条件聚合获得 1 行 3 列的结果会更容易：

SELECT SUM(levels > 10) AS `more than 10`, 
       SUM(levels > 20) AS `more than 20`,
       SUM(levels > 30) AS `more than 30`
FROM tablename 

请参阅演示。