我有一个包含以下列的表:
URL_ID
URL_ADDR
URL_Time
我想使用MySQL查询来删除URL_ADDR
列上的重复项。
是否可能在不使用任何编程的情况下完成此操作?
考虑以下测试用例:
CREATE TABLE mytb (url_id int, url_addr varchar(100));
INSERT INTO mytb VALUES (1, 'www.google.com');
INSERT INTO mytb VALUES (2, 'www.microsoft.com');
INSERT INTO mytb VALUES (3, 'www.apple.com');
INSERT INTO mytb VALUES (4, 'www.google.com');
INSERT INTO mytb VALUES (5, 'www.cnn.com');
INSERT INTO mytb VALUES (6, 'www.apple.com');
我们的测试表现在包含:
SELECT * FROM mytb;
+--------+-------------------+
| url_id | url_addr |
+--------+-------------------+
| 1 | www.google.com |
| 2 | www.microsoft.com |
| 3 | www.apple.com |
| 4 | www.google.com |
| 5 | www.cnn.com |
| 6 | www.apple.com |
+--------+-------------------+
5 rows in set (0.00 sec)
那么我们可以使用多表 DELETE
语法,如下所示:
DELETE t2
FROM mytb t1
JOIN mytb t2 ON (t2.url_addr = t1.url_addr AND t2.url_id > t1.url_id);
... 这将删除重复条目,仅基于 url_id
保留第一个 URL:
SELECT * FROM mytb;
+--------+-------------------+
| url_id | url_addr |
+--------+-------------------+
| 1 | www.google.com |
| 2 | www.microsoft.com |
| 3 | www.apple.com |
| 5 | www.cnn.com |
+--------+-------------------+
3 rows in set (0.00 sec)
更新 - 基于上面的新评论:
如果重复的URL不具有相同的格式,您可能希望应用REPLACE()
函数来删除www。
或http://
部分。例如:
DELETE t2
FROM mytb t1
JOIN mytb t2 ON (REPLACE(t2.url_addr, 'www.', '') =
REPLACE(t1.url_addr, 'www.', '') AND
t2.url_id > t1.url_id);
您可能想尝试在http://labs.creativecommons.org/2010/01/12/removing-duplicate-rows-in-mysql/提到的方法。
ALTER IGNORE TABLE your_table ADD UNIQUE INDEX `tmp_index` (URL_ADDR);
这将只保留特定的URL_ADDR
中具有最高URL_ID
的那些记录。
DELETE FROM table
WHERE URL_ID NOT IN
(SELECT ID FROM
(SELECT MAX(URL_ID) AS ID
FROM table
WHERE URL_ID IS NOT NULL
GROUP BY URL_ADDR ) X) /*Sounds like you would need to GROUP BY a
calculated form - e.g. using REPLACE to
strip out www see Daniel's answer*/
(衍生表'X'旨在避免错误“无法在FROM子句中更新目标表'tablename'”)
好的,你可以这样做:
INSERT INTO ... SELECT DISTINCT
从原始表中选择并插入到临时表中;INSERT INTO ... SELECT
从临时表中选择并插入到原始表中;这种方法有些笨拙和麻烦,并需要多次查询(更不用说特权),但如果你找不到其他解决方案,它会起到作用。
您可以按照URL_ADDR进行分组,这将有效地为您提供URL_ADDR字段中的唯一值。
select
URL_ID
URL_ADDR
URL_Time
from
some_table
group by
URL_ADDR
祝愉快!
DELETE FROM `your_table`
WHERE
`unique_id` IN (
SELECT
`unique_id`
FROM (
SELECT
`unique_id_in_table`,
ROW_NUMBER() OVER (
PARTITION BY `group_by_column`
ORDER BY `group_by_column`) AS row_num
FROM
`your_table`
) t
WHERE row_num > 1
);
其中:
如何为多列创建Daniel Vassallo指南?
DELETE t2
FROM directory1 t1
JOIN directory1 t2 ON
(t2.page = t1.page,
t2.parentTopic = t1.parentTopic,
t2.title = t1.title,
t2.description = t1.description,
t2.linktype = t1.linktype,
t2.priority = t1.priority
AND t2.linkID > t1.linkID);
也许是这样的吗?
DELETE FROM url WHERE URL_ID IN (
SELECT URL_ID
FROM url a INNER JOIN (
SELECT URL_ADDR, MAX(URL_ID) MaxURLId
FROM url
GROUP BY URL_ADDR
HAVING COUNT(*) > 1) b ON a.URL_ID <> b.MaxURLId AND a.URL_ADDR = b.URL_ADDR
)