表一 字段:id、Cid(对应表二中的id)
表二 字段:id、uptime (时间型)
要删除表二中 uptime 为15天前的id对应的 表一中的Cid的行
------解决方案--------------------
delete 表一 from 表二 where 表一.cid=表二id and datediff(dd,uptime,getdate())>=15
试一下看行不行,没测试数据
------解决方案--------------------
delete table1 from table1 a,table2 b where a.cid = b.id and datediff(day,b.uptime,getdate()) > 15
------解决方案--------------------
- SQL code
--> 测试数据:[tbl1]if object_id('[tbl1]') is not null drop table [tbl1]create table [tbl1]([id] int,[title] varchar(17))insert [tbl1]select 1,'2012星光大道:张三' union allselect 2,'2012星光大道:李四' union allselect 3,'2013星光大道:王五'goif object_id('[tbl2]') is not null drop table [tbl2]create table [tbl2]([cid] int,[date] date)goinsert tbl2select 1,'2012-03-12' union allselect 3,'2012-02-01'delete tbl1 from tbl2 where tbl1.id=tbl2.cid and DATEDIFF(DD,tbl2.[date],GETDATE())>=15select * from tbl1--cid=3数据对用的日期距离当前日期超过15天,删除成功/*id title1 2012星光大道:张三2 2012星光大道:李四*/
------解决方案--------------------
------解决方案--------------------
- SQL code
delete afrom table1 as awhere exists(select 1 from Table2 where uptime<cnvert(varchar(10),getdate()-15,120) and ID=a.Cid)delete Table2 where uptime<cnvert(varchar(10),getdate()-15,120)
------解决方案--------------------
先删除表1的数据再删除表2