mysql - 获取每第 N 行需要项目的 MySQL 结果

Question

如果可能的话，我有一个关于 mysql 查询的问题。

示例:我们有产品表，每个产品都有字段类型，可以是“免费”或“黄金”。当我们按关键字搜索所有产品时，我们会得到类似

的结果

id | product_name | ... | type
---+--------------+-----+-----
1  | ...          | ... | free
2  | ...          | ... | gold
3  | ...          | ... | free
4  | ...          | ... | free
5  | ...          | ... | free
6  | ...          | ... | gold
7  | ...          | ... | free
8  | ...          | ... | free
9  | ...          | ... | gold
10 | ...          | ... | free
11 | ...          | ... | gold
12 | ...          | ... | free
13 | ...          | ... | free
14 | ...          | ... | gold
15 | ...          | ... | free
16 | ...          | ... | gold
17 | ...          | ... | free
18 | ...          | ... | free
19 | ...          | ... | free

问题是:如何排序这个结果以获得每个第 4 行类型“gold”

id | product_name | ... | type
---+--------------+-----+-----
1  | ...          | ... | free
3  | ...          | ... | free
4  | ...          | ... | free
2  | ...          | ... | gold
5  | ...          | ... | free
7  | ...          | ... | free
8  | ...          | ... | free
6  | ...          | ... | gold
10 | ...          | ... | free
12 | ...          | ... | free
13 | ...          | ... | free
9  | ...          | ... | gold
15 | ...          | ... | free
17 | ...          | ... | free
18 | ...          | ... | free
11 | ...          | ... | gold
19 | ...          | ... | free
14 | ...          | ... | gold
16 | ...          | ... | gold

我不知道如何解决它。可能吗？

我知道如何用 PHP 解决它，但我需要知道我是否可以用 MySQL 查询来解决它!

Answer 1

您可以使用以下查询:

SELECT id, product_name, type, r
FROM ( 
SELECT id, product_name, type,  
       IF((@r1+1) % 4 = 0, @r1:= @r1 + 2, @r1:= @r1 + 1) AS r
FROM mytable, (SELECT @r1:=0) AS var
WHERE type <> 'gold'
ORDER BY id ) t


UNION ALL

SELECT id, product_name, type,  r*4 AS r
FROM (
SELECT id, product_name, type,  
       @r2:= @r2+1 AS r
FROM mytable, (SELECT @r2:=0) AS var
WHERE type = 'gold' ) s
ORDER BY r

解释:

• UNION 的第一部分检索所有非“黄金”记录并分配 r 等于 1,2,3,5,6 的值， 7,9,.. 给他们。
• 第二部分检索所有“黄金”记录并为它们分配等于 4,8,12,... 的 r 值。
• ORDER BY r 应用于 UNION ALL 返回的整个集合并强制执行所需的排序。

Demo here