Mysql 当每组的列值发生变化时 Select 并连接行

如果您使用的是MySQL 8，窗口函数会有所帮助.https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

您可以将your results除以device和test，并添加一列作为result的上一个值，然后使用结果与上一个值不同的最后一行.

以下查询在结果中创建一个新列，其中包含previous_value

SELECT 
    assessment_id,
    device_id,
    test,
    result,
    LAG (result) over w as `previous_value`,
    LAG (assessment_id) over w as `previous_assessment_id`
FROM assessments join results using(assessment_id)
WINDOW w AS (PARTITION BY device_id, test ORDER BY assessment_id)

得出结果:

+---------------+-----------+------+--------+----------------+------------------------+
| assessment_id | device_id | test | result | previous_value | previous_assessment_id |
+---------------+-----------+------+--------+----------------+------------------------+
|             1 |         1 | A    | PASS   | NULL           |                   NULL |
|             3 |         1 | A    | FAIL   | PASS           |                      1 |
|             2 |         2 | B    | FAIL   | NULL           |                   NULL |
|             4 |         2 | B    | PASS   | FAIL           |                      2 |
|             5 |         2 | B    | PASS   | PASS           |                      4 |
+---------------+-----------+------+--------+----------------+------------------------+

这是战斗的重要部分.现在，我们需要获取该结果，并找到每个设备/测试对具有最高判断id的行，其中result！=上一个_值.

该窗口是在GROUP BY、ORDER BY甚至HAVING之后计算的，因此在该查询(我已经想到)中可以做的事情不多，可以将其缩小到每个设备/测试对的最近条目.因此，上述内容必须是一个子查询才能获得最终结果.

注意:我假设如果结果从未改变，那么您希望显示第一次记录结果的时间.换句话说，您希望以previous_value = NULL作为一个过渡来计算结果.

以下是一个查询，列出了设备/测试对的测试结果更改的所有时间:

SELECT * FROM 
(SELECT 
    assessment_id,
    device_id,
    test,
    result,
    LAG (result) over w as `previous_value`
FROM assessments join results using(assessment_id)
WINDOW w AS (PARTITION BY `device_id`, `test` ORDER BY `assessment_id`)
) AS t
WHERE result != `previous_value` OR `previous_value` IS NULL

得到结果(我省略了previous_assesssment_id个，其他的留了一些空白):

+---------------+-----------+------+--------+----------------+
| assessment_id | device_id | test | result | previous_value |
+---------------+-----------+------+--------+----------------+
|             1 |         1 | A    | PASS   | NULL           |
|             3 |         1 | A    | FAIL   | PASS           |
|             2 |         2 | B    | FAIL   | NULL           |
|             4 |         2 | B    | PASS   | FAIL           |
+---------------+-----------+------+--------+----------------+

EDIT

这就是问题的答案.如果第一次设置值不重要，只需删除WHERE子句的OR部分.这个答案的其余部分是因为我说服自己，问题是得到价值翻转的最近时间.我把它留在这里，但只是出于兴趣.

Carrying On

这是结果与之前不同的所有时间，加上第一次记录结果.差不多了.

此时，在外部查询中添加另一个窗口来聚合上面的行并识别正确的行是很诱人的.但至少在MySQL 8中，不支持嵌套窗口.

但是考虑到这个结果，我们可以使用MAX()和GROUP BY创建一个查询，该查询给出了我们最终想要的所有行的判断ID:

SELECT MAX(assessment_id)
FROM (
    SELECT 
       assessment_id,
       device_id,
       test,
       result,
       LAG (result) over w as `previous_value`,
       LAG (assessment_id) over w as `previous_assessment_id`
    FROM assessments join results using(assessment_id)
    WINDOW w AS (PARTITION BY device_id, test ORDER BY assessment_id)
) AS t

where result != previous_value OR previous_value IS NULL
GROUP BY device_id, test

这将产生:

+--------------------+
| MAX(assessment_id) |
+--------------------+
|                  3 |
|                  4 |
+--------------------+

现在我们确切地知道需要哪一行；但是我们构建了所有关于先前值的数据，现在我们需要一种方法将该查询的结果与子查询的结果连接起来.

幸运的是，MySQL 8有一种方法可以隐藏查询并多次使用它，称为公共表表达式，使用WITH子句docs here.因此，我们可以创建包含所有有趣数据的表，然后将其用作子查询，以获取我们最终想要的id，然后将其与我们刚刚创建的结果连接起来:

WITH
  transitions AS (SELECT 
    assessment_id,
    device_id,
    test,
    result,
    LAG (result) over w as `previous_value`,
    LAG (assessment_id) over w as `previous_assessment_id`
FROM assessments join results using(assessment_id)
WINDOW w AS (PARTITION BY device_id, test ORDER BY assessment_id)
)

SELECT transitions.*
FROM transitions
JOIN (
    SELECT MAX(assessment_id) as assessment_id
    FROM transitions
    WHERE result != previous_value OR previous_value IS NULL
    GROUP BY device_id, test
) AS t2 using (assessment_id)

给我们最终答案(以及您可以填写的其他栏):

+---------------+-----------+------+--------+----------------+------------------------+
| assessment_id | device_id | test | result | previous_value | previous_assessment_id |
+---------------+-----------+------+--------+----------------+------------------------+
|             3 |         1 | A    | FAIL   | PASS           |                      1 |
|             4 |         2 | B    | PASS   | FAIL           |                      2 |
+---------------+-----------+------+--------+----------------+------------------------+

第一部分创建了一个数据集，其中包括关于每次测试结果的所有信息.然后我们编写一个查询，获取该查询中感兴趣行的id，然后我们连接回原始数据集以填充所有列.