删除重复行的两种方法

数据准备：

CREATE TABLE my_test (id INT NOT NULL PRIMARY KEY, col CHAR(1));
INSERT INTO my_test(1,'a');
INSERT INTO my_test(2,'a');
INSERT INTO my_test(3,'b');
INSERT INTO my_test(4,'b');
INSERT INTO my_test(5,'b');
INSERT INTO my_test(6,'c');
INSERT INTO my_test(7,'c');
INSERT INTO my_test(8,'d');
SEL * FROM my_test order by id;

结果

	id	col
1	1	a
2	2	a
3	3	b
4	4	b
5	5	b
6	6	c
7	7	c
8	8	d

方法1：

BT;
DELETE FROM my_test a WHERE id>(
SEL MIN(id) FROM my_test b WHERE a.col=b.col);
SEL * FROM my_test ORDER BY id;
ROLLBACK;

或者：

BT;
DELETE FROM my_test a WHERE id<(
SEL MAX(id) FROM my_test b WHERE a.col=b.col);
SEL * FROM my_test ORDER BY id;
ROLLBACK;

结果：

	id	col
1	1	a
2	3	b
3	6	c
4	8	d

方法2：

BT;
DELETE FROM  my_test  t1, my_test  t2
WHERE t1.col=t2.col AND t1.id>t2.id;
SEL * FROM my_test;
ROLLBACK;

或者

BT;
DELETE FROM  my_test  t1, my_test  t2
WHERE t1.col=t2.col AND t1.id<t2.id;
SEL * FROM my_test;
ROLLBACK;

结果：

	id	col
1	3	b
2	6	c
3	1	a
4	8	d