php - 查找具有最多行 MYSQL 的范围

Question

我有一张保存价格的表格。这背后的想法是让用户建议他们认为合理的价格。例如，假设我有一个表名 tbl_Prices:

| id |price |
|1   |1200  |
|2   |50000 |
|3   |50000 |
|4   |45000 |
|5   |506980|
|6   |2000  |

从这个表中我们可以看出，行数最多的范围是50000 到 506980。所以我希望提出一个查询来找到这个范围。

我看了this answer .这很好，但由于某些原因，我无法增加偏移量，我几乎无法理解发生了什么，它产生:

|from_price|to_price|count|
|499999    | 500003 |2    |
|499998    | 500002 |2    |
|499997    | 500001 |2    |
|499996    | 500000 |2    |
|500000    | 500004 |2    |
|499996    | 450000 |1    |

Answer 1

我已经找到了根据如下定义的范围找出总数的解决方案:

SELECT
CASE WHEN accountid BETWEEN 0 AND 216 THEN '0 to 216'
WHEN accountid BETWEEN 217 and 319 THEN '217 to 319'
WHEN accountid BETWEEN 320 and 429 THEN '320 to 429'
WHEN accountid BETWEEN 430 and 539 THEN '430 to 539'
WHEN accountid BETWEEN 540 and 649 THEN '540 to 649'
WHEN accountid BETWEEN 650 and 759 THEN '650 to 759'
WHEN accountid >= 860 THEN '860 +' END AS accountgroup, count(accountid) AS total
FROM account
GROUP BY accountgroup;

输出如下:

希望对您有所帮助。谢谢。