id name age1 a 22
2 a 23
3 b 21
4 b 24
5 b 33
6 c 44要求删除name 总数大于 2的记录,如何做啊delete from a where count(name) >2好象语法不对1 a 22
2 a 23
6 c 44要求得到这个结果
2 a 23
3 b 21
4 b 24
5 b 33
6 c 44要求删除name 总数大于 2的记录,如何做啊delete from a where count(name) >2好象语法不对1 a 22
2 a 23
6 c 44要求得到这个结果
delete from tta a WHERE 2>(SELECT COUNT(*) FROM tta WHERE a.name=NAME)
mysql> create table name(id int, name varchar(11),age int);
Query OK, 0 rows affected (0.14 sec)mysql> insert into name values('1','a','22'),('2','a','23'),('3','b','21'),('4','b','24'),('5','b','33'),('6','c','44');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql> select * from name;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | a | 22 |
| 2 | a | 23 |
| 3 | b | 21 |
| 4 | b | 24 |
| 5 | b | 33 |
| 6 | c | 44 |
+------+------+------+
6 rows in set (0.00 sec)
mysql> select count(name) count,name from name group by name;
+-------+------+
| count | name |
+-------+------+
| 2 | a |
| 3 | b |
| 1 | c |
+-------+------+
3 rows in set (0.00 sec)
mysql> select name from (select count(name) count,name from name group by name) a where a.count>2;
+------+
| name |
+------+
| b |
+------+
1 row in set (0.00 sec)
mysql> delete from name where name in (select name from (select count(name) count,name from name group by name) a where a.count>2);
Query OK, 3 rows affected (0.03 sec)
笨人的思路,见笑