Mysql 查找另一个值后面的值

Question

我很难给出一个连贯的陈述，但我可以提供一个示例集

ID STATUS DATE
1  A      2016-01-01
2  A      2016-01-01
2  B      2016-01-02
3  C      2016-01-13
4  D      2016-01-14
5  A      2016-01-15
5  B      2016-01-16
6  A      2016-01-17
7  C      2016-01-18
8  B      2016-01-19
9  B      2016-01-20

我想要一个可以确定两件事的sql语句:1) 有多少个项目从 STATUS = A 转到 STATUS = B，且具有相同的 ID2)我只想显示具有上述状态的行 - 如下:

ID STATUS DATE        
2  A      2016-01-01
2  B      2016-01-02
5  A      2016-01-15
5  B      2016-01-16

COUNT(distinct ID) of that result should return 2 in this case

如有任何帮助，我们将不胜感激

Answer 1

将表与其自身连接起来，将行与其后面具有相同 ID 的行进行匹配。

SELECT t1.id, t1.status AS start_status, t1.date AS start_date,
        t2.status AS end_status, t2.date AS end_date
FROM yourTable AS t1
JOIN yourTable AS t2 ON t1.id = t2.id AND t1.date = date_sub(t2.date, interval 1 day)
WHERE t1.status = 'A' AND t2.status = 'B'

这将同时显示两行，例如

id start_status start_date end_status end_date
2  A            2016-01-01 B          2016-01-02
5  A            2016-01-15 B          2016-01-16