mysql - 如何使用 'greater than'和 'group by'创建联接？

我有一个包含一系列名称，事件和日期的表。我创建了一个与特定事件(evt5_date)相关的新字段'evt5'。

每个名称可以有多个事件，每个事件的时间记录在evt_date字段中。evt1和evt2这两个事件与evt5相关。

我想将evt5首次出现的日期插入evt1之前的所有evt2和evt5行中。如果在evt5或evt1之后没有evt2，则该字段将保留为空。

必须为每个name完成所有这些操作。有数千种不同的名称。我只在下面的数据中显示2

当前表数据-evt5_date中没有值

name      evt_date       event    evt5_date
name-1    2010-06-30     evt1
name-1    2009-10-30     evt5
name-1    2009-09-30     evt2
name-1    2009-06-30     evt5
name-1    2009-03-30     evt5
name-1    2009-02-28     evt2
name-1    2009-01-30     evt1
name-2    2005-05-30     evt2
name-2    2005-03-30     evt5
name-2    2005-01-30     evt1

我希望它看起来如何-evt5_date字段中的值

name      evt_date       event    evt5_date
name-1    2010-06-30     evt1
name-1    2009-10-30     evt5
name-1    2009-09-30     evt2     2009-10-30
name-1    2009-06-30     evt5
name-1    2009-03-30     evt5
name-1    2009-02-28     evt2     2009-03-30
name-1    2009-01-30     evt1     2009-03-30
name-2    2005-05-30     evt2
name-2    2005-03-30     evt5
name-2    2005-01-30     evt1     2005-03-31

我尝试使用下面的代码执行更新，但我不知道如何指定evt5的日期大于evt1的evt_date和evt2的日期之间的链接，同时还按evt5进行分组以获得与以下内容有关的evt_date:最新的evt5。

我还需要按名称分组，因为事件是特定于每个名称的。

update mytable as t1
set t1.evt5_date = (select min(t2.evt_date) from mytable as t2
                    where t2.event = 'evt5' AND
                           t2.evt_date > t1.evt_date
                    group by name)
where
  t1.event in ('evt1', 'evt2')

任何建议将不胜感激。谢谢

更新最终解决方案-对@biziclop提供的答案进行一些细微更改，以保持name的完整性

UPDATE mytable AS t1
INNER JOIN
    (
      SELECT
             a.name, a.evt_date,
        MIN( b.evt_date ) AS nearest_date
      FROM       mytable AS a
      INNER JOIN mytable AS b ON b.event = 'evt5'
                            AND b.evt_date > a.evt_date
                            AND a.name = b.name       -- needed this additional condition
      GROUP BY a.name, a.evt_date                     -- added 'a.name' to 'group by'
    ) AS nearest_dates
       ON nearest_dates.evt_date = t1.evt_date AND
         nearest_dates.name = t1.name                 -- added this additional condition
SET t1.evt5_date = nearest_dates.nearest_date
WHERE t1.event IN ('evt1', 'evt2');

最佳答案

我修正了您的更新声明，我认为它现在可以正常工作。我省略了GROUP BY name部分，因为它在您的版本中没有意义。

UPDATE mytable AS t1
INNER JOIN
    (
      SELECT
        a.evt_date,
        MIN( b.evt_date ) AS nearest_date
      FROM       mytable AS a
      INNER JOIN mytable AS b ON b.event = 'evt5'
                            AND b.evt_date > a.evt_date
      GROUP BY a.evt_date
    ) AS nearest_dates ON nearest_dates.evt_date = t1.evt_date
SET t1.evt5_date = nearest_dates.nearest_date
WHERE t1.event IN ('evt1', 'evt2');

首先进行一些验证:
http://sqlfiddle.com/#!2/309ac/6

最终的UPDATE查询:
http://sqlfiddle.com/#!2/80c3c/1

关于mysql - 如何使用 'greater than'和 'group by'创建联接？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11084256/