交织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屋！