mySQL - 使用动态范围在不同范围内计算用户的总点数

Question

当我尝试执行以下查询时，它给出了正确的结果，但我想动态显示范围

 SELECT
 CASE
 WHEN points <= 25 THEN '0-25'
 WHEN points BETWEEN 25 AND 50 THEN '25-50'
 WHEN points BETWEEN 50 AND 75 THEN '50-75'
 WHEN points BETWEEN 75 AND 100 THEN '75-100'
 ELSE '> 100'
 END AS `Range`
 ,COUNT(*) AS `Count`
 ,CASE
 WHEN points <= 25 THEN 1
 WHEN points BETWEEN 25 AND 50 THEN 2
 WHEN points BETWEEN 50 AND 75 THEN 3
 WHEN points BETWEEN 75 AND 100 THEN 4
 ELSE 5
 END AS `Sort`
 FROM (
 SELECT user_id, SUM(points) as points
 FROM tbl
 GROUP BY user_id
 ) AS summary
 GROUP BY `Range`, `Sort`

输出

+--------+-------+
| Range  | Count |
+--------+-------+
| 0-20   | 01    |
| 25-50  | 02    |
| 50-75  | 01    |
| 75-100 | 04    |
| > 100  | 00    |
+--------+-------+

我从另一个表的以下值中获得 100 值

从 tbl1 中选择最小值(点)，其中 1 LIMIT 0,1

如果最小值为 100，则输出将为:

输出

+--------+-------+
| Range  | Count |
+--------+-------+
| 0-20   | 01    |
| 25-50  | 02    |
| 50-75  | 01    |
| 75-100 | 04    |
| > 100  | 00    |
+--------+-------+

如果最小值为 500，则输出将为:

输出

+---------+-------+
| Range   | Count |
+---------+-------+
| 0-100   | 01    |
| 100-200 | 02    |
| 300-400 | 01    |
| 400-500 | 04    |
| > 500   | 00    |
+---------+-------+

提前致谢

Answer 1

SELECT CASE WHEN points <= range1 THEN CONCAT('0-', range1)
            WHEN points <= range2 THEN CONCAT(range1, '-', range2)
            WHEN points <= range3 THEN CONCAT(range2, '-', range3)
            WHEN points <= range4 THEN CONCAT(range3, '-', range4)
            ELSE CONCAT('> ', range4)
       END AS `Range`
       ,COUNT(*) AS `Count`
       ,LEAST(5, GREATEST(1, CEIL(points/range1))) AS Sort
FROM (SELECT user_id, SUM(points) as points
      FROM tbl
      GROUP BY user_id
     ) AS summary
JOIN (SELECT FLOOR(MIN(points)*.25) range1, FLOOR(MIN(points)*.50) range2, FLOOR(MIN(points)*.75) range3, MIN(points) range4 FROM tbl1) x
GROUP BY `Range`, `Sort`

请注意，您不需要使用 BETWEEN在 WHEN条款。这些子句按顺序进行测试，因此如果它超过 points <= 25 ，它必须大于25并且没有必要在下一个子句中明确测试这一点。