将表与同一表的先前记录联接 [英] Join a table with previous record of same table

问题描述

我有一个带有历史记录的表，该表由多个触发器存储在主表中.我想在历史记录表上创建一条select语句，在该表中，我的每条记录都由其先前的记录(由相同的LineID和最高的ActionDate标识)进行了联接，以便提取这两者之间的差异.

I have a table with historic records, posted there by several triggers in a main table. I want to create a select statement on the history table where I have each record JOINed by their prior record (identified by the same LineID and the highest ActionDate) so I can extract the differences between those two.

我尝试了此操作，但是(My)SQL不允许引用JOINED子选择中的第一个"FROM"表:where子句中的未知列h1.LineID

I tried this, but (My)SQL does not allow references to the first "FROM" table in the JOINED subselect: Unknown column h1.LineID in where clause

select 
  h1.*, 
  prev.* 
from history h1
LEFT OUTER JOIN 
  (Select * 
     From history h2 
     where  h1.LineID=h2.LineID and h2.ActionDate < h1.ActionDate 
     order by Actiondate desc limit 1
  ) prev on h1.LineID=prev.LineID

我该怎么做?

推荐答案

您可以使用以下内容获取对上一行的引用:

You can get a reference to the previous row using:

select h.*,
       (select h2.ActionDate
        from history h2
        where h2.LineId = h.LineId and h2.ActionDate < h.ActionDate
        order by h2.ActionDate desc
        limit 1
       ) as prev_ActionDate
from history h;

如果需要完整的行，可以使用join来获取数据:

If you want the complete row, you can use a join to get the data:

select h.*, hprev.*
from (select h.*,
             (select h2.ActionDate
              from history h2
              where h2.LineId = h.LineId and h2.ActionDate < h.ActionDate
              order by h2.ActionDate desc
              limit 1
             ) as prev_ActionDate
      from history h
     ) h left join
     history hprev
     on hprev.LineId = h.LineId and hprev.ActionDate = h.prev_ActionDate;

这篇关于将表与同一表的先前记录联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！