Can I concatenate multiple MySQL rows into one fie

2018-12-30 23:59发布

Using MySQL, I can do something like:

SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;

My Output:

shopping
fishing
coding

but instead I just want 1 row, 1 col:

Expected Output:

shopping, fishing, coding

The reason is that I'm selecting multiple values from multiple tables, and after all the joins I've got a lot more rows than I'd like.

I've looked for a function on MySQL Doc and it doesn't look like the CONCAT or CONCAT_WS functions accept result sets, so does anyone here know how to do this?

10条回答
若你有天会懂
2楼-- · 2018-12-31 00:22

Alternate syntax to concatenate multiple, individual rows

WARNING: This post will make you hungry.

Given:

I found myself wanting to select multiple, individual rows—instead of a group—and concatenate on a certain field.

Let's say you have a table of product ids and their names and prices:

+------------+--------------------+-------+
| product_id | name               | price |
+------------+--------------------+-------+
|         13 | Double Double      |     5 |
|         14 | Neapolitan Shake   |     2 |
|         15 | Animal Style Fries |     3 |
|         16 | Root Beer          |     2 |
|         17 | Lame T-Shirt       |    15 |
+------------+--------------------+-------+

Then you have some fancy-schmancy ajax that lists these puppies off as checkboxes.

Your hungry-hippo user selects 13, 15, 16. No dessert for her today...

Find:

A way to summarize your user's order in one line, with pure mysql.

Solution:

Use GROUP_CONCAT with the the IN clause:

mysql> SELECT GROUP_CONCAT(name SEPARATOR ' + ') AS order_summary FROM product WHERE product_id IN (13, 15, 16);

Which outputs:

+------------------------------------------------+
| order_summary                                  |
+------------------------------------------------+
| Double Double + Animal Style Fries + Root Beer |
+------------------------------------------------+

Bonus Solution:

If you want the total price too, toss in SUM():

mysql> SELECT GROUP_CONCAT(name SEPARATOR ' + ') AS order_summary, SUM(price) AS total FROM product WHERE product_id IN (13, 15, 16);
+------------------------------------------------+-------+
| order_summary                                  | total |
+------------------------------------------------+-------+
| Double Double + Animal Style Fries + Root Beer |    10 |
+------------------------------------------------+-------+

PS: Apologies if you don't have an In-N-Out nearby...

查看更多
路过你的时光
3楼-- · 2018-12-31 00:23

Have a look at GROUP_CONCAT if your MySQL version (4.1) supports it. See the documentation for more details.

It would look something like:

  SELECT GROUP_CONCAT(hobbies SEPARATOR ', ') 
  FROM peoples_hobbies 
  WHERE person_id = 5 
  GROUP BY 'all';
查看更多
孤独寂梦人
4楼-- · 2018-12-31 00:23

You can change the max length of the GROUP_CONCAT value by setting the group_concat_max_len parameter.

See details in the MySQL documantation.

查看更多
梦该遗忘
5楼-- · 2018-12-31 00:26

There's a GROUP Aggregate function, GROUP_CONCAT.

查看更多
怪性笑人.
6楼-- · 2018-12-31 00:27

You can use GROUP_CONCAT:

SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id, GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies 
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
    (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
    FROM peoples_hobbies 
    GROUP BY person_id)
    AS UNSIGNED
);
查看更多
十年一品温如言
7楼-- · 2018-12-31 00:29

we have two way to concatenate columns in MySql

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

Or

select group_concat(hobbies) as `Hobbies` from people_hobbies where 1
查看更多
登录 后发表回答