mysql - 按逻辑排序并得到混合结果？

Question

我有这个表结构

  id | status
-----+------+
1    | a    |
2    | b    |
3    | b    |
4    | b    |
5    | a    |
6    | a    |
7    | b    |
8    | a    |

我有两个状态“a”和“b”。我需要按照这样的逻辑来排序:对于每两个“a”，显示一个“b”。所以像这样(“a”更重要，所以如果有很多“b”，它们就会留在最后)

  id | status
-----+------+
1    | a    |
5    | a    |
2    | b    |

6    | a    |
8    | a    |
3    | b    |

4    | b    |
7    | b    |

有办法吗？

Answer 1

select      id
           ,status

from       (select      status
                       ,id
                       ,@prev_status := @status
                       ,@status      := status
                       ,@rn          := case when @prev_status = status 
                                             then @rn + 1 
                                             else 1 
                                        end           as rn

            from        mytable t1,(select @status:=null,@rn:=0) x

            order by    status
                       ,id
            ) t

 order by  floor((rn-1) / case status when 'a' then 2 else 1 end)         
          ,case status when 'a' then 1 else 2 end  
          ,rn
;

---

+----+--------+
| id | status |
+----+--------+
|  1 | a      |
|  5 | a      |
|  2 | b      |
|  6 | a      |
|  8 | a      |
|  3 | b      |
|  4 | b      |
|  7 | b      |
+----+--------+

这将帮助您理解解决方案:
(group_id = floor((rn-1)/case status when 'a' then 2 else 1 end))

+--------+----+----+----------+
| status | id | rn | group_id |
+--------+----+----+----------+
| a      |  1 |  1 |        0 |
| a      |  5 |  2 |        0 |
| a      |  6 |  3 |        1 |
| a      |  8 |  4 |        1 |
| b      |  2 |  1 |        0 |
| b      |  3 |  2 |        1 |
| b      |  4 |  3 |        2 |
| b      |  7 |  4 |        3 |
+--------+----+----+----------+