表结构.name number a 2
a 1
b 4
c 1
d 1我要删除 number总和<=3 的所有记录,
例如上述记录的话,一删除,就剩余b 4
一条.
a 1
b 4
c 1
d 1我要删除 number总和<=3 的所有记录,
例如上述记录的话,一删除,就剩余b 4
一条.
解决方案 »
- 请mysql 存储过程专家解答
- mysql外键
- 某字段的数据源来源于多个表怎么办?
- 如何导入mysql的数据库
- 刚刚接触mysql问一个小问题马上给分
- ISNULL(t1.ac,0) as a1 在MYSQL里怎么写
- ERROR 1044: Access denied for user: ''@'localhost' to database 'testdb'
- 求助:多表视图如何创建
- sql语句在mysql cluster执行时间过长?
- mysql存储过程中如何求出数据表统计列所占百分比
- mysql Manager 导出sql文件··急啊~~!
- 如何输出存储过程中的SQL语句,为了查看这个语句是否正确
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表中符合条件的记录。