表A内容如下
C1 C2
A A001
A A002
A A003
B A003
C A004
D A005
D A006
要求把
A A0001
A A0002
D A0005
这三条记录删除。
也即:C1里重复的记录中只保留C2里最大的那个值
C1 C2
A A001
A A002
A A003
B A003
C A004
D A005
D A006
要求把
A A0001
A A0002
D A0005
这三条记录删除。
也即:C1里重复的记录中只保留C2里最大的那个值
调试欢乐多
比如如下语句:
delete from ABC where C2 in( Select min(C2) From ABC Where C1 In
(Select C1 From ABC Group By C1 Having Count(*)>1)
group by C1)mysql就报错,说不允许在删除时查询要删除的表。
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
create temporary table tmp1(c1 varchar(20),c2 varchar(20));
insert into tmp1 (c1,c2) select c1,c2 FROM `a` WHERE c2 NOT IN (SELECT max( c2 ) c2 FROM a a2 WHERE a.c1 = a2.c1 GROUP BY c1);
delete from a where c2 in (select c2 from tmp1 where a.c1=tmp1.c1);
END;你call这个存储过程就删了你不要的数据了。
mysql> select * from test;
+----+------+------+
| id | a | b |
+----+------+------+
| 36 | A | A001 |
| 37 | A | A002 |
| 38 | A | A003 |
| 39 | B | A003 |
| 40 | C | A004 |
| 41 | D | A005 |
| 42 | D | A006 |
+----+------+------+delete from test where id=any(select id from (select id,a,b from test where b not in (select max(b) b from test test1 where test.a=test1.a group by a))D);
+----+------+------+
| id | a | b |
+----+------+------+
| 38 | A | A003 |
| 39 | B | A003 |
| 40 | C | A004 |
| 42 | D | A006 |
+----+------+------+