SQL多行合并求助：同groupid行合并并填充缺失值

Hey there! I’ve run into this exact scenario plenty of times—dealing with partial records that need to be stitched together by a group ID. Let’s break down how to solve this across different SQL environments.

Core Approach: Use Aggregation Functions That Ignore NULLs #

The key insight here is that most SQL aggregation functions (like MAX() or MIN()) automatically ignore NULL values. For each column in your table, if you group by groupid and apply MAX() to the column, it will pick the non-null value (if there’s only one) from all rows in the group. If multiple non-null values exist for a column in the same group, MAX() will return the largest one (lexicographical order for strings, numerical order for numbers)—so make sure your data doesn’t have conflicting values here unless that’s the behavior you want.

Basic Example #

Suppose your table is named partial_records with columns groupid, name, email, phone. Here’s the query:

SELECT
  groupid,
  MAX(name) AS name,
  MAX(email) AS email,
  MAX(phone) AS phone
  -- Add all other columns you need to merge here
FROM partial_records
GROUP BY groupid;

Let’s test this with sample data:

The query will return:

Handling Lots of Columns? Use Dynamic SQL #

If your table has dozens of columns and you don’t want to type each one manually, dynamic SQL can generate the query for you. Here’s how to do this in MySQL:

-- Step 1: Generate the list of columns with MAX() aggregation
SET @column_list = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(', column_name, ') AS ', column_name))
INTO @column_list
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'partial_records' 
  AND column_name != 'groupid'; -- Exclude the group column

-- Step 2: Build and execute the full query
SET @full_query = CONCAT('SELECT groupid, ', @column_list, ' FROM partial_records GROUP BY groupid');

PREPARE merged_query FROM @full_query;
EXECUTE merged_query;
DEALLOCATE PREPARE merged_query;

For PostgreSQL, the dynamic SQL would look like this:

DO $$
DECLARE
  column_list TEXT;
BEGIN
  SELECT string_agg(DISTINCT 'MAX(' || quote_ident(column_name) || ') AS ' || quote_ident(column_name), ', ')
  INTO column_list
  FROM information_schema.columns
  WHERE table_name = 'partial_records'
    AND column_name != 'groupid';

  EXECUTE 'SELECT groupid, ' || column_list || ' FROM partial_records GROUP BY groupid';
END $$;

Notes to Keep in Mind #

• Conflicting Values: If a column has multiple different non-null values in the same group, MAX() will pick the largest one. If this isn’t acceptable, you’ll need to define a priority (e.g., most recent record) and use window functions like ROW_NUMBER() to filter before aggregating.
• Boolean Columns: For boolean types, use BOOL_OR() (PostgreSQL) or MAX(CAST(boolean_col AS INT)) (MySQL/SQL Server) to retain the TRUE value if it exists.
• SQL Server: You can use dynamic SQL similar to MySQL, or leverage STRING_AGG to build the column list.

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