MySQL：如何将多行记录扁平化为结果集中的不同列

场景回顾 #

你现在的查询：

select product_id, url from images where product_id = 1;

返回的结果是多行：

+------------+-------------------+
| product_id | url |
+------------+-------------------+
| 1 | https://img1.com |
| 1 | https://img2.com |
| 1 | https://img3.com |
+------------+-------------------+

期望转成这样的单行多列结果：

+------------+-------------------+-------------------+-------------------+
| product_id | url1 | url2 | url3 |
+------------+-------------------+-------------------+-------------------+
| 1 | https://img1.com | https://img2.com | https://img3.com |
+------------+-------------------+-------------------+-------------------+

方法一：窗口函数 + 条件聚合（MySQL 8.0+ 推荐） #

这种方法利用ROW_NUMBER()窗口函数给每个product_id下的url编序号，再通过CASE+聚合函数把不同序号的url映射到对应列：

SELECT
  product_id,
  MAX(CASE WHEN rn = 1 THEN url END) AS url1,
  MAX(CASE WHEN rn = 2 THEN url END) AS url2,
  MAX(CASE WHEN rn = 3 THEN url END) AS url3
FROM (
  -- 内层查询给每个product_id的url分配行号
  SELECT
    product_id,
    url,
    ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY url) AS rn
  FROM images
  WHERE product_id = 1
) AS temp
GROUP BY product_id;

说明： #

• PARTITION BY product_id：按product_id分组，每组内单独编行号
• ORDER BY url：可以指定url的排序规则，比如按创建时间排序的话改成ORDER BY created_at
• 如果同一个product_id的url数量更多，只需继续添加MAX(CASE WHEN rn = N THEN url END) AS urlN即可

方法二：用户变量模拟行号（MySQL 5.x 兼容） #

如果你的MySQL版本低于8.0，没有窗口函数，可以用用户变量来生成行号：

SELECT
  product_id,
  MAX(CASE WHEN rn = 1 THEN url END) AS url1,
  MAX(CASE WHEN rn = 2 THEN url END) AS url2,
  MAX(CASE WHEN rn = 3 THEN url END) AS url3
FROM (
  SELECT
    product_id,
    url,
    -- 当product_id和上一行相同时行号+1，否则重置为1
    @rn := IF(@prev_pid = product_id, @rn + 1, 1) AS rn,
    @prev_pid := product_id
  FROM images, 
       -- 初始化变量
       (SELECT @rn := 0, @prev_pid := NULL) AS vars
  WHERE product_id = 1
  ORDER BY product_id, url
) AS temp
GROUP BY product_id;

额外说明 #

• 如果同一个product_id的url数量不固定，比如有的是2条、有的是4条，上面的查询会把多余的列显示为NULL，少的列也会留空
• 如果需要动态生成对应数量的列，那得用存储过程拼接SQL语句，不过大部分场景下固定列数的方案就足够满足需求了

内容的提问来源于stack exchange，提问作者OneMoreError