SQL多表Join产生重复记录，如何合并语言字段至单个单元格？

Hey there! Looks like you're hitting that classic snag where joining tables leads to duplicate rows—one for each language entry tied to your main record. The good news is this is a super common problem, and we can easily aggregate those language values into a single cell per main record using database-specific string aggregation functions. Let's break this down.

Core Idea #

Instead of returning a separate row for each language, we'll group the results by your main table's unique identifier(s) and use a function to concatenate all related language values into one string.

Solutions by Database #

Below are examples tailored to popular databases—pick the one that matches your setup:

1. MySQL/MariaDB #

Use GROUP_CONCAT to combine values:

SELECT 
    main.id,
    main.name,
    -- Combine languages separated by commas; add DISTINCT if you need to remove duplicates
    GROUP_CONCAT(DISTINCT lang.language SEPARATOR ', ') AS languages
FROM main_table main
-- Use LEFT JOIN instead of INNER JOIN if you want to keep main records with no languages
JOIN language_table lang ON main.id = lang.main_id
GROUP BY main.id, main.name;

• Pro tip: If you hit length limits, adjust the group_concat_max_len system variable to allow longer strings.

2. PostgreSQL #

Use STRING_AGG for clean aggregation:

SELECT 
    main.id,
    main.name,
    STRING_AGG(DISTINCT lang.language, ', ') AS languages
FROM main_table main
JOIN language_table lang ON main.id = lang.main_id
GROUP BY main.id, main.name;

• You can also add an ORDER BY inside the function to sort languages: STRING_AGG(lang.language, ', ') WITHIN GROUP (ORDER BY lang.language)

3. SQL Server #

For SQL Server 2017+

Use the built-in STRING_AGG:

SELECT 
    main.id,
    main.name,
    STRING_AGG(lang.language, ', ') AS languages
FROM main_table main
JOIN language_table lang ON main.id = lang.main_id
GROUP BY main.id, main.name;

For Older SQL Server Versions

Use STUFF + FOR XML PATH as a workaround:

SELECT 
    main.id,
    main.name,
    STUFF((
        SELECT ', ' + lang.language
        FROM language_table lang
        WHERE lang.main_id = main.id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS languages
FROM main_table main
GROUP BY main.id, main.name;

4. Oracle #

Use LISTAGG to concatenate values:

SELECT 
    main.id,
    main.name,
    LISTAGG(DISTINCT lang.language, ', ') WITHIN GROUP (ORDER BY lang.language) AS languages
FROM main_table main
JOIN language_table lang ON main.id = lang.main_id
GROUP BY main.id, main.name;

• The ORDER BY clause inside LISTAGG lets you sort the languages alphabetically or in any order you need.

Key Notes #

• Include all non-aggregated fields in GROUP BY: Most databases require you to group by every field in your SELECT that isn't being aggregated (like main.id and main.name here).
• Handle missing languages: Use LEFT JOIN instead of INNER JOIN if you want to retain main records that have no associated languages. You can use COALESCE to replace NULL with an empty string: COALESCE(GROUP_CONCAT(...), '') AS languages.
• Remove duplicates: Add DISTINCT inside the aggregation function if the same language might appear multiple times for a single main record.

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