英文:
removing an entire row based on an duplicate column
问题
你可以使用以下SQL语句来删除基于重复时间戳的整行数据：
DELETE t1 FROM tblname t1
INNER JOIN tblname t2
WHERE t1.timestamp_column = t2.timestamp_column
AND t1.id < t2.id;
这将删除具有重复时间戳的行，只保留其中一行。
英文:
Let's say I have a Mysql table that contains timestamps, ids, and a few more parameters.
How can I remove an entire row based on duplicate timestamps?
SELECT DISTINCT COL_NAME From tblname, will return 1 column with my timestamps filtered, but I need to actually remove them from my table (The Entire row).
Thanks!
答案1
得分: 0
根据我的意见，首先找到重复的时间戳数据行。在找到重复行之后，您可以删除其中一行重复行。如果您希望删除具有相同时间戳值的行组中的最小id，这是我的建议代码。这段代码是子查询的组合。
CREATE TEMPORARY TABLE tmp_table AS ( -- 创建临时表
SELECT MIN(id) AS id_to_keep
FROM tblname
GROUP BY timestamp_col
HAVING COUNT(*) > 1
);
DELETE FROM tblname
WHERE id NOT IN (SELECT id_to_keep FROM tmp_table);
请注意，这是SQL代码，用于查找和删除具有重复时间戳的行。
英文:
According to my opinion first find the Duplicate timestamps data rows. After find the duplicate rows , you can delete one of the duplicate row. If you like to delete smallest id for each group of rows with the same timestamp value this is my suggest code. This is code is combination of subqueries.
CREATE TEMPORARY TABLE tmp_table AS ( // create temp table
SELECT MIN(id) AS id_to_keep
FROM tblname
GROUP BY timestamp_col
HAVING COUNT(*) > 1
);
DELETE FROM tblname
WHERE id NOT IN (SELECT id_to_keep FROM tmp_table);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论