delete from tb where exists(select [name] from tb a where a.[name]=tb.name group by [name] having(sum(number))<=3)
create table tb([name] varchar(20),number int) go insert into tb select 'a',2 union all select 'a',1 union all select 'b',4 union all select 'c',1 union all select 'd',1 go select * from tb; select [name] from tb group by [name] having(sum(number))<=3 delete from tb where exists(select [name] from tb a where a.[name]=tb.name group by [name] having(sum(number))<=3) select * from tb; go drop table tb; go/*结果 name number -------------------- ----------- a 2 a 1 b 4 c 1 d 1 name -------------------- a c d name number -------------------- ----------- b 4 */
delete a from table1 a,(select name from table1 group by name having sum(number)<=3) b where a.name=b.name
select * from Tb where exists (select name,sum(number)as num from Tb group by name having num>3)
select * from Tb where exists (select name,sum(number)as num from Tb group by name having num>3)
不行哦用MYSQL下执行会出错!!。
mysql> select * from t_seadaughter; +------+--------+ | name | number | +------+--------+ | a | 2 | | a | 1 | | b | 4 | | c | 1 | | d | 1 | +------+--------+ 5 rows in set (0.02 sec)mysql> delete a from t_seadaughter a,(select name from t_seadaughter group by na me having sum(number)<=3) b where a.name=b.name; Query OK, 4 rows affected (0.09 sec)mysql> select * from t_seadaughter; +------+--------+ | name | number | +------+--------+ | b | 4 | +------+--------+ 1 row in set (0.00 sec)mysql>
这怎么解释呢? 这是MYSQL自身的语法啊。delete a from a,b where a.id=b.id 删除A表中符合条件的记录。
go
insert into tb
select 'a',2
union all select 'a',1
union all select 'b',4
union all select 'c',1
union all select 'd',1
go
select * from tb;
select [name] from tb group by [name] having(sum(number))<=3
delete from tb where exists(select [name] from tb a where a.[name]=tb.name group by [name] having(sum(number))<=3)
select * from tb;
go
drop table tb;
go/*结果
name number
-------------------- -----------
a 2
a 1
b 4
c 1
d 1
name
--------------------
a
c
d
name number
-------------------- -----------
b 4
*/
select * from Tb where exists (select name,sum(number)as num from Tb group by name having num>3)
+------+--------+
| name | number |
+------+--------+
| a | 2 |
| a | 1 |
| b | 4 |
| c | 1 |
| d | 1 |
+------+--------+
5 rows in set (0.02 sec)mysql> delete a from t_seadaughter a,(select name from t_seadaughter group by na
me having sum(number)<=3) b where a.name=b.name;
Query OK, 4 rows affected (0.09 sec)mysql> select * from t_seadaughter;
+------+--------+
| name | number |
+------+--------+
| b | 4 |
+------+--------+
1 row in set (0.00 sec)mysql>
这是MYSQL自身的语法啊。delete a from a,b where a.id=b.id 删除A表中符合条件的记录。