将整个表格作为单行联接 [英] Join a whole table as single row

问题描述

假设我有2张这样的桌子:

Let's say I have 2 tables like this:

--------+-------          
| id    | name |
--------+-------
| 1     | Paul |
| 2     | Jack |
| 3     | Joe  |
--------+-------


--------+--------
| id    | Color |
--------+--------
| 1     | Blue  |
| 2     | Red   |
| 3     | Pink  |
--------+--------

我想要这样的结果:

--------+-----------------
| Paul  | Blue,Red,Pink  |
--------+-----------------

我不知道它是否取决于我可能错过的复杂查询或简单"关键字.我已经在JOIN附近搜索了这样的内容:

I don't know if it depends on a complex query or a "simple" keyword that I might have missed. I've searched near the JOIN thing like this:

SELECT * FROM main WHERE name = Paul CROSS JOIN colors WHERE 1;

但这似乎并不那么容易，而且当然不起作用.

But it doesn't seems that easy and it's of course not working.

任何提示或关键字搜索将不胜感激

Any hints or keyword to search for would be much appreciated

推荐答案

使用Cross Join和group_concat.

查询

select t1.name,group_concat(distinct t2.color separator ',') as color
from tbl1 t1,tbl2 t2
where t1.name = 'Paul'
group by t1.name;

SQL小提琴

这篇关于将整个表格作为单行联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！