SQL内连接返回重复记录，需将关联多技术字段合并为数组

Hey there! I get it—your current join is returning duplicate rows from the Main table whenever there are multiple matching technique records, and you want to bundle those technique values into an array-like structure instead. Let's fix this, and I'll cover the most common database systems since the syntax varies a bit:

MySQL/MariaDB #

Use GROUP_CONCAT to stitch together technique values into a comma-separated string (which acts like a simple array). You'll also need to group by all non-aggregated columns from the Main table:

SELECT 
  Main.id, 
  Main.created_min, 
  Main.local_photo_name, 
  Main.description, 
  Main.creator, 
  Main.title, 
  Main.museum,
  GROUP_CONCAT(technique.technique SEPARATOR ', ') AS techniques
FROM Main 
INNER JOIN technique ON technique.kleding_id = Main.id 
GROUP BY Main.id, Main.created_min, Main.local_photo_name, Main.description, Main.creator, Main.title, Main.museum
LIMIT 5;

• You can customize the separator (e.g., use '|' instead of ', ') by changing the SEPARATOR value.
• Add DISTINCT inside GROUP_CONCAT if you want to remove duplicate technique values for the same Main.id.

PostgreSQL #

PostgreSQL has native array support, so use ARRAY_AGG to generate a proper array type directly:

SELECT 
  Main.id, 
  Main.created_min, 
  Main.local_photo_name, 
  Main.description, 
  Main.creator, 
  Main.title, 
  Main.museum,
  ARRAY_AGG(technique.technique) AS techniques
FROM Main 
INNER JOIN technique ON technique.kleding_id = Main.id 
GROUP BY Main.id, Main.created_min, Main.local_photo_name, Main.description, Main.creator, Main.title, Main.museum
LIMIT 5;

• Add DISTINCT to deduplicate values: ARRAY_AGG(DISTINCT technique.technique)
• Sort the array values: ARRAY_AGG(technique.technique ORDER BY technique.technique)

SQL Server #

Use STRING_AGG for comma-separated strings (available in SQL Server 2017+), and note that SQL Server uses OFFSET/FETCH instead of LIMIT:

SELECT 
  Main.id, 
  Main.created_min, 
  Main.local_photo_name, 
  Main.description, 
  Main.creator, 
  Main.title, 
  Main.museum,
  STRING_AGG(technique.technique, ', ') AS techniques
FROM Main 
INNER JOIN technique ON technique.kleding_id = Main.id 
GROUP BY Main.id, Main.created_min, Main.local_photo_name, Main.description, Main.creator, Main.title, Main.museum
ORDER BY Main.id
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

• For SQL Server 2022+, you can use ARRAY instead of STRING_AGG to get a native array type.

Quick Notes #

• If you want to keep Main records that have no matching technique entries, swap INNER JOIN with LEFT JOIN. You can use COALESCE to replace NULL results with an empty string/array (e.g., COALESCE(GROUP_CONCAT(...), '') for MySQL).
• Always make sure your GROUP BY clause includes every non-aggregated column from the Main table—this avoids syntax errors caused by strict SQL modes.

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