一个简单查询,请帮我看看(30分)

use tempdb<br>go<br><br>create table #t1(id &nbsp;int identity,B1 &nbsp;varchar(5),B2 &nbsp;varchar(5))<br>create table #t2(id &nbsp;int identity,[Date] &nbsp;datetime)<br><br>insert #t1 select '00:00','02:00'<br>union all select '05:00','07:00'<br>union all select '09:00','11:00'<br>union all select '12:00','14:00'<br>union all select '15:00','18:00'<br>union all select '18:00','19:00'<br>union all select '20:00','23:00'<br>union all select '23:00','23:59'<br><br>insert #t2 select '04:22'<br>union all select '09:23'<br>union all select '13:00'<br><br><br>--如何查询出 #t2中不在#t1(B1,B2)范围内的记录啊？<br><br><br>drop table #t1,#t2

select from #t2 not in <br>(select from t1)

select * from #t2 where id not in(select distinct id from #t2 a,(select b1,b2 from #t1) as b<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where convert(varchar(5),[date],108) between b.b1 and b.b2)<br><br>自己解决了.浪费我的30分啊..

借此题再问个问题<br>--测试<br>use tempdb<br>go<br><br>create table #t1(id int identity,K1 varchar(10),name varchar(10)) &nbsp;--员工表<br>create table #t2(id int identity,K1 varchar(10), T3 int,[date] datetime) &nbsp; --记录表<br>create table #t3(id int,[type] varchar(2)) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;--设备表<br><br>--三表关系(#t1与#t2 K1=K1 &nbsp;#t2与#t3 T3=id)<br><br>insert #t1 select 'A1','张三'<br>union all select 'A2','李四'<br>union all select 'A3','王麻子'<br><br>insert #t2 select 'A1','1','2006-04-05 10:21:34'<br>union all select 'A1','1','2007-09-05 09:21:34'<br>union all select 'A1','2','2008-03-01 10:21:34'<br>union all select 'A2','1','2006-04-05 10:21:34'<br>union all select 'A2','2','2007-09-05 09:21:34'<br>union all select 'A2','1','2008-03-01 10:21:34'<br>union all select 'A3','2','2006-04-05 10:21:34'<br>union all select 'A3','2','2007-09-05 09:21:34'<br>union all select 'A3','2','2008-03-01 10:21:34'<br>union all select 'A3','2','2006-04-05 10:21:34'<br><br><br>insert #t3 select 1,'进'<br>union all select 2,'出'<br><br><br>select * from #t1<br>select * from #t2<br>select * from #t3<br><br><br>/*<br>&lt;date&gt; &nbsp;输入日期<br>查询出在&lt;date&gt;之后没有进来的人员以及最近进来时间和上次出去时间<br><br>结果格式 &nbsp; &nbsp;id K1 name 最近进入时间 上次出去时间<br><br><br>--谁知道这个查询怎么写？<br><br><br>*/<br><br>--没有进来的人员<br><br>select a.* from #t1 a where a.K1 not in (select a.K1 from #t1 a inner join #t2 b on a.K1=b.K1 inner join <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; #t3 c on b.t3=c.id and [type]='进' and convert(varchar(10),b.[date],120)&gt; '2008-02-01')<br><br>--完整的应该怎么写，请指定<br><br>drop table #t1,#t2<br><br><br>大家帮我看看应该如何写这个SQL语句呢?谢谢了...

请帮我看看,怎么没有人回答呢?

自己提前一下