mysql - 在 mariadb 中连接具有相同 id 的行？

Question

我想将 name 与相同的 pid 连接起来。

下面是我正在尝试的查询。但它给了我错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@tbl1 WHERE pid = tbl1.pid FOR XM' at line 3

SELECT *,STUFF((
            SELECT DISTINCT', ' + tbl1.name
            FROM @tbl1
            WHERE pid = tbl1.pid
            FOR XML PATH('')), 1, 2, '')

FROM (
    SELECT DISTINCT  pid
    FROM @tbl1
) tbl1
INNER JOIN tbl2 ON tbl2.pid = tbl1.pid

更新

样本数据

pid      name
1        Editor
1        Reviewer
7        EIC
7        Editor
7        Reviewer
7        Editor
19       EIC
19       Editor
19       Reviewer

需要的数据

1 Editor,Reviewer
7 EIC,Editor,Reviewer
19 EIC,Editor, Reviewer

Answer 1

您可以使用 group_concat在 mysql 中为此

select pid,group_concat(name)
from your_table
group by pid

它将返回 3 行，包括 pid 和逗号分隔的名称列表

Also note "The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet"

DEMO