这就是表格的样子 -时间id代码
8/22/1999 12:00:00 AM A< ———— no8/24/2001 6:03:02 AM A6/27/2002 4:45:20 PM 0001 B.5 / …
假设这是Sybase ASE,尽管以下大多数是相当简单的SQL,它应该很容易转换为其他RDBMS产品
首先,我们将看看我们是否能找到3行 code 改为'A'
code
select f1.[time], f1.id, f1.code from fliptable f1 where f1.code = 'A' and ( -- see if the previous record has a code!='A'; to find the 'previous' -- record we find the row with max(time) < current record's time exists (select 1 from fliptable f2 where f2.id = f1.id and f2.code != f1.code and f2.[time] = (select max(f3.[time]) from fliptable f3 where f3.id = f1.id and f3.[time] < f1.[time])) or -- catch case where the 'first' row in the table has code='A' not exists (select 1 from fliptable f4 where f4.id = f1.id and f4.[time] < f1.[time]) ) order by f1.[time] go time id code ------------------------------- ---- ---- Aug 22 1999 12:00AM 0001 A May 8 2003 10:02AM 0001 A Dec 6 2017 9:30PM 0001 A Dec 6 2017 9:30PM 0001 A <=== side effect of having a dup row in the data
从这里我们应该能够添加一个 top 1 然后翻到 order by / desc 拉最后/最新记录......
top 1
order by / desc
select top 1 f1.[time], f1.id, f1.code from fliptable f1 where f1.code = 'A' and ( exists (select 1 from fliptable f2 where f2.id = f1.id and f2.code != f1.code and f2.[time] = (select max(f3.[time]) from fliptable f3 where f3.id = f1.id and f3.[time] < f1.[time])) or not exists (select 1 from fliptable f4 where f4.id = f1.id and f4.[time] < f1.[time]) ) order by f1.[time] desc go time id code ------------------------------- ---- ---- Dec 6 2017 9:30PM 0001 A
在ASE 15.7 SP138上测试