多表关联查询需求：实现每人一行展示关联群组信息

Got it, let's solve this problem: you need to return a single row for each person, with all their linked groups displayed in a single column. Based on your table structure (Persons, Groups, and Link), here are solutions tailored to different database systems:

SQL Server (2017+) & Azure SQL #

Use the built-in STRING_AGG function—it's clean and straightforward for string aggregation:

SELECT 
    p.uniqueid AS person_id,
    p.email,
    STRING_AGG(g.title, ', ') AS associated_groups
FROM persons p
LEFT JOIN link l ON p.uniqueid = l.personid  -- Note: I assumed your Link table has a `personid` column linking to Persons; adjust if your actual field name differs
LEFT JOIN groups g ON l.groupid = g.uniqueid
GROUP BY p.uniqueid, p.email;

• LEFT JOIN ensures people with no groups still appear in results (their associated_groups will be NULL).
• STRING_AGG concatenates group titles with a comma separator—feel free to change ', ' to any delimiter you prefer.

MySQL #

MySQL uses GROUP_CONCAT for this scenario:

SELECT 
    p.uniqueid AS person_id,
    p.email,
    GROUP_CONCAT(g.title SEPARATOR ', ') AS associated_groups
FROM persons p
LEFT JOIN link l ON p.uniqueid = l.personid
LEFT JOIN groups g ON l.groupid = g.uniqueid
GROUP BY p.uniqueid, p.email;

• By default, GROUP_CONCAT uses a comma separator, but adding SEPARATOR lets you customize it.
• If you hit length limits, you can adjust the group_concat_max_len system variable to allow longer strings.

Oracle (11gR2+) #

Oracle's LISTAGG function handles this nicely, and you can even sort the groups if needed:

SELECT 
    p.uniqueid AS person_id,
    p.email,
    LISTAGG(g.title, ', ') WITHIN GROUP (ORDER BY g.title) AS associated_groups
FROM persons p
LEFT JOIN link l ON p.uniqueid = l.personid
LEFT JOIN groups g ON l.groupid = g.uniqueid
GROUP BY p.uniqueid, p.email;

• The WITHIN GROUP (ORDER BY g.title) clause sorts the grouped alphabetically—remove it if you don't need sorting.

Older SQL Versions (e.g., SQL Server pre-2017) #

If you're stuck with a version without modern aggregation functions, use FOR XML PATH to manually concatenate strings:

SELECT 
    p.uniqueid AS person_id,
    p.email,
    STUFF(
        (SELECT ', ' + g.title 
         FROM link l
         JOIN groups g ON l.groupid = g.uniqueid
         WHERE l.personid = p.uniqueid
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
        1, 2, ''
    ) AS associated_groups
FROM persons p
GROUP BY p.uniqueid, p.email;

• STUFF removes the leading ', ' that the subquery would otherwise produce.
• Using TYPE.value('.', 'NVARCHAR(MAX)') prevents XML escaping of special characters like & or <.

Quick Note #

Double-check your Link table's schema—your sample script only mentions groupid, but you'll need a personid (or equivalent) column to link to the Persons table. Adjust the join conditions if your actual field names are different.

内容的提问来源于stack exchange，提问作者MBijen