我能将多个 MySQL 行连接成一个字段吗？

我的意图是在不使用 group_concat() 函数的情况下应用字符串连接：

Set @concatHobbies = '';
SELECT TRIM(LEADING ', ' FROM T.hobbies ) FROM 
(
   select 
   Id, @concatHobbies := concat_ws(', ',@concatHobbies,hobbies) as hobbies
   from peoples_hobbies
)T
Order by Id DESC
LIMIT 1

在这里

   select 
   Id, @concatHobbies := concat_ws(', ',@concatHobbies,hobbies) as hobbies
   from peoples_hobbies

将返回

  Id    hobbies
  1     , shopping
  2     , shopping, fishing
  3     , shopping, fishing, coding

现在我们期望的结果在第三个位置。所以我将使用最后一行

  Order by Id DESC 
  LIMIT 1
  

我还要从我的字符串中去掉第一个逗号和空格“，”。

 TRIM(LEADING ', ' FROM T.hobbies )

在 MySql 中，我们有两种方法来连接列。

select concat(hobbies) as `Hobbies` from people_hobbies where 1

或者

select group_concat(hobbies) as `Hobbies` from people_hobbies where 1

很晚了，但对于那些正在搜索“使用数据透视表将多个MySQL行连接成一个字段”的人来说，这将是有用的：）

查询：

SELECT pm.id, pm.name, GROUP_CONCAT(c.name) as channel_names
FROM payment_methods pm
LEFT JOIN payment_methods_channels_pivot pmcp ON pmcp.payment_method_id = pm.id
LEFT JOIN channels c ON c.id = pmcp.channel_id
GROUP BY pm.id

Tables

payment_methods 
  id  | name
  1   | PayPal

channels
  id  | name
  1   | Google
  2   | Faceook

payment_methods_channels_pivot
   payment_method_id | channel_id
   1                 |  1
   1                 |  2

输出:

（注：该内容为HTML代码，无需翻译）

在这种情况下，另一个有趣的例子是 -

以下是示例表people_hobbies的结构 -

DESCRIBE people_hobbies;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int unsigned | NO   | PRI | NULL    | auto_increment |
| ppl_id  | int unsigned | YES  | MUL | NULL    |                |
| name    | varchar(200) | YES  |     | NULL    |                |
| hby_id  | int unsigned | YES  | MUL | NULL    |                |
| hobbies | varchar(50)  | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

表格的填充方式如下 -

SELECT * FROM people_hobbies;
+----+--------+-----------------+--------+-----------+
| id | ppl_id | name            | hby_id | hobbies   |
+----+--------+-----------------+--------+-----------+
|  1 |      1 | Shriya Jain     |      1 | reading   |
|  2 |      4 | Shirley Setia   |      4 | coding    |
|  3 |      2 | Varsha Tripathi |      7 | gardening |
|  4 |      3 | Diya Ghosh      |      2 | fishing   |
|  5 |      4 | Shirley Setia   |      3 | gaming    |
|  6 |      1 | Shriya Jain     |      6 | cycling   |
|  7 |      2 | Varsha Tripathi |      1 | reading   |
|  8 |      3 | Diya Ghosh      |      5 | shopping  |
|  9 |      3 | Diya Ghosh      |      4 | coding    |
| 10 |      4 | Shirley Setia   |      1 | reading   |
| 11 |      1 | Shriya Jain     |      4 | coding    |
| 12 |      1 | Shriya Jain     |      3 | gaming    |
| 13 |      4 | Shirley Setia   |      2 | fishing   |
| 14 |      4 | Shirley Setia   |      7 | gardening |
| 15 |      2 | Varsha Tripathi |      3 | gaming    |
| 16 |      2 | Varsha Tripathi |      2 | fishing   |
| 17 |      1 | Shriya Jain     |      5 | shopping  |
| 18 |      1 | Shriya Jain     |      7 | gardening |
| 19 |      3 | Diya Ghosh      |      1 | reading   |
| 20 |      4 | Shirley Setia   |      5 | shopping  |
+----+--------+-----------------+--------+-----------+

现在，一个名为 hobby_list 的表被生成，其中包含所有人的列表以及每个人爱好的列表，每个爱好占据一行 -

CREATE TABLE hobby_list AS
    -> SELECT ppl_id, name,
    -> GROUP_CONCAT(hobbies ORDER BY hby_id SEPARATOR "\n")
    -> AS hobbies
    -> FROM people_hobbies
    -> GROUP BY ppl_id
    -> ORDER BY ppl_id;

SELECT * FROM hobby_list;

使用GROUP_CONCAT：

SELECT GROUP_CONCAT(hobbies) FROM peoples_hobbies WHERE person_id = 5;

在SQL Server中，使用string_agg将行字段值转换为列：

select string_agg(field1, ', ') a FROM mytable 

or

select string_agg(field1, ', ') within group (order by field1 dsc) a FROM mytable group by field2