交织MySQL表的行 [英] Interleave rows of MySQL table
问题描述
我有一个包含数据和一个类的表,类似
I have a table containing data and a class, something like
----------------
| DATA | Class |
----------------
| 1 | A |
| 2 | A |
| 5 | B |
| 10 | A |
| 2 | A |
| 45 | B |
| 90 | B |
----------------
我想交错两个类以获得类似的内容:
I would like to interleave the two classes to obtain something like this:
----------------
| DATA | Class |
----------------
| 1 | A |
| 5 | B |
| 2 | A |
| 45 | B |
| 2 | A |
| 90 | B |
| 10 | A |
----------------
我考虑过即时生成一列,该列将为每个单独的类添加一个顺序递增的索引并对该类进行排序,但是需要一个好的方向/指针来做到这一点.
I thought about generating on the fly an additional column that will add a sequential incrementing index to each separate class and sort on that one, but need a good direction/pointer to do that.
类似的东西:
-----------------------
| DATA | Class | indx |
-----------------------
| 1 | A | 1 |
| 5 | B | 1 |
| 2 | A | 2 |
| 45 | B | 2 |
| 2 | A | 3 |
| 90 | B | 3 |
| 10 | A | 4 |
-----------------------
仅使用MySQL是否有可能?
Is it even possible using only MySQL?
推荐答案
独立枚举每个类,然后按枚举排序:
Enumerate each class independently, and then order by the enumeration:
select data, class, idx
from (select a.*,
(@seqnum := if(@class = class, @seqnum + 1,
if(@class := class, 1, 1)
)
) as idx
from atable a cross join
(select @class := NULL, @seqnum := 0) vars
order by class, data
) a
order by idx, class;
此查询独立枚举每个类,因此数据最小的行的值为1,依此类推.为此,它正在使用MySQL变量.在其他数据库中,您将使用row_number()
.进行枚举后,最后一步就是按该字段排序.
This query enumerates each class independently, so the row with the smallest data gets a value of 1 and so on. It is using MySQL variables for this purpose. In other databases, you would use row_number()
. Once you have the enumeration, the final step is just to sort by that field.
这篇关于交织MySQL表的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!