SQL 删除重复数据

创建测试表

CREATE TABLE emp(
	id serial2,		//表的的自增
	name VARCHAR,
	age VARCHAR,
	addr VARCHAR,
	opreate_time VARCHAR
	);

插入测试数据

INSERT INTO emp (name, age, addr, operate_time) VALUES('david', '20', '上海', '123400');
INSERT INTO emp (name, age, addr, operate_time) VALUES('david', '20', '上海', '123401');
INSERT INTO emp (name, age, addr, operate_time) VALUES('steve', '20', '上海', '123402');
INSERT INTO emp (name, age, addr, operate_time) VALUES('Tom', '18', '北京', '123403');
INSERT INTO emp (name, age, addr, operate_time) VALUES('Tom', '18', '北京', '123404');
INSERT INTO emp (name, age, addr, operate_time) VALUES('Jim', '23', '南京', '123405');
INSERT INTO emp (name, age, addr, operate_time) VALUES('lili', '19', '青岛', '123406');

若要保证 name、age、addr 这三个字段作为一个整体去重，方案如下：
第一种方案：（先分组后去重）

DELETE FROM emp m 
WHERE m.id not in (
SELECT MIN(t.id) 
FROM emp t 
GROUP BY t.name, t.age, t.addr
);

第二种方案：

DELETE From emp a
WHERE a.id <> (
SELECT MIN(b.id)
FROM emp b 
WHERE a.name = b.name AND a.age=b.age AND a.addr=b.addr
);