MySQL 将具有相同 ID 的多行值复制到新列中? [英] MySQL copy multiple rows values with same ID into new columns?

问题描述

ItemID    |    File                    

<小时>

1           /storage/somefile1.jpg
1           /storage/somefile2.jpg
1           /storage/somefile3.jpg
1           /storage/somefile5.jpg  

2           /storage/somerandomfile.jpg
2           /storage/anotherrandomfile.jpg
2           /storage/yetanotherrandomfile.jpg
2           /storage/somefile.jpg  

我想为每个文件创建一个新列，而不是每个文件有 1 行.如:

I am wanting to create a new column for each file rather than having 1 row per file. Such as:

ItemID    |    File                     |    File2                        | File3 etc...

<小时>

1           /storage/somefile1.jpg      |   /storage/somefile2.jpg        |   /storage/..


2           /storage/somerandomfile.jpg |  /storage/anotherrandomfile.jpg | /storage/..

有什么办法可以通过查询自动执行此操作吗?

Is there any way to automate this with a query?

推荐答案

在看到您的评论并且您只是想将其制作成 CSV 后，您可以执行以下操作:

After seeing your comment and that you're just trying to make a CSV out of it, you can do something like this:

<?php
$query = $db->query('SELECT ItemID, GROUP_CONCAT(File SEPARATOR \'|$|\') AS Files FROM Table GROUP BY ItemID');
// Use a string that cant appear as part of the filename as the separator
$fh = fopen('items.csv', 'w');
foreach ($query->fetchAll(PDO::FETCH_ASSOC) as $row) {
    $files = explode('|$|', $row['Files']);
    fputcsv($fh, array_merge(array($row['ItemID']), $files));
}
fclose($fh);

这篇关于MySQL 将具有相同 ID 的多行值复制到新列中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！