我有一张a表的记录说:
_____________________________________________________________________________
Date | country | channel | clicks
_____________________________________________________________________________
1-01-2016 | US | MTV | 100
1-01-2016 | US | MTV | 50
_____________________________________________________________________________
我使用查询将此记录插入到另一个表B中:
INSERT INTO B
SELECT
DATE,
country,
channel,
SUM(clicks) AS clicks
FROM
A
GROUP BY DATE,
country,
channel;
现在表B看起来像:
_____________________________________________________________________________
Date | country | channel | clicks
_____________________________________________________________________________
1-01-2016 | US | MTV | 150
_____________________________________________________________________________
在另一个时刻,我在A表中又得到了一个记录
_____________________________________________________________________________
Date | country | channel | clicks
_____________________________________________________________________________
1-01-2016 | US | MTV | 300
_____________________________________________________________________________
现在我想更新表B中的旧记录,它应该如下所示:
_____________________________________________________________________________
Date | country | channel | clicks
_______________________________________________________________________________
1-01-2016 | US | MTV | 450
_____________________________________________________________________________
如何使用MySQL
最佳答案
您可以使用相同的查询(用于insert
)和INNER JOIN
来更新。
UPDATE B
INNER JOIN
(
SELECT
DATE,
country,
channel,
SUM(clicks) AS clicks
FROM A
GROUP BY DATE, country, channel
)AS t
ON B.Date = t.Date AND B.country = t.country AND B.channel = t.channel
SET B.clicks = t.clicks;
编辑:
以便通过同一个查询
update/insert
。INSERT INTO B SELECT
t.DATE,
t.country,
t.channel,
t.clicks
FROM
(
SELECT
DATE,
country,
channel,
SUM(clicks) AS clicks
FROM A
GROUP BY DATE, country, channel
) AS t
ON DUPLICATE KEY UPDATE clicks = t.clicks
关于mysql - MySQL:插入到…从…中选择…在重复键上,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/39590827/