MySQL更新查询，左连接和分组依据 [英] MySQL Update query with left join and group by

问题描述

我正在尝试创建更新查询，并且在获取正确的语法方面进展甚微。
以下查询正在工作：

  SELECT t.Index1，t.Index2，COUNT（m.EventType）
 FROM表t 
 LEFT JOIN MEENT m ON 
（m.Index1 = t.Index1 AND 
 m.Index2 = t.Index2 AND 
（m.EventType =' A'OR m.EventType ='B'）
）
 WHERE（t.SpecialEventCount IS NULL）
 GROUP BY t.Index1，t.Index2 
  

它创建一个三元组Index1，Index2，EventCounts的列表。
它仅在t.SpecialEventCount为NULL的情况下执行此操作。我正在尝试编写的更新查询应将此SpecialEventCount设置为该计数，即上述查询中的COUNT（m.EventType）。这个数字可以是0或任何正数（因此左连接）。索引1和索引2在表t中是唯一的，它们用于识别MEvent中的事件。

如何修改select查询以成为更新查询？即就像

  UPDATE Table SET SpecialEventCount = COUNT（m.EventType）..... 
  

但是我很困惑把什么放在哪里并且失败了许多不同的猜测。

（Index1，Index2）是 Table上的唯一键 $ c>，否则我会期望引用 t.SpecialEventCount 导致出错。

编辑的查询使用子查询，因为它无法使用 GROUP BY

  UPDATE 
表格AS t 
 LEFT JOIN（
 SELECT 
索引1，
索引2，
 COUNT（EventType）AS NumEvents 
 FROM 
 MEvents 
 WHERE 
 EventType ='A'OR EventType ='B'
 GROUP BY 
 Index1，
 Index2 
）AS m ON 
 m.Index1 = t.Index1 AND 
 m.Index2 = t.Index2 
 SET 
 t.SpecialEventCount = m.NumEvents 
 WHERE 
 t.SpecialEventCount IS NULL 
  

I am trying to create an update query and making little progress in getting the right syntax. The following query is working:

SELECT t.Index1, t.Index2, COUNT( m.EventType ) 
    FROM Table t
    LEFT JOIN MEvents m ON
        (m.Index1 = t.Index1 AND
         m.Index2 = t.Index2 AND
        (m.EventType =  'A' OR m.EventType =  'B')
    ) 
    WHERE (t.SpecialEventCount IS NULL)
    GROUP BY t.Index1, t.Index2

It creates a list of triplets Index1,Index2,EventCounts. It only does this for case where t.SpecialEventCount is NULL. The update query I am trying to write should set this SpecialEventCount to that count, i.e. COUNT(m.EventType) in the query above. This number could be 0 or any positive number (hence the left join). Index1 and Index2 together are unique in Table t and they are used to identify events in MEvent.

How do I have to modify the select query to become an update query? I.e. something like

UPDATE Table SET SpecialEventCount=COUNT(m.EventType).....

but I am confused what to put where and have failed with numerous different guesses.

解决方案

I take it that (Index1, Index2) is a unique key on Table, otherwise I would expect the reference to t.SpecialEventCount to result in an error.

Edited query to use subquery as it didn't work using GROUP BY

UPDATE
    Table AS t
    LEFT JOIN (
        SELECT
            Index1,
            Index2,
            COUNT(EventType) AS NumEvents
        FROM
            MEvents
        WHERE
            EventType = 'A' OR EventType = 'B'
        GROUP BY
            Index1,
            Index2
    ) AS m ON
        m.Index1 = t.Index1 AND
        m.Index2 = t.Index2
SET
    t.SpecialEventCount = m.NumEvents
WHERE
    t.SpecialEventCount IS NULL

这篇关于MySQL更新查询，左连接和分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！