問題描述

使用 MySQL，我可以執行以下操作:

Using MySQL, I can do something like:

SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;

我的輸出:

shopping
fishing
coding

但我只想要 1 行，1 列:

but instead I just want 1 row, 1 col:

預期輸出:

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.

我在 上尋找了一個函數MySQL Doc 它看起來不像 CONCAT 或 CONCAT_WS 函數接受結果集.

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?

推薦答案

您可以使用 GROUP_CONCAT:

You can use GROUP_CONCAT:

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

正如 Ludwig 在他的評論中所述， 您可以添加 DISTINCT 運算符以避免重復:

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;

正如 Jan 在他們的評論中所述， 您還可以在使用 ORDER BY 內爆之前對值進行排序:

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;

正如 Dag 在他的評論中所述， 結果有 1024 字節的限制.要解決此問題，請在查詢之前運行此查詢:

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;

當然，您可以根據需要更改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);

這篇關于我可以將多個 MySQL 行連接到一個字段中嗎?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！