mysql - 如何根据条件对MySQL中的行进行分组

Question

我有这张表:

| id | part number | serial_number | qty |
==========================================
| 1  | A           | 12345         | 1   |
| 2  | A           | 54321         | 1   |
| 3  | A           | 67890         | 1   |
| 4  | B           |               | 10  |
| 5  | B           |               | 5   |
| 6  | C           |               | 6   |
| 7  | C           |               | 3   |

我想像这样对行进行分组:

| id | part number | serial_number | qty |
==========================================
| 1  | A           | 12345         | 1   |
| 2  | A           | 54321         | 1   |
| 3  | A           | 67890         | 1   |
| 4  | B           |               | 10  |
| 6  | C           |               | 6   |

如何做到这一点？这可能吗？

我想按没有序列号的零件号对行进行分组。因此，如果我有 4 行具有相同部件号但没有序列号，则它只显示 1 行。

Answer 1

这看起来您只想通过部件号 和serial_number 的组合进行聚合，取最大数量:

SELECT
    MIN(t1.id) AS id, t1.part_number, t1.serial_number, t1.qty
FROM yourTable t1
INNER JOIN
(
    SELECT part_number, serial_number, MAX(qty) AS qty
    FROM yourTable
    GROUP BY part_number, serial_number
) t2
    ON t1.part_number = t2.part_number AND
       (t1.serial_number = t2.serial_number OR
        t1.serial_number IS NULL AND t2.serial_number IS NULL) AND
       t1.qty = t2.qty
GROUP BY
    t1.part_number,
    t1.serial_number,
    t1.qty
ORDER BY
    MIN(t1.id);

Demo

请注意，null 是在 GROUP BY 操作中构成组成员的合法值。