mysql - 从 MySQL 数据库中选择多个字段重复项
问题描述
我有一个旧论坛,其中包含重复的第一篇帖子(可能回复不同)的线程。我想删除除其中一个线程之外的所有线程(保留视图计数最高的线程)。
我有以下 SQL 查询来帮助识别重复线程,但我找不到一种方法来仅列出 xf_thread.view_count 列的最低值的重复项:
SELECT
t.thread_id, MIN(t.view_count)
FROM
xf_thread t
INNER JOIN
xf_post p ON p.thread_id = t.thread_id
WHERE
t.first_post_id = p.post_id
GROUP BY
t.title,
t.username,
p.message
HAVING
COUNT(t.title) > 1
AND COUNT(t.username) > 1
AND COUNT(p.message) > 1;
目前,此查询正确地对线程进行分组,但它只显示一个随机 thread_id - 而不是对应于 min(view_count) 的 thread_id。
我已经阅读了如何解决这个问题,但我不知道如何实现这一点 - 因为看起来不可能通过查询对行进行分组排序。
编辑
感谢 Madhur 的帮助,查询现在返回所有要删除的线程 ID。但是,我可以弄清楚如何删除具有匹配 thread_id 的行。这是我尝试使用的查询(它一直在运行,而选择查询(https://stackoverflow.com/a/52314208/2469308)在几秒钟内运行:
DELETE FROM xf_thread
WHERE thread_id IN (SELECT Substring_index(Group_concat(DISTINCT t.thread_id
ORDER BY
t.view_count
ASC
SEPARATOR ','),
',', 1) AS
thread_id_with_minimum_views
FROM (SELECT *
FROM xf_thread) t
INNER JOIN xf_post p
ON p.thread_id = t.thread_id
WHERE t.first_post_id = p.post_id
AND t.user_id = 0
AND t.reply_count < 2
GROUP BY t.title,
t.username,
p.message
HAVING Count(t.title) > 1
AND Count(t.username) > 1
AND Count(p.message) > 1
ORDER BY t.thread_id);
解决方案
一个非常 hacky 的解决方案是在GROUP_CONCATthread_id
中进行排序。然后,我们可以利用字符串操作来获得最小值。view_count
thread_id
view_count
在您的SELECT
子句中t.thread_id
,您可以尝试以下操作,而不是 :
SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT t.thread_id
ORDER BY t.view_count ASC
SEPARATOR ','),
',',
1) AS thread_id_with_minimum_views
现在,基于SELECT
使用最小视图识别重复记录的DELETE
查询,从表中删除此类记录的查询xf_thread
将如下所示:
DELETE t_delete FROM xf_thread AS t_delete
INNER JOIN (SELECT CAST(SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT t.thread_id ORDER BY t.view_count ASC SEPARATOR ','), ',', 1) AS UNSIGNED) AS tid_min_view
FROM (SELECT * FROM xf_thread) t
INNER JOIN xf_post p ON p.thread_id = t.thread_id
WHERE t.first_post_id = p.post_id
AND t.user_id = 0
AND t.reply_count < 2
GROUP BY t.title, t.username, p.message
HAVING Count(t.title) > 1
AND Count(t.username) > 1
AND Count(p.message) > 1
ORDER BY t.thread_id) AS t_dup
ON t_delete.thread_id = t_dup.tid_min_view