如何使用MySQL JOIN实现无笛卡尔积的分组数据聚合

Yes, you can pull off this aggregation in MySQL—though you’ll need to pair JOIN logic with window functions (or manual ranking for older MySQL versions) to avoid unwanted Cartesian products. Let’s walk through the solution step by step.

Original Dataset #

First, let’s formalize the sample data for clarity:

CREATE TABLE your_table (
    group_id INT,
    a_id INT,
    b_id INT,
    c_id INT,
    d_id INT
);

INSERT INTO your_table VALUES
(1, 1, null, null, null),
(1, null, 2, null, null),
(1, null, null, 3, null),
(1, null, null, null, 4),
(1, null, null, null, 5),
(2, 11, null, null, null),
(2, null, 12, null, null),
(2, null, null, 13, null);

Expected Result #

We want to group rows by group_id without creating Cartesian products, resulting in this output:

Solution Query #

The core idea is to assign a "batch number" to each non-null field entry within its group_id, then aggregate by group_id and this batch number. Here’s the working query:

SELECT
    group_id,
    MAX(a_id) AS a_id,
    MAX(b_id) AS b_id,
    MAX(c_id) AS c_id,
    MAX(d_id) AS d_id
FROM (
    SELECT 
        group_id,
        a_id, b_id, c_id, d_id,
        -- Assign a sequential number to each entry of the same field type in the group
        ROW_NUMBER() OVER (
            PARTITION BY group_id, 
            CASE
                WHEN a_id IS NOT NULL THEN 'a'
                WHEN b_id IS NOT NULL THEN 'b'
                WHEN c_id IS NOT NULL THEN 'c'
                WHEN d_id IS NOT NULL THEN 'd'
            END 
            ORDER BY (SELECT NULL) -- Order doesn't matter if you don't care about entry sequence
        ) AS batch_num
    FROM your_table
) AS ranked_rows
GROUP BY group_id, batch_num
ORDER BY group_id, batch_num;

How It Avoids Cartesian Products #

1. Ranking Rows: The inner query uses ROW_NUMBER() to tag each row with a batch_num. For example:
   • The first non-null a_id, b_id, c_id, and d_id in group_id=1 all get batch_num=1
   • The second non-null d_id in group_id=1 gets batch_num=2
     This ensures we only group entries that are "matched" by their occurrence order.
2. Aggregation: When grouping by group_id and batch_num, MAX() pulls the non-null value for each field in the batch. For group_id=1:
   • batch_num=1 combines the first set of non-null values into one row
   • batch_num=2 only includes the extra d_id=5, leaving other fields null
3. No Cartesian Chaos: By grouping on batches instead of joining all non-null entries directly, we avoid multiplying rows unnecessarily.

Alternative for MySQL 5.7 or Earlier #

If you’re using a version without window functions, you can manually assign batch numbers with user-defined variables:

SELECT
    group_id,
    MAX(a_id) AS a_id,
    MAX(b_id) AS b_id,
    MAX(c_id) AS c_id,
    MAX(d_id) AS d_id
FROM (
    SELECT 
        t.*,
        @rn := CASE
            WHEN @prev_group = group_id AND @prev_type = field_type THEN @rn + 1
            ELSE 1
        END AS batch_num,
        @prev_group := group_id,
        @prev_type := field_type
    FROM (
        SELECT 
            group_id,
            a_id, b_id, c_id, d_id,
            CASE
                WHEN a_id IS NOT NULL THEN 'a'
                WHEN b_id IS NOT NULL THEN 'b'
                WHEN c_id IS NOT NULL THEN 'c'
                WHEN d_id IS NOT NULL THEN 'd'
            END AS field_type
        FROM your_table
        ORDER BY group_id, field_type
    ) AS t
    CROSS JOIN (SELECT @prev_group := -1, @prev_type := '', @rn := 0) AS vars
) AS ranked_rows
GROUP BY group_id, batch_num
ORDER BY group_id, batch_num;

This uses variables to track and increment batch numbers for each field type within a group, achieving the same result as the window function version.

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