代码之家 › 专栏 › 技术社区 › Toma Tomov

选择多行作为这些行数据数组的MySQL查询

mysql

Toma Tomov · 技术社区 · 7 年前

(SELECT * FROM product WHERE 1 LIMIT 3) as first_array
(SELECT * FROM product WHERE 1 OFFSET 3 LIMIT 3) as second_array

希望你能理解我。抱歉,我不知道该怎么解释。

假设我只想要ids输出示例:

id_1   id_2
 1       4
 2       5
 3       6

我从帖子下面的答案中试过的是:

SELECT id as id_1 FROM `ct_product` WHERE 1 LIMIT 3
UNION ALL
SELECT id as id_2 FROM `ct_product` WHERE 1 LIMIT 3 OFFSET 3

我的表行是:

1 回复 | 直到 7 年前

Nick SamSmith1986 7 年前

您可以使用以下查询:

SELECT a1.id, a2.id
FROM (SELECT *, @rownum1:=@rownum1+1 AS rownum
      FROM (SELECT id
            FROM `ct_product`
            LIMIT 3
            ) art
      JOIN (SELECT @rownum1 := 0) r
      ) a1
JOIN  (SELECT *, @rownum2:=@rownum2+1 AS rownum
      FROM (SELECT id
            FROM `ct_product`
            LIMIT 3, 3
            ) art
      JOIN (SELECT @rownum2 := 0) r
      ) a2
ON a1.rownum = a2.rownum

输出: