MySql查询用于标识表中的序列

I Need help with mysql query to update a new column of the same table based on series of entry and exit dates.

我需要帮助mysql查询根据一系列进入和退出日期更新同一个表的新列。

Below is table:

下表是:

╔════╦═══════════╦═════════════╦═════════════╦════════════════════╗
║ ID ║   PLACE   ║  ENTRYDATE  ║  EXITDATE   ║ LAST_PLACE_VISITED ║
╠════╬═══════════╬═════════════╬═════════════╬════════════════════╣
║  1 ║ Delhi     ║ 1-Jan-2012  ║ 5-Jan-2012  ║                    ║
║  1 ║ Agra      ║ 10-Jan-2012 ║ 11-Jan-2012 ║                    ║
║  1 ║ Bangalore ║ 21-Jan-2012 ║ 24-Jan-2012 ║                    ║
║  1 ║ Mumbai    ║ 12-Jan-2012 ║ 19-Jan-2012 ║                    ║
║  2 ║ LA        ║ 1-Mar-2012  ║ 3-Mar-2012  ║                    ║
║  2 ║ SFO       ║ 10-Mar-2012 ║ 14-Mar-2012 ║                    ║
║  2 ║ NY        ║ 4-Mar-2012  ║ 9-Mar-2012  ║                    ║
║  3 ║ Delhi     ║ 12-Apr-2012 ║ 13-Apr-2012 ║                    ║
╚════╩═══════════╩═════════════╩═════════════╩════════════════════╝

The data type of ENTRYDATE and EXITDATE is DATE.

ENTRYDATE和EXITDATE的数据类型是DATE。

From the above table i need to write a query to update "Last_Place_Visited" column based on entry and exit date of the ID.

从上表我需要编写一个查询,根据ID的进入和退出日期更新“Last_Place_Visited”列。

Any help with this query would be much appriciated.

任何有关此查询的帮助都会受到很多关注。

Thanks. Bhargav

2 个解决方案

#1

I tried to modify the table itself:

我试图修改表本身:

UPDATE T SET LAST_PLACE_VISITED = (
  SELECT  t2.PLACE
  FROM    T t2
  WHERE   t2.EXITDATE = (  
    SELECT  MAX(t1.EXITDATE)
    FROM    T t1
    WHERE   t1.ID = ID
    AND     t1.EXITDATE < EXITDATE    
));

MySQL won't permit this: You can't specify target table 'T' for update in FROM clause:

MySQL不允许这样做:你不能在FROM子句中为更新指定目标表'T':

But you could work with a view or a temporary table and use this:

但您可以使用视图或临时表并使用此:

UPDATE: Inserted LIMIT 1 for dealing with the case of multiple occurences of the maximum value of EXITDATE for distinct IDs. Disadvantage: We cannot predict which row with maximum value will be taken.

更新:插入LIMIT 1,用于处理不同ID的EXITDATE最大值出现多次的情况。缺点:我们无法预测哪个行具有最大值。

UPDATE 2: Added condition AND t2.ID = t0.ID

更新2:添加条件AND t2.ID = t0.ID

SELECT t0.ID, t0.PLACE, t0.ENTRYDATE, t0.EXITDATE, (
  SELECT t2.PLACE
  FROM T t2
  WHERE t2.EXITDATE = (  
    SELECT  MAX(t1.EXITDATE)
    FROM    T t1
    WHERE   t1.ID = t0.ID
    AND     t1.EXITDATE < t0.EXITDATE    
  )
  AND t2.ID = t0.ID
  LIMIT 1
  ) AS LAST_PLACE_VISITED
FROM T t0;

See my SQLFiddle demo

看我的SQLFiddle演示

#2

Here's a very messy one since MySQL doesn't support window functions,

这是一个非常混乱的,因为MySQL不支持窗口函数,

UPDATE  TravelTbl a
        INNER JOIN
        (
            SELECT  a.ID, 
                    a.Place, 
                    a.EntryDate, 
                    a.ExitDate, 
                    b.Place Last_Place_Visited
            FROM
                    (
                        SELECT  ID,
                                Place,
                                EntryDate,
                                ExitDate,
                                Last_Place_Visited,
                                @grp := if(@ID = ID, @grp ,0) + 1 GRP_RecNo,
                                @ID := ID
                        FROM    TravelTbl,
                                (SELECT @ID := '', @grp := 0) vars
                        ORDER   BY  EntryDate
                    ) a
                    LEFT JOIN
                    (
                        SELECT  ID,
                                Place,
                                EntryDate,
                                ExitDate,
                                Last_Place_Visited,
                                @grp2 := if(@ID2 = ID, @grp2 ,0) + 1 GRP_RecNo,
                                @ID2 := ID
                        FROM    TravelTbl,
                                (SELECT @ID2 := '', @grp2 := 0) vars
                        ORDER   BY  EntryDate
                    ) b ON  a.ID = b.ID AND
                            a.GRP_RecNo = b.GRP_RecNo + 1
        ) b ON  a.ID = b.ID AND
                a.Place = b.Place AND
                a.EntryDate = b.EntryDate AND
                a.ExitDate = b.ExitDate AND
                b.Last_Place_Visited IS NOT NULL
SET     a.Last_Place_Visited = b.Last_Place_Visited

SQLFiddle Demo

OUTPUT

╔════╦═══════════╦═════════════╦═════════════╦════════════════════╗
║ ID ║   PLACE   ║  ENTRYDATE  ║  EXITDATE   ║ LAST_PLACE_VISITED ║
╠════╬═══════════╬═════════════╬═════════════╬════════════════════╣
║  1 ║ Delhi     ║ 1-Jan-2012  ║ 5-Jan-2012  ║ (null)             ║
║  1 ║ Agra      ║ 10-Jan-2012 ║ 11-Jan-2012 ║ Delhi              ║
║  1 ║ Bangalore ║ 21-Jan-2012 ║ 24-Jan-2012 ║ Mumbai             ║
║  1 ║ Mumbai    ║ 12-Jan-2012 ║ 19-Jan-2012 ║ Agra               ║
║  2 ║ LA        ║ 1-Mar-2012  ║ 3-Mar-2012  ║ (null)             ║
║  2 ║ SFO       ║ 10-Mar-2012 ║ 14-Mar-2012 ║ NY                 ║
║  2 ║ NY        ║ 4-Mar-2012  ║ 9-Mar-2012  ║ LA                 ║
║  3 ║ Delhi     ║ 12-Apr-2012 ║ 13-Apr-2012 ║ (null)             ║
╚════╩═══════════╩═════════════╩═════════════╩════════════════════╝

#1