我有一张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/

10-16 16:14
查看更多