SQL -从上一次重复出现状态获取

Question

我有一张像下面这样的桌子

#ID ResultStatus    StatusDate
100 F               9/01/2017
100 S               6/01/2017
100 F               2/01/2017
300 F               7/01/2017
300 F               3/01/2017
300 S               1/01/2017
500 S               7/01/2017
800 F               7/01/2017
800 S               3/01/2017
800 F               2/01/2017
800 S               1/01/2017

我想得到所有的'F‘记录后，最后'S’纪录。它应该会回来

• ID 100创9/01/2017纪录
• 300伊拉克第纳尔2017年3月1日和2017年7月1日的记录
• 对于ID 500，没有，因为没有F
• 800伊拉克第纳尔2017年7/01/2017纪录 选择上次成功后的所有失败。

我正在使用Teradata，但是任何SQL帮助都是非常感谢的。

Answer 1

标准的SQL方法是：

select t.*
from t
where t.resultstatus = 'F' and
      t.statusdate > (select max(t2.statusdate)
                      from t t2
                      where t2.resultstatus = 'S' and t2.id = t.id
                     );

但是，我也倾向于使用窗口函数来完成这个任务：

select t.*
from (select t.*,
             max(case when t.resultstatus = 'S' then statusdate end) over (partition by id) as max_s
      from t
     ) t
where t.resultstatus = 'F' and
      t.statusdate > max_s;

如果在没有S时需要所有行，则将where更改为：

where resultstatus = 'F' and
      (statusdate > max_s or max_s is null);

编辑：

下列办法也可能有效：

select t.*
from t
qualify t.resultstatus = 'F' and
        t.statusdate > max(case when t.resultstatus = 'S' then statusdate end) over (partition by id);

Answer 2

通过使用交叉连接、解析函数ROW_NUMBER()，我们可以完成这个问题。下面是下面链接中的SQL解决方案，它将详细解释它。

DDL :-

CREATE TABLE Sample( ID INT, ResultStatus VARCHAR(10), StatusDate DATE);

INSERT INTO Sample VALUES(100,'F','09-01-2017');
INSERT INTO Sample VALUES(100,'S','06-01-2017');
INSERT INTO Sample VALUES(100,'F','02-01-2017');

INSERT INTO Sample VALUES(300,'F','07-01-2017');
INSERT INTO Sample VALUES(300,'F','03-01-2017');
INSERT INTO Sample VALUES(300,'S','01-01-2017');

INSERT INTO Sample VALUES(500,'F','07-01-2017');

INSERT INTO Sample VALUES(800,'F','07-01-2017');
INSERT INTO Sample VALUES(800,'S','03-01-2017');
INSERT INTO Sample VALUES(800,'F','02-01-2017');
INSERT INTO Sample VALUES(800,'S','01-01-2017');

SQL :-

SELECT B.id,B.ResultStatus,B.StatusDate
  FROM
(
SELECT *,
       ROW_NUMBER() OVER( PARTITION BY ID ORDER BY StatusDate DESC ) AS rn,
       ROW_NUMBER() OVER( PARTITION BY ID ORDER BY ResultStatus DESC,StatusDate ) AS rn_status
  FROM Sample ) A
  CROSS JOIN
 (
SELECT *,
       ROW_NUMBER() OVER( PARTITION BY ID ORDER BY StatusDate DESC ) AS rn,
       ROW_NUMBER() OVER( PARTITION BY ID ORDER BY ResultStatus DESC,StatusDate DESC ) AS rn_status
  FROM Sample 
 ) B
WHERE A.ResultStatus = 'S'
  AND A.ResultStatus != B.ResultStatus
  AND B.StatusDate > A.StatusDate 
  AND A.ID = B.ID
  AND A.rn > B.rn
  AND A.rn_status = 1
  AND B.rn_status - B.rn = 1 
;

http://sqlfiddle.com/#!6/b2d17/17