求简单sql语句

[yaozhiyong110]

a表 有id、starttime(timestamp时间类型)、endtime(timestamp时间类型)等

b表有id、starttime(timestamp时间类型)、endtime(timestamp时间类型)等

两表id是一样的 时间可能有交集

求sql语句：

将a表记录删除 条件是b表没有任何记录的时间在a表的时间范围内(无交集) 以a.id = b.id匹配

举例：

a表

1 1001 2012-10-10 10:10:10 2012-10-10 10:20:10

2 1001 2012-10-10 10:30:10 2012-10-10 10:40:10

b表

1 1001 2012-10-10 10:05:10 2012-10-10 10:11:00


这样的话 a表第二条记录将删除

PS：效率越高越好 因为数据可能有几万甚至几十万条 而且这个操作很频繁

谢谢..........

[开启时代]

delete from A where exists(select 1 from B where A.id=B.id and (A.starttime>B.endtime or A.endtime<B.starttime));

[yaozhiyong110]

[Quote=引用 1 楼 的回复:]
delete from A where exists(select 1 from B where A.id=B.id and (A.starttime>B.endtime or A.endtime<B.starttime));
[/Quote]


能解释下吗？ 特别是括号里的条件不明白.. 谢谢

[yaozhiyong110]

[Quote=引用 1 楼 的回复:]
delete from A where exists(select 1 from B where A.id=B.id and (A.starttime>B.endtime or A.endtime<B.starttime));
[/Quote]

a表

1 1001 2012-10-10 10:10:10 2012-10-10 10:20:10

2 1001 2012-10-10 10:30:10 2012-10-10 10:40:10

b表

1 1001 2012-10-10 10:05:10 2012-10-10 10:11:00

2 1001 2012-10-10 10:35:10 2012-10-10 10:41:00


你的语句 这样a的第二条记录会删掉不？ 我等下试试

[yaozhiyong110]

错了 是

引用 1 楼 的回复:
delete from A where exists(select 1 from B where A.id=B.id and (A.starttime>B.endtime or A.endtime<B.starttime));


a表

1 1001 2012-10-10 10:10:10 2012-10-10 10:20:10

2 1001 2012-10-10 10:30:10 2012-10-10 10:40:10

b表

1 1001 2012-10-10 10:05:10 2012-10-10 10:11:00

2 1001 2012-10-10 10:45:10 2012-10-10 10:51:00

[rucypli]

delete from A where exists(select 1 from B where A.id=B.id and (A.starttime>B.endtime or A.endtime<B.starttime));


从A中取出来每一条数据和B比较 如果存在 则删除

[yaozhiyong110]

[Quote=引用 5 楼 的回复:]
delete from A where exists(select 1 from B where A.id=B.id and (A.starttime>B.endtime or A.endtime<B.starttime));


从A中取出来每一条数据和B比较 如果存在 则删除
[/Quote]

这只解释了括号前的意思啊 大哥

[yaozhiyong110]

delete from A where exists(select 1 from B where A.id=B.id and (A.starttime>B.endtime or A.endtime<B.starttime));

这个语句是 把a中的数据 只要和b中无交集 就删除？

[ACMAIN_CHM]

delete from A where NOT exists (select 1 from B where A.id=B.id and A.starttime<B.endtime AND A.endtime>B.starttime);

[liangchengfck1]

[Quote=引用 5 楼 的回复:]
delete from A where exists(select 1 from B where A.id=B.id and (A.starttime>B.endtime or A.endtime<B.starttime));


从A中取出来每一条数据和B比较 如果存在 则删除
[/Quote]0

[yaozhiyong110]

解决了 最后sql语句：

DELETE FROM A WHERE A.endTime IS NOT NULL AND NOT EXISTS (SELECT 1 FROM B WHERE B.startTime IS NOT NULL AND A.id=B.id AND ((B.endtime IS NOT NULL AND A.starttime<B.endtime AND A.endtime>B.starttime) OR (B.endtime IS NULL AND A.endtime>B.starttime)))


谢谢各位...

[yaozhiyong110]

DELETE FROM A WHERE A.endTime IS NOT NULL AND NOT EXISTS (SELECT 1 FROM B WHERE A.id=B.id AND B.startTime IS NOT NULL AND ((B.endtime IS NOT NULL AND A.starttime<B.endtime AND A.endtime>B.starttime) OR (B.endtime IS NULL AND A.endtime>B.starttime)))

id匹配放前面应该效率高点