带有左连接和分组依据的 MySQL 更新查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3022405/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Update query with left join and group by
提问by Rob
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.
它创建了一个三元组 Index1、Index2、EventCounts 的列表。它仅在 t.SpecialEventCount 为 NULL 的情况下执行此操作。我尝试编写的更新查询应该将此 SpecialEventCount 设置为该计数，即上面查询中的 COUNT(m.EventType)。这个数字可以是 0 或任何正数（因此是左连接）。Index1 和 Index2 在 Table t 中是唯一的，它们用于标识 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.
但我很困惑应该把什么放在哪里，并因无数不同的猜测而失败。
回答by Hammerite
I take it that (Index1, Index2)is a unique key on Table, otherwise I would expect the reference to t.SpecialEventCountto result in an error.
我认为它(Index1, Index2)是 上的唯一键Table，否则我希望引用t.SpecialEventCount会导致错误。
Edited query to use subquery as it didn't work using GROUP BY
编辑查询以使用子查询，因为它无法使用 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
回答by zvibo
Doing a left join with a subquery will generate a giant temporary table in-memory that will have no indexes.
对子查询执行左连接将在内存中生成一个没有索引的巨大临时表。
For updates, try avoiding joins and using correlated subqueries instead:
对于更新，请尝试避免连接并使用相关子查询：
UPDATE
Table AS t
SET
t.SpecialEventCount = (
SELECT COUNT(m.EventType)
FROM MEvents m
WHERE m.EventType in ('A','B')
AND m.Index1 = t.Index1
AND m.Index2 = t.Index2
)
WHERE
t.SpecialEventCount IS NULL
Do some profiling, but this can be significantly faster in some cases.
做一些分析，但在某些情况下这可能会更快。
回答by riverfan
my example
我的例子
update card_crowd as cardCrowd
LEFT JOIN
(
select cc.id , count(1) as num
from card_crowd cc LEFT JOIN
card_crowd_r ccr on cc.id = ccr.crowd_id
group by cc.id
) as tt
on cardCrowd.id = tt.id
set cardCrowd.join_num = tt.num;
