create table user(
id int auto_increment primary key,
name varchar(20),
age int
);
insert into user values(1,'lucy',22);
insert into user values(2,'lucy',22);
insert into user values(3,'tom',22);
insert into user values(4,'tom',22);
insert into user values(5,'tom',22);
insert into user values(6,'jerry',22);
insert into user values(7,'scott',22);delete from user where id not in
(
select id from user
group by name,age
)
;有错误。求解
id int auto_increment primary key,
name varchar(20),
age int
);
insert into user values(1,'lucy',22);
insert into user values(2,'lucy',22);
insert into user values(3,'tom',22);
insert into user values(4,'tom',22);
insert into user values(5,'tom',22);
insert into user values(6,'jerry',22);
insert into user values(7,'scott',22);delete from user where id not in
(
select id from user
group by name,age
)
;有错误。求解
(
select MAX(id) from user
group by name,age
)
;逻辑有问题
delete a from tt a inner join (select name,age from tt group by name,age having count(*)>=2)b on a.name=b.name and a.age=b.age
select id from
(select id from user a where not exists(select 1 from user b where a.name=b.name and a.age=b.age and a.id<b.id)) tt)
-> id int auto_increment primary key,
-> name varchar(20),
-> age int
-> );
Query OK, 0 rows affected (0.00 sec)mysql> insert into user values(1,'lucy',22);
Query OK, 1 row affected (0.00 sec)mysql> insert into user values(2,'lucy',22);
Query OK, 1 row affected (0.00 sec)mysql> insert into user values(3,'tom',22);
Query OK, 1 row affected (0.00 sec)mysql> insert into user values(4,'tom',22);
Query OK, 1 row affected (0.00 sec)mysql> insert into user values(5,'tom',22);
Query OK, 1 row affected (0.00 sec)mysql> insert into user values(6,'jerry',22);
Query OK, 1 row affected (0.00 sec)mysql> insert into user values(7,'scott',22);
Query OK, 1 row affected (0.00 sec)mysql> select * from user;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | lucy | 22 |
| 2 | lucy | 22 |
| 3 | tom | 22 |
| 4 | tom | 22 |
| 5 | tom | 22 |
| 6 | jerry | 22 |
| 7 | scott | 22 |
+----+-------+------+
7 rows in set (0.00 sec)mysql> delete u from user u left join (select id from user group by name,age ) b on u.id=b.id where b.id is null;
Query OK, 3 rows affected (0.00 sec)mysql> select * from user;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | lucy | 22 |
| 3 | tom | 22 |
| 6 | jerry | 22 |
| 7 | scott | 22 |
+----+-------+------+
4 rows in set (0.00 sec)