英文:
MYSQL: Incremental Sorting of Duplicate Numbers
问题
我有一个类似这样的表格：
| id | values |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 2 |
我想要按照某种方式将它们递增排序，可以使用循环，以以下方式排序：
| id | values |
|---|---|
| 1 | 1 |
| 4 | 2 |
| 2 | 1 |
| 5 | 2 |
| 3 | 1 |
| 6 | 2 |
我认为可以使用PHP轻松实现这个目标，但我想看看是否可以使用SQL来实现。
英文:
I have a table that looks like this:
| id | values |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 2 |
I would like to have them sorted incrementally in some sort of loop that orders them in this fashion
| id | values |
|---|---|
| 1 | 1 |
| 4 | 2 |
| 2 | 1 |
| 5 | 2 |
| 3 | 1 |
| 6 | 2 |
I believe this could be done easily with PHP however I would like to see if this can be done using SQL.
答案1
得分: 1
使用 ROW_NUMBER() 窗口函数在 ORDER BY 子句中：
SELECT *
FROM tablename
ORDER BY ROW_NUMBER() OVER (PARTITION BY `values` ORDER BY id),
`values`;
查看示例。<br/>
英文:
Use ROW_NUMBER() window function in the ORDER BY clause:
SELECT *
FROM tablename
ORDER BY ROW_NUMBER() OVER (PARTITION BY `values` ORDER BY id),
`values`;
See the demo.<br/>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论