Can I concatenate multiple MySQL rows into one field?
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?
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 her/his 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
)
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';
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...
链接地址: http://www.djcxy.com/p/19768.html上一篇: SQLite中的“插入如果不存在”语句