当SELECT语句中其他字段重复时合并指定唯一字段的SQL咨询

Got it, let's tackle this problem step by step. First, let's clarify your core requirement: you want to merge the table2.name values (which are unique) whenever all other selected fields (field1 to field6) are duplicated across rows.

First, let's fix the obvious issue in your original SQL snippet—you're joining table2 twice without aliases, which causes ambiguity (the condition table2.field8 = table2.id doesn't make sense for the same table instance). Let's use aliases to clean that up first.

Step 1: Fix the Join Structure (With Aliases) #

Let's rewrite your base query to avoid table name confusion. If you intended to join table2 twice (e.g., for a self-reference), we'll use distinct aliases; if it was a typo, adjust accordingly:

SELECT 
  t1.field1, t1.field2, t1.field3, t1.field4, t1.field5, t1.field6,
  t2.name
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.table1_id
-- If you need a second join to table2, use a unique alias like this:
-- INNER JOIN table2 t3 ON t3.field8 = t2.id
WHERE 
  t1.id IN (
    SELECT table1_id FROM table2_table1 
    WHERE field8 IN (SELECT field8 FROM table3 WHERE field9 = 1234)
  )
  AND t1.disabled = 0

Step 2: Group and Aggregate to Merge Unique Names #

The core solution is to group your results by all fields that should be considered "duplicated" (field1 to field6), then use a string aggregation function to combine the unique name values. The exact function depends on your database system:

For MySQL/MariaDB: Use GROUP_CONCAT #

SELECT 
  t1.field1, t1.field2, t1.field3, t1.field4, t1.field5, t1.field6,
  GROUP_CONCAT(t2.name SEPARATOR ', ') AS merged_names
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.table1_id
WHERE 
  t1.id IN (
    SELECT table1_id FROM table2_table1 
    WHERE field8 IN (SELECT field8 FROM table3 WHERE field9 = 1234)
  )
  AND t1.disabled = 0
GROUP BY t1.field1, t1.field2, t1.field3, t1.field4, t1.field5, t1.field6

For PostgreSQL: Use STRING_AGG #

SELECT 
  t1.field1, t1.field2, t1.field3, t1.field4, t1.field5, t1.field6,
  STRING_AGG(t2.name, ', ') AS merged_names
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.table1_id
WHERE 
  t1.id IN (
    SELECT table1_id FROM table2_table1 
    WHERE field8 IN (SELECT field8 FROM table3 WHERE field9 = 1234)
  )
  AND t1.disabled = 0
GROUP BY t1.field1, t1.field2, t1.field3, t1.field4, t1.field5, t1.field6

For SQL Server: #

2017+ Version (Simpler STRING_AGG)

SELECT 
  t1.field1, t1.field2, t1.field3, t1.field4, t1.field5, t1.field6,
  STRING_AGG(t2.name, ', ') AS merged_names
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.table1_id
WHERE 
  t1.id IN (
    SELECT table1_id FROM table2_table1 
    WHERE field8 IN (SELECT field8 FROM table3 WHERE field9 = 1234)
  )
  AND t1.disabled = 0
GROUP BY t1.field1, t1.field2, t1.field3, t1.field4, t1.field5, t1.field6

Older Versions (STUFF + FOR XML PATH)

SELECT 
  t1.field1, t1.field2, t1.field3, t1.field4, t1.field5, t1.field6,
  STUFF(
    (SELECT ', ' + t2.name 
     FROM table2 t2 
     WHERE t2.table1_id = t1.id
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 2, ''
  ) AS merged_names
FROM table1 t1
WHERE 
  t1.id IN (
    SELECT table1_id FROM table2_table1 
    WHERE field8 IN (SELECT field8 FROM table3 WHERE field9 = 1234)
  )
  AND t1.disabled = 0
GROUP BY t1.field1, t1.field2, t1.field3, t1.field4, t1.field5, t1.field6

For Oracle: Use LISTAGG #

SELECT 
  t1.field1, t1.field2, t1.field3, t1.field4, t1.field5, t1.field6,
  LISTAGG(t2.name, ', ') WITHIN GROUP (ORDER BY t2.name) AS merged_names
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.table1_id
WHERE 
  t1.id IN (
    SELECT table1_id FROM table2_table1 
    WHERE field8 IN (SELECT field8 FROM table3 WHERE field9 = 1234)
  )
  AND t1.disabled = 0
GROUP BY t1.field1, t1.field2, t1.field3, t1.field4, t1.field5, t1.field6

Key Notes: #

• Grouping Logic: We group by all fields (field1 to field6) that define a "duplicate" row. This ensures only rows with identical values in these fields will have their name values merged.
• Unique Names: Since you stated table2.name is always unique, you don't need to add DISTINCT to the aggregation function—though you can include it if you ever need to handle accidental duplicates.
• Alias Best Practice: Always use aliases when joining the same table multiple times (like your original duplicate table2 join) to avoid ambiguity and errors.

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