你能告诉我如何删除未知号码的最后一条记录吗(有条件的)?
例如,在这种情况下,我想删除id为6到10的记录。
注:此表和记录不是常量。
+----+-----+---------+
| id | url | emailid |
+----+-----+---------+
| 1 | 10 | 1 |
| 2 | 20 | 0 |
| 3 | 30 | 2 |
| 4 | 40 | 0 |
| 5 | 50 | 10 |
| 6 | 60 | 0 |
| 7 | 70 | 0 |
| 8 | 80 | 0 |
| 9 | 90 | 0 |
| 10 | 100 | 0 |
+----+-----+---------+
谢谢。。。
您似乎想要删除最后一组所有值都为0
的记录。这有点痛苦。你可以找到这样的最小id:
select min(t.id)
from table t
where t.emailid = 0 and
not exists (select 1 from table t2 where t2.id > t.id and t2.emailid <> 0);
逻辑是:查找emailid
为0且没有后续非零的电子邮件ID的所有行。
您可以使用join
:将其放入delete
中
delete t
from table t cross join
(select min(t.id) as first0id
from table t
where t.emailid = 0 and
not exists (select 1 from table t2 where t2.id > t.id and t2.emailid <> 0)
) tmin
where t.id >= tmin.first0id;
您可以像以下一样在查询中使用between
关键字
delete from yourtable where id BETWEEN 6 AND 10
使用此查询
delete from your_table where id between 6 AND 10
由于不是常量,您可以先将起始值和结束值存储在变量中,然后传入查询,例如(在php中)
$start = 6 ;
$end = 10;
查询
"delete from your_table where id between $start AND $end"