>我有这个:
1899, 184, 531 *
1900, 184, 531 *
1901, 113, 531
1902, 184, 436
我想得到
1899, 184, 531
1901, 113, 531
1902, 184, 436
没有临时表等。怎么办?
SELECT MIN(Col1), Col2, Col3
FROM yourTable
GROUP BY Col2, Col3
这假设您选择了1899
记录而不是1900
记录,因为前者是两者中较小的记录。
如果要从表中删除重复记录(已将其定义为重复记录),则可以使用以下查询:
DELETE t1.*
FROM yourTable t1
LEFT JOIN
(
SELECT MIN(Col1) AS Col1, Col2, Col3
FROM yourTable
GROUP BY Col2, Col3
) t2
ON t1.Col1 = t2.Col1 AND t1.Col2 = t2.Col2 AND t1.Col3 = t2.Col3
WHERE t2.Col1 IS NULL
如果你想做一个 SELECT 来过滤掉重复项,你可以使用这个:
SELECT DISTINCT Col2, Col3 FROM table_name ORDER BY Id;
SQL fiddle for SELECT
或者,如果需要 DELETE 查询,可以执行以下操作:
DELETE t1 FROM table_name t1, table_name t2
WHERE t1.Id < t2.Id
AND t1.Col2 = t2.Col2
AND t1.Col3 = t2.Col3;
用于删除的 SQL 小提琴
对于删除:
DELETE
FROM table1 t
WHERE EXISTS (SELECT u.*
FROM table1 u
WHERE u.col2 = t.col2 AND u.col3 = t.col3
AND u.col1 > t.col1)
当然,在与子查询相同的表上存在删除的问题。
对于DELETE
:
DELETE t1
FROM yourtable t1
JOIN yourtable t2
ON t1.col2 = t2.col2
AND t1.col3 = t2.col3
AND t1.col1 > t2.col1
SQLFiddle 演示
select min(f1) as f1, f2, f3 from tbl group by f2, f3
这可以删除重复的行。
从table_name中删除,其中列 1 在(从中选择列 1(从table_name按列 1 分组,列 2 具有计数(*)>1) t 中选择列 1);