如何使用“大于”和“分组”创建连接？

Question

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

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

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

所有这些都必须针对每个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和evt2的evt_date之间的关联，并按evt5分组以获取与最新evt5相关的evt_date。

由于事件针对每个名称，因此我还需要按名称分组。

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');

- getting-there

---

a2.evt5_date是谁？我不明白，你更新了T1并设置了a2？ - jcho360

@jcho360 - 抱歉那是个打错字，应该是“t1”。现在已经修正了。 - getting-there

你能在 SQLFiddle 中放一些结构和数据并分享吗？ - jcho360

http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/ - biziclop

@biziclop，谢谢你们的帮助。很抱歉我没有表述清楚，但是NAME字段中的值确实是不同的，这就是为什么我使用了“group by name”语句的原因。我已经更新了上面的问题。我还查看了你在sqlfiddle上提供的代码（太棒了，我以前从未听说过！所以感谢你与我分享）。我已经更新了数据并重新运行了代码。它几乎可以工作了，但我需要在某个地方添加“name”条件。再次感谢你的帮助。 - getting-there

@jcho360 - 谢谢老兄，我无法在上面的评论中标记你。 - getting-there

Answer 1

我修复了你的更新语句，我认为现在它可以正常工作了。 我省略了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

- biziclop

---

非常感谢您提供的帮助，以及在sqlfiddle上对问题进行的详细解释。我做了一些小修改以考虑到不同的名称（如更新后的问题中所示）。再次感谢您的帮助。 - getting-there