SQL(MySQL)中是否有一种方法可以执行“循环"操作?在特定字段上订购? [英] Is there a way in SQL (MySQL) to do a "round robin" ORDER BY on a particular field?

问题描述

SQL(MySQL)中是否可以在特定字段上执行循环" ORDER BY?

Is there a way in SQL (MySQL) to do a "round robin" ORDER BY on a particular field?

作为一个例子，我想要一张这样的桌子:

As an example, I would like to take a table such as this one:

+-------+------+
| group | name |
+-------+------+
|     1 | A    |
|     1 | B    |
|     1 | C    |
|     2 | D    |
|     2 | E    |
|     2 | F    |
|     3 | G    |
|     3 | H    |
|     3 | I    |
+-------+------+

并运行按以下顺序产生结果的查询:

And run a query that produces results in this order:

+-------+------+
| group | name |
+-------+------+
|     1 | A    |
|     2 | D    |
|     3 | G    |
|     1 | B    |
|     2 | E    |
|     3 | H    |
|     1 | C    |
|     2 | F    |
|     3 | I    |
+-------+------+

请注意，表可能有很多行，因此我无法在应用程序中进行排序. (显然，查询中也有LIMIT子句.)

Note that the table may have many rows, so I can't do the ordering in the application. (I'd obviously have a LIMIT clause as well in the query).

推荐答案

您可以做的是创建一个临时列，在其中创建集合以提供类似以下的信息:

What you can do is create a temporary column in which you create sets to give you something like this:

+-------+------+-----+
| group | name | tmp |
+-------+------+-----+
|     1 | A    |   1 |
|     1 | B    |   2 |
|     1 | C    |   3 |
|     2 | D    |   1 |
|     2 | E    |   2 |
|     2 | F    |   3 |
|     3 | G    |   1 |
|     3 | H    |   2 |
|     3 | I    |   3 |
+-------+------+-----+

要了解如何创建集合，请查看以下 question/答案.

To learn how to create the sets, have a look at this question/answer.

然后简单

ORDER BY tmp, group, name

这篇关于SQL(MySQL)中是否有一种方法可以执行“循环"操作?在特定字段上订购?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！