PROSAGA码农传奇-Sybase-如何在表中为另一个表的每个记录查找特定行

<div class =“post-text”itemprop =“text”>
  <P>
    假设这是Sybase ASE，尽管以下大多数是相当简单的SQL，它应该很容易转换为其他RDBMS产品
  </p>
  <P>
    首先，我们将看看我们是否能找到3行
     <code>
      code
    </code>
     改为'A'
  </p>
   <pre>
    <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

</code>
  </pre>
  <P>
    从这里我们应该能够添加一个
     <code>
      top 1
    </code>
     然后翻到
     <code>
      order by / desc
    </code>
     拉最后/最新记录......
  </p>
   <pre>
    <code>
      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

</code>
  </pre>
  <P>
    在ASE 15.7 SP138上测试
  </p>
</DIV>