英文:
Delete duplicates from joined table MySQL
问题
请帮助我从 'Component' 表中删除所有具有相同 'country' 且属于相同 'Page' 的记录，只保留具有最新 createdAt 的国家的记录。
表之间的关系：
Page -> PageComponent.pageId -> PageComponent.componentId -> Component.id
带有表和数据的沙盒链接 https://www.db-fiddle.com/f/CnFouhFZyfkEpzGKij5eq/2
英文:
please help me delete from the 'Component' table all records that have the same 'country' and they belong to the same 'Page', leave only one record with the country that has the newest createdAt?
Relationship between tables:
Page -> PageComponent.pageId -> PageComponent.componentId -> Component.id
Sandbox with table and data https://www.db-fiddle.com/f/CnFouhFZyfkEpzGKij5eq/2
答案1
得分: 1
我认为我没有完全理解，但请尝试这个：
DELETE
main
FROM
`Component` main
WHERE main.id NOT IN (
SELECT MAX(c.`id`)
FROM `Page` p
JOIN `PageComponent` pc ON pc.`pageId`=p.`id`
JOIN (SELECT * FROM `Component`) c ON c.`id`=pc.`componentId`
GROUP BY c.`country`, p.`id` HAVING COUNT(*)>1
)
请告诉我是否有什么问题。
英文:
I think I am not getting it all but please try this:
DELETE
main
FROM
`Component` main
WHERE main.id NOT IN (
SELECT MAX(c.`id`)
FROM `Page` p
JOIN `PageComponent` pc ON pc.`pageId`=p.`id`
JOIN (SELECT * FROM `Component`) c ON c.`id`=pc.`componentId`
GROUP BY c.`country`, p.`id` HAVING COUNT(*)>1
)
Let me know if I have messed up something.
答案2
得分: 0
我找到了一个解决方案：
使用 duplicateIdTable 作为 (
选择
任意值(c.country),
在 (按 c.createdAt 排序) p.id, c.country 分区下的 LAG(c.id) 作为 duplicateComponentId,
在 (按 c.createdAt 排序) p.id, c.country 分区下的 LAG(pc.id) 作为 duplicatePageComponentId,
p.id
从 Page p
加入 PageComponent pc ON p.id = pc.pageId
加入 Component c ON pc.componentId = c.id
)
从 Component, PageComponent 删除 FROM Component
内连接 PageComponent ON PageComponent.componentId = Component.id
内连接 duplicateIdTable ON duplicateIdTable.duplicateComponentId = Component.id
和 duplicateIdTable.duplicatePageComponentId = PageComponent.id
其中 Component.id = duplicateIdTable.duplicateComponentId
和 PageComponent.id = duplicateIdTable.duplicatePageComponentId
英文:
I found a solution:
WITH duplicateIdTable AS (
SELECT
any_value(c.country),
LAG(c.id) over (partition by p.id, c.country order by c.createdAt) as duplicateComponentId,
LAG(pc.id) over (partition by p.id, c.country order by c.createdAt) as duplicatePageComponentId,
p.id
FROM Page p
JOIN PageComponent pc ON p.id = pc.pageId
JOIN Component c ON pc.componentId = c.id
)
DELETE Component, PageComponent FROM Component
INNER JOIN PageComponent ON PageComponent.componentId = Component.id
INNER JOIN duplicateIdTable ON duplicateIdTable.duplicateComponentId = Component.id
and duplicateIdTable.duplicatePageComponentId = PageComponent.id
WHERE Component.id = duplicateIdTable.duplicateComponentId
and PageComponent.id = duplicateIdTable.duplicatePageComponentId
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论