gpt4 book ai didi

mysql查询将唯一行移动到临时表

转载 作者:行者123 更新时间:2023-11-29 08:25:24 25 4
gpt4 key购买 nike

我想创建一个查询,将每个唯一条目从一个表移动到另一个临时表。我创建了以下查询来获取唯一条目:

select date, messageType, zCampaignId, isSenderPolicy, sender, recipient, policy, operator, country, zNumber 
from topcampaigns_HOUR
group by date, messageType, zCampaignId, isSenderPolicy, sender, recipient, policy, operator, country, zNumber
having count(*) = 1;

它返回很多结果:

/* Affected rows: 0  Found rows: 473  Warnings: 0  Duration for 1 query: 0.000 sec. (+ 0.016 sec. network) */

但是,当我将其放入更新中时,我似乎无法更新 tmp 表:

UPDATE topcampaigns_HOUR_tmp as b 
INNER JOIN (
select date, messageType, zCampaignId, isSenderPolicy, sender, recipient, policy, operator, country, zNumber
from topcampaigns_HOUR
group by date, messageType, zCampaignId, isSenderPolicy, sender, recipient, policy, operator, country, zNumber
having count(*) = 1) as a
SET
b.date=a.date,
b.messageType=a.messageType,
b.zCampaignId=a.zCampaignId,
b.isSenderPolicy=a.isSenderPolicy,
b.sender=a.sender,
b.recipient=a.recipient,
b.policy=a.policy,
b.operator=a.operator,
b.country=a.country,
b.zNumber=a.zNumber;

结果是:

/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 1 query: 00:04:25 */

我尝试了很多方法,例如使用:

UPDATE topcampaigns_HOUR_tmp JOIN (select date, messageType, zCampaignId, isSenderPolicy, sender, recipient, policy, operator, country, zNumber from topcampaigns_HOUR group by date, messageType, zCampaignId, isSenderPolicy, sender, recipient, policy, operator, country, zNumber having count(*) = 1) a SET 
topcampaigns_HOUR_tmp.date=a.date,
topcampaigns_HOUR_tmp.messageType=a.messageType,
topcampaigns_HOUR_tmp.zCampaignId=a.zCampaignId,
topcampaigns_HOUR_tmp.isSenderPolicy=a.isSenderPolicy,
topcampaigns_HOUR_tmp.sender=a.sender,
topcampaigns_HOUR_tmp.recipient=a.recipient,
topcampaigns_HOUR_tmp.policy=a.policy,
topcampaigns_HOUR_tmp.operator=a.operator,
topcampaigns_HOUR_tmp.country=a.country,
topcampaigns_HOUR_tmp.zNumber=a.zNumber;

我再次得到:

/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 1 query: 00:03:42 */

topcampaigns_HOUR_tmp 表似乎永远不会更新。

我做错了什么?

一个

最佳答案

如果您要添加行,则需要 INSERT;如果您要从查询结果添加行,则需要 INSERT ... SELECT 。尝试这样的事情:

INSERT INTO topcampaigns_HOUR_tmp (
date, messageType, zCampaignId, isSenderPolicy, sender,
recipient, policy, operator, country, zNumber)
select
date, messageType, zCampaignId, isSenderPolicy, sender,
recipient, policy, operator, country, zNumber
from topcampaigns_HOUR
group by
date, messageType, zCampaignId, isSenderPolicy, sender,
recipient, policy, operator, country, zNumber
having count(*) = 1

关于mysql查询将唯一行移动到临时表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/18106924/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com