SQL Group By忽略组内共享字段的实现方案咨询

Alright, let's work through this problem. From your sample data and desired output, I can see you want to roll up product variants into one record per group (grouped by variantId here), with specific rules for which field values to keep or set to null. Let's break down what we need to do first:

Key Requirements (from your example): #

• Group variants by variantId (each ID maps to a unique product line)
• Always set id and variantId to null in the aggregated record
• Keep the name value (since all variants in a group share the same name)
• For other fields (like size, color), only keep the value if every variant in the group has the exact same non-null value — otherwise, set the field to null

Static SQL Solution (for known fields) #

If you know all the fields in your table upfront, you can write a straightforward query with conditional logic for each field:

SELECT
  NULL AS id,
  MAX(name) AS name, -- MAX works here because all group values are identical
  NULL AS variantId,
  -- Keep size only if all entries have the same non-null value
  CASE
    WHEN COUNT(DISTINCT size) = 1 AND COUNT(size) = COUNT(*) THEN MAX(size)
    ELSE NULL
  END AS size,
  -- Keep color only if all entries have the same non-null value
  CASE
    WHEN COUNT(DISTINCT color) = 1 AND COUNT(color) = COUNT(*) THEN MAX(color)
    ELSE NULL
  END AS color
FROM products
GROUP BY variantId;

How this works: #

• COUNT(DISTINCT field) = 1: Checks if all non-null values in the group are identical
• COUNT(field) = COUNT(*): Ensures there are no null values for that field in the group
• When both conditions are true, we keep the shared value; otherwise, we set the field to null.

Dynamic SQL Solution (for unknown/changeable fields) #

Since you mentioned you can't predict the number or specific shared fields, we can use dynamic SQL to automatically handle all fields in your table without manual updates:

SET @sql = NULL;

-- Generate conditional logic for all fields except id, name, variantId
SELECT GROUP_CONCAT(
  DISTINCT CONCAT(
    'CASE WHEN COUNT(DISTINCT ', column_name, ') = 1 AND COUNT(', column_name, ') = COUNT(*) THEN MAX(', column_name, ') ELSE NULL END AS ', column_name
  )
) INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = DATABASE()
  AND table_name = 'products'
  AND column_name NOT IN ('id', 'name', 'variantId');

-- Build the full query
SET @sql = CONCAT(
  'SELECT NULL AS id, MAX(name) AS name, NULL AS variantId, ', @sql, ' FROM products GROUP BY variantId'
);

-- Execute the dynamic query
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

How this works: #

1. It queries the INFORMATION_SCHEMA.COLUMNS to get all fields in your products table, excluding the ones we handle manually (id, name, variantId).
2. For each field, it generates a CASE statement that checks if the field is fully shared (no nulls, all values identical) and returns the value if true, else null.
3. It combines these into a complete SQL query and executes it dynamically.

This solution will automatically adapt if you add or remove fields from your products table later — no need to rewrite the query!

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