mysql - 如何按音调分组？

Question

我有一张“付款”表

user_id amount
1       300
1       100
2       100
2       100
3       10
4       200

我应该进行什么查询以按组显示结果：

diapason       number
0 -10          0
10 - 100       1
100 -200       3
more than 200  2

我认为我应该使用“拥有”？

标签： mysqlsqldatabasejoinselect

Answer 1

对于这种类型的查询，您可以使用UNION：

SELECT '0-10' as diapason, SUM(CASE WHEN amount < 10 THEN 1 ELSE 0 END) AS number FROM payments
UNION
SELECT '10-100', SUM(CASE WHEN amount BETWEEN 10 AND 99 THEN 1 ELSE 0 END) FROM payments
UNION
SELECT '100-200', SUM(CASE WHEN amount BETWEEN 100 AND 199 THEN 1 ELSE 0 END) FROM payments
UNION
SELECT 'more than 200', SUM(CASE WHEN amount >= 200 THEN 1 ELSE 0 END) FROM payments

输出：

diapason        number
0-10            0
10-100          1
100-200         3
more than 200   2

SQLFiddle 演示