在 MySQL 中将记录展平为列 [英] Flatten records into columns in MySQL

问题描述

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

I want to flatten multiple records from a table as distinct columns in the result set. For ex:

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 |
+-------------+-------------------------------+

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

I want the result set to have three columns something like this:

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

如果我们没有三行，我希望最后一列为空.例如-

In case, we do not have three rows, I want the last columns to be null. For ex-

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 行.

In case of more than 3 rows, we can safely ignore more than 3 rows.

任何帮助将不胜感激.

推荐答案

相关子查询可能是最简单的方法:

A correlated subquery might be the simplest approach:

select p.product_id,
       (select i.url
        from images i
        where i.product_id = p.product_id
        order by i.image_id
        limit 1 offset 0
       ) as url1,
       (select i.url
        from images i
        where i.product_id = p.product_id
        order by i.image_id
        limit 1 offset 1
       ) as url2,
       (select i.url
        from images i
        where i.product_id = p.product_id
        order by i.image_id
        limit 1 offset 2
       ) as url3
from products p;

这里假设您有一个名为 products 的表.如果没有，您始终可以使用 (select distinct product_id from images) p.

This assumes you have a table called products. If not, you can always use (select distinct product_id from images) p.

这还假设您在 images 中有一个图像 ID 列.如果没有，您可以使用 order by url.

This also assumes you have an image id column in images. If not, you can use order by url.

如果您不想使用 products 表，您可以这样做:

If you don't want to use the products table, you can do:

select p.product_id,
       (select i.url
        from images i
        where i.product_id = p.product_id
        order by i.image_id
        limit 1 offset 0
       ) as url1,
       (select i.url
        from images i
        where i.product_id = p.product_id
        order by i.image_id
        limit 1 offset 1
       ) as url2,
       (select i.url
        from images i
        where i.product_id = p.product_id
        order by i.image_id
        limit 1 offset 2
       ) as url3
from (select distinct product_id from images) p;

这篇关于在 MySQL 中将记录展平为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！