将两张桌子合并为一张桌子 [英] Combine two tables into one table

问题描述

我需要将用户表与所有用户图像排成一行，就像这样

I need to join user table with all user images in one row, like this

table user
+----+---------------+
|id  |   name        | 
+----+---------------+
| 1  | Mike          | 
| 2  | Jerry         |
| .. | .....         |  
+----+---------------+

table image
+------+---------+---------+
| id   | user_id |  img    |
+------+---------+---------+
|  1   |  1      | img_1   |
|  2   |  1      | img_2   |
|  3   |  1      | img_3   |
|  4   |  2      | img_4   |
|  ..  |  ....   | .....   |
+------+---------+---------+

我需要生成这样的SQL结果

I need to generate SQL results like this

+------+--------+----------+----------+----------+
| id   | name   |  img1    |  img2    |  img3    |
+------+--------+----------+----------+----------+
|  1   | Mike   |  img_1   |  img_2   |  img_3   |
+------+--------+----------+----------+----------+
|  ... | ....   |  ....    |  ....    |  ....    |
+------+--------+----------+----------+----------+

推荐答案

mysql本身不支持，但是您可以使用数据透视表来精确地创建结果，但是如果可能的话，需要进行一些硬编码.

That's not natively supported in mysql, however you could use a pivot to create exactly your result, however that requires some hardcoding I'd avoid if possible.

您的任务的简单解决方案可以使用GROUP_CONCAT()，它会产生类似结果

A simple solution for your task could be using GROUP_CONCAT(), which would produce a resultset like

+------+--------+----------+----------+----------+
| id   | name   |  images                        |
+------+--------+----------+----------+----------+
|  1   | Mike   |  img_1;img_2;img_3             |
+------+--------+----------+----------+----------+
|  ... | ....   |                                |
+------+--------+----------+----------+----------+

如果这足够好，您可以使用

If that's good enough, you can achieve that with

SELECT a.id, a.name, GROUP_CONCAT(b.img) images
FROM user a 
    INNER JOIN image b ON a.id = b.user_id
GROUP BY b.user_id;

这篇关于将两张桌子合并为一张桌子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！