mysql - 在后续行上显示空结果

Question

我在 MySQL 中有下表

+----+------------+-----------+-------------+-------------------+
| id | product_id |  status   | transaction |      action       |
+----+------------+-----------+-------------+-------------------+
|  1 |          2 | Pending   |             | sent              |
|  2 |          2 | Complete  |           1 | received          |
|  2 |          2 | Complete  |           1 | some other action |
+----+------------+-----------+-------------+-------------------+

我只想在完成状态后显示一次交易。所以结果集将是

+----+------------+-----------+-------------+-------------------+
| id | product_id |  status   | transaction |      action       |
+----+------------+-----------+-------------+-------------------+
|  1 |          2 | Pending   |             | sent              |
|  2 |          2 | Complete  |           1 | received          |
|  2 |          2 | Complete  |             | some other action |
+----+------------+-----------+-------------+-------------------+

Answer 1

在 MySQL 中执行此操作的最简单方法是使用变量。查询看起来像这样:

select id, product_id, status,
       if(id <> @previd and @prevstatus <> status and status = 'Complete', transaction, NULL) as transaction,
       action,
       @previd := id, @prevstatus := status
from table t cross join
     (select @previd := -1, @prevstatus := '') const
order by id, product_id, status;

一个问题是该表没有用于稳定排序的列，因此这是最佳猜测。