我想要一张这样的桌子
显示我们拥有的书,按页数排名.我们要使用50页范围来对页数进行排名.这意味着任何页数在500-549之间的书都具有相同的等级；页数550-599具有相同的等级；页数600-649具有相同的等级；页数650-699具有相同的等级,以此类推.在示例显示中,我们有四本书全部位于等级3.请注意,我们没有跳过等级编号-请检查前两个示例行.不要假设最大页面计数值.在设计查询逻辑时.不要使用表中的当前数据集来决定逻辑.
+---------+------------+------+
| Book_ID | Page_count | Rank |
+---------+------------+------+
| 1001 | 2000 | 1 |
| 1587 | 1300 | 2 |
| 8546 | 982 | 3 |
| 8415 | 980 | 3 |
| 1474 | 976 | 3 |
| 1444 | 970 | 3 |
| 1524 | 918 | 4 |
| | 894 | 5 |
| 200 | 879 | 5 |
| 201 | 850 | 5 |
| 4574 | 825 | 6 |
| 6584 | 825 | 6 |
编辑：现在我有
SELECT b.book_id AS Book_ID, b.page_count as Page_Count, (
SELECT COUNT(DISTINCT page_count)
FROM a_bkinfo.books as b2
WHERE b2.page_count >= b.page_count
) as Rank
FROM a_bkinfo.books as b
ORDER BY Page_Count DESC
现在我得到这个结果
Book_ID Page_Count Rank
1448 3192 1
1306 2895 2
1979 1368 3
1678 1002 4
1677 982 5
1689 980 6
我需要弄清楚如何做50页范围
解决方法:
我认为您正在寻找这样的东西：
select
book_id,
page_count,
(select count(distinct b2.page_count DIV 50)
from books b2
where b2.page_count DIV 50 >= books.page_count DIV 50) as page_rank
from books
order by page_count desc
标签：mysql
来源： https://codeday.me/bug/1031/1976543.html
