问如何通过交替(0，0，0，1，1，1，1，1，1，1，1，0，0，0，...)对结果进行排序行
EN

Stack Overflow用户

提问于 2019-09-03 07:17:37

回答 3查看 139关注 0票数 2

我使用MySQL来执行数据库事务，使用PHP来运行MySQL查询。我想使用is_free列(0，0，0，1，1，1，1，1，1，1，1，1，1，1，0，0，0，...)的值通过这个序列获取查询结果。我想要按is_free列的值的3:6的比例来获取记录。

有没有可能做到这一点？

我搜索了不同的算法，但没有找到符合我要求的。我已经找到了唯一的查询，它抛出了替代记录的结果，但我的要求没有得到满足。

我希望输出像这样

id     is_free
--     -------
1      0
2      0
3      0
4      1
5      1
6      1
7      1
8      1
9      1
10     0
11     0
12     0

sql

mysql

TDSQL-CMySQL免费体验

云原生数据库TDSQL-C MySQL 2核4G 免费体验15天

回答 3

Stack Overflow用户

发布于 2019-09-03 12:07:49

你需要一些类似于ROW_NUMBER() OVER (PARTITION BY is_free ORDER BY id)的东西。一旦你有了每个分区的行号(例如0-0，0-1，0-2，0-3，...，1-0，1-1，1-2，1-3，...)您可以使用基本数学对结果进行排序(值0-0...0-2和1-0...1-5首先排序，0-3...0-5和1-6...1-11排序，依此类推)。不幸的是，MySQL 5.x不支持窗口函数，因此您需要使用某种技巧来计算行数。这里有一个：

SELECT *
     , rn div IF(is_free = 1, 6, 3) AS gn
FROM (
    SELECT id
         , is_free
         , (SELECT COUNT(*) FROM t AS x WHERE x.id < t.id AND x.is_free = t.is_free) AS rn
    FROM t
) AS x
ORDER BY gn, is_free

Demo on db<>fiddle

票数 1

Stack Overflow用户

发布于 2019-09-03 11:38:37

在MySQL 8+中，您可以使用：

order by floor( (row_number() over (partition by is_free order by id) - 1) / 4),
         is_free, id

row_number()上的算法对它们进行批量处理。您可以对早期版本中的变量执行类似的操作：

select t.id, t.is_free
from (select t.*,
             (@rn := if(@if = is_free, @rn + 1,
                        if(@if := is_free, 1, 1)
                       )
             ) as rn
      from (select t.* from t order by is_free, id) t cross join
           (select @if := -1, @rn := 0) params
     ) t
order by floor((rn - 1) / 4), is_free, id;

票数 0

Stack Overflow用户

发布于 2019-09-04 11:50:12

select id,is_free,rank
from (
         (
            select id,is_free,if(@row_num = @target,@target := @target + 9,@target := @target + 0) as target,if(@target - @row_num > 3,@row_num := @row_num + 7,@row_num := @row_num + 1) as rank
            from test,(select @row_num := 0,@target := 3) t2
            where is_free = 0
         )
        union
          (
            select id,is_free,if(@row_num2 = @target2,@target2 := @target2 + 9,@target2 := @target2 + 0) as target,if(@target2 - @row_num2 > 8,@row_num2 := @row_num2 + 4,@row_num2 := @row_num2 + 1) as rank
            from test,(select @row_num2 := 3,@target2 := 9) t3
            where is_free = 1
          )
) derived
order by rank,is_free,id;

演示： https://www.db-fiddle.com/f/pc9oCwwtWjacETVeGCMtcw/0

上面的想法是给每一行一个rank，这样我们就可以保持3:6的比率。
如果我们仔细观察，0的排名与1 , 2 , 3 , 10 , 11 , 12 , 19 , 20 , 21的排名相同，1的排名与4, 5, 6, 7, 8, 9, 13, 14, 15, 16, 17, 18, 22, 23 ...的排名相同，如下所示：

系列：

0   0   0   1   1   1   1   1   1   0   0   0   1   1   1   1   1   1   0   0   0

1   2   3   4   5   6   7   8   9  10   11  12  13  14  15  16  17  18  19  20  21