mysql - 在 MySQL 中将记录展平为列

Question

我想将表中的多条记录展平为结果集中的不同列。例如:

select product_id, url from images where product_id = 1;

+-------------+-------------------------------+
| product_id  | url                           |
+-------------+-------------------------------+
|           1 | http://example.com/images/abc |
|           1 | http://example.com/images/def |
|           1 | http://example.com/images/ghi |
+-------------+-------------------------------+

我希望结果集包含如下所示的三列:

+-------------+---------------------------------+-------------------------------+-------------------------------+
| product_id  | Image 1                         | Image 2                       | Image 3                       | 
+-------------+---------------------------------+-------------------------------+-------------------------------+
|           1 | http://example.com/images/abc   | http://example.com/images/def | http://example.com/images/ghi |
+-------------+---------------------------------+-------------------------------+-------------------------------+

如果我们没有三行，我希望最后一列为空。对于前-

select product_id, url from images where product_id = 2;
+-------------+-------------------------------+
| product_id  | url                           |
+-------------+-------------------------------+
|           2 | http://example.com/images/abc |
|           2 | http://example.com/images/def |
+-------------+-------------------------------+

那么，输出应该是:

+-------------+---------------------------------+-------------------------------+-------------------------------+
| product_id  | Image 1                         | Image 2                       | Image 3                       | 
+-------------+---------------------------------+-------------------------------+-------------------------------+
|           2 | http://example.com/images/abc   | http://example.com/images/def | NULL                          |
+-------------+---------------------------------+-------------------------------+-------------------------------+

如果超过 3 行，我们可以安全地忽略超过 3 行。

如有任何帮助，我们将不胜感激。

Answer 1

MySQL 非常擅长使本应简单的事情变得更加困难。这仍然是一个 PIVOT，但它在 MySQL 中与 T-SQL 略有不同。

编辑:我们可以复制 ROW_NUMBER() 功能，动态变量 @rn 保存行号， @pid 保存前一行的 product_ID，然后 CROSS APPLY 回到 images 表。我使用 rn 值关联 product_ID 记录。

Product_ID 3 表明它将忽略返回的任何超过 3 个图像 url。

SQL Fiddle

MySQL 5.6 架构设置:

CREATE TABLE images (product_id int, url varchar(50) ) ;
INSERT INTO images 
SELECT 1,'http://example.com/images/abc' UNION ALL
SELECT 1,'http://example.com/images/def' UNION ALL
SELECT 1,'http://example.com/images/ghi' UNION ALL
SELECT 2,'http://example.com/images/abc' UNION ALL
SELECT 2,'http://example.com/images/def' UNION ALL
SELECT 3,'http://example.com/images/qrz' UNION ALL
SELECT 3,'http://example.com/images/rzq' UNION ALL
SELECT 3,'http://example.com/images/zqr' UNION ALL
SELECT 3,'http://example.com/images/qqq' UNION ALL
SELECT 3,'http://example.com/images/rrr' UNION ALL
SELECT 3,'http://example.com/images/zzz'
;

查询 1:

SELECT s1.product_ID
  , max( CASE WHEN s1.rn = 1 THEN s1.url END ) AS Image1
  , max( CASE WHEN s1.rn = 2 THEN s1.url END ) AS Image2
  , max( CASE WHEN s1.rn = 3 THEN s1.url END ) AS Image3
FROM (
  SELECT t1.product_ID, t1.url
    , @rn:=CASE WHEN @pid=product_ID THEN @rn+1 ELSE 1 END AS rn
    , @pid:=product_ID AS pid
  FROM images t1, ( SELECT @rn:=0,@pid:='' ) AS t
  ORDER BY t1.product_ID
) s1
GROUP BY s1.product_ID
ORDER BY s1.product_ID

Results :

| product_ID |                        Image1 |                        Image2 |                        Image3 |
|------------|-------------------------------|-------------------------------|-------------------------------|
|          1 | http://example.com/images/abc | http://example.com/images/def | http://example.com/images/ghi |
|          2 | http://example.com/images/abc | http://example.com/images/def |                        (null) |
|          3 | http://example.com/images/rrr | http://example.com/images/qqq | http://example.com/images/zqr |