MySQL比较行并获取更改

Question

除了选择和加入之外，还有点迷失，需要帮助。我有一张表，用于维护所创建产品的属性。该表中当前有 110k 行。我正在寻找一种方法来查询该数据并返回与每个产品的属性变化相关的数据。

+-----------+---------+--------+--------+--------+
| attrib_id | prod_id | height | weight | length |
+-----------+---------+--------+--------+--------+
|         1 |     120 |     20 |      3 |      5 |
|         2 |     101 |      5 |     10 |     20 |
|         3 |     101 |      5 |     10 |     20 |
|         4 |     101 |      5 |     10 |     20 |
|         5 |     120 |     20 |      3 |      5 |
|         6 |     101 |      8 |     10 |     20 |
|         7 |     120 |     20 |      3 |      5 |
|         8 |     101 |      8 |     15 |     30 |
|         9 |     101 |     16 |     15 |     20 |
|        10 |     120 |     20 |     10 |      3 |
+-----------+---------+--------+--------+--------+

当产品属性发生变化时，我希望看到类似这样的输出:

+-----------+---------+-------------+------------+------------+-------------+------------+------------+-------------+------------+------------+
| attrib_id | prod_id | orig_height | new_height | chg_height | orig_weight | new_weight | chg_weight | orig_length | new_length | chg_length |
+-----------+---------+-------------+------------+------------+-------------+------------+------------+-------------+------------+------------+
|         6 |     101 |           5 |          8 |          3 |          10 |            |            |          20 |            |            |
|        10 |     120 |          20 |            |            |           3 |         10 |          7 |           5 |          3 |         -2 |
+-----------+---------+-------------+------------+------------+-------------+------------+------------+-------------+------------+------------+

Answer 1

您的预期输出有点不正确。

您想找到最小和最大 attrib_id，然后使用聚合找到所需的值:

select attrib_id,
    prod_id,
    original_height,
    case when original_height = new_height then null else new_height end new_height,
    nullif(new_height - original_height, 0) chg_height,
    original_weight,
    case when original_weight = new_weight then null else new_weight end new_weight,
    nullif(new_weight - original_weight, 0) chg_weight,
    original_length,
    case when original_length = new_length then null else new_length end new_length,
    nullif(new_length - original_length, 0) chg_length
from (
    select t2.max_id attrib_id,
        t.prod_id,
        max(case when t.attrib_id = t2.min_id then t.height end) original_height,
        max(case when t.attrib_id = t2.max_id then t.height end) new_height,
        max(case when t.attrib_id = t2.min_id then t.weight end) original_weight,
        max(case when t.attrib_id = t2.max_id then t.weight end) new_weight,
        max(case when t.attrib_id = t2.min_id then t.length end) original_length,
        max(case when t.attrib_id = t2.max_id then t.length end) new_length
    from t
    join (
        select prod_id,
            min(attrib_id) min_id,
            max(attrib_id) max_id
        from t
        group by prod_id
        ) t2 on t.prod_id = t2.prod_id
        and t.attrib_id in (t2.min_id, t2.max_id)
    group by t.prod_id
    ) t;

Demo