mysql - 如何在每第n行选择混合结果？

Question

我这里有一个产品表

product_id, popularity, date_add

我想选择产品并按受欢迎程度排序，并将新产品分布在每 5 行(5,10,15，...)中。能够再次选择第 2 页。输出应该是这样的。

popularity #1 
popularity #2 
popularity #3 
popularity #4
latest #1
popularity #5
popularity #6
popularity #7
popularity #8
latest #2
popularity #9
popularity #10
popularity #11
popularity #12
latest #3

我该怎么做？谢谢!

Answer 1

DDL:

create table Te (product_id int, popularity int, date_add int);

insert into Te (product_id, popularity, date_add)
values
(1,      10,               1),
(2,      21,               1),
(3,      12,               1),
(4,      23,               1),
(5,      5,                1),
(6,      2,                5),
(7,      3,                6),
(8,       1,                7),
(9,       1,                1),
(11,      210,            11),
(12,      221,            11),
(13,      212,            11),
(14,      223,            11),
(15,      25,             11),
(16,      22,             15),
(17,      23,             16),
(18 ,      21,             27),
(19,       21,             31);

SQL(使用循环等很容易构建)

(select * from Te order by popularity desc LIMIT 5 OFFSET 0) 
UNION ALL
(select * from Te order by date_add desc LIMIT 1 OFFSET 0) 
UNION ALL

(select * from Te order by popularity desc LIMIT 5 OFFSET 5) 
UNION ALL
(select * from Te order by date_add desc LIMIT 1 OFFSET 1) 
UNION ALL

(select * from Te order by popularity desc LIMIT 5 OFFSET 10) 
UNION ALL
(select * from Te order by date_add desc LIMIT 1 OFFSET 2) 
UNION ALL

(select * from Te order by popularity desc LIMIT 5 OFFSET 15) 
UNION ALL
(select * from Te order by date_add desc LIMIT 1 OFFSET 3) 

结果:

product_id  popularity  date_add

    14        223        11
    12        221        11
    13        212        11
    11        210        11
    15        25         11
    *19       21         31
    17        23         16
    4         23         1
    *16       22         15
    *19       21         31
    *2        21         1
    18        21         27
    *2        21         1
    3         12         1
    1         10         1
    5         5          1
    7         3          6
    17        23         16
    6         2          5
    9         1          1
    8         1          7
    *16       22         15

正如您通过 ID 看到的那样，您可能会发现重复项，您可能想要过滤掉这些...