筛选数据查询解决方案_Sql Server

筛选数据查询
表结构
T_date T_Type
2011-12-12 11:11:51 1
2011-12-12 11:11:41 1
2011-12-12 11:11:31 1
2011-12-12 11:11:21 2
2011-12-12 11:11:11 1
2011-12-12 11:11:01 1
2011-12-12 11:10:51 1
2011-12-12 11:10:41 1
2011-12-12 11:10:31 2
2011-12-12 11:10:21 1
2011-12-12 11:10:11 2
2011-12-12 11:10:01 1
2011-12-12 11:09:51 1

要求：所有T_Type为2的数据都要查出，为1的查出最接近2的（大于上一个2的最小时间），最后1条不管1-2都记录
查出如下数据：
红色字体

------解决方案--------------------

SQL code

create table tb(T_date datetime,T_Type int)insert into tb select '2011-12-12 11:11:51',1insert into tb select '2011-12-12 11:11:41',1insert into tb select '2011-12-12 11:11:31',1insert into tb select '2011-12-12 11:11:21',2insert into tb select '2011-12-12 11:11:11',1insert into tb select '2011-12-12 11:11:01',1insert into tb select '2011-12-12 11:10:51',1insert into tb select '2011-12-12 11:10:41',1insert into tb select '2011-12-12 11:10:31',2insert into tb select '2011-12-12 11:10:21',1insert into tb select '2011-12-12 11:10:11',2insert into tb select '2011-12-12 11:10:01',1insert into tb select '2011-12-12 11:09:51',1goselect * from tb where t_type=2union allselect * from tb a where t_type=1 and exists(select * from tb b where t_date<a.t_date and t_type=2and not exists(select 1 from tb where t_date>b.t_date and t_date<a.t_date))union allselect * from tb a where not exists(select 1 from tb where t_date<a.t_date)union allselect * from tb a where not exists(select 1 from tb where t_date>a.t_date)order by t_date desc/*T_date                  T_Type----------------------- -----------2011-12-12 11:11:51.000 12011-12-12 11:11:31.000 12011-12-12 11:11:21.000 22011-12-12 11:10:41.000 12011-12-12 11:10:31.000 22011-12-12 11:10:21.000 12011-12-12 11:10:11.000 22011-12-12 11:09:51.000 1(8 行受影响)*/godrop table tb
------解决方案--------------------
SQL codeuse Tempdbgo--> -->  if not object_id(N'Tempdb..#T1') is null    drop table #T1GoCreate table #T1([T_date] Datetime,[T_Type] int)Insert #T1select '2011-12-12 11:11:51',1 union allselect '2011-12-12 11:11:41',1 union allselect '2011-12-12 11:11:31',1 union allselect '2011-12-12 11:11:21',2 union allselect '2011-12-12 11:11:11',1 union allselect '2011-12-12 11:11:01',1 union allselect '2011-12-12 11:10:51',1 union allselect '2011-12-12 11:10:41',1 union allselect '2011-12-12 11:10:31',2 union allselect '2011-12-12 11:10:21',1 union allselect '2011-12-12 11:10:11',2 union allselect '2011-12-12 11:10:01',1 union allselect '2011-12-12 11:09:51',1GoSelect * from #T1  AS a  WHERE T_Type=2UNION SELECT b.* FROM #T1 AS a OUTER APPLY(SELECT TOP 1 * FROM #T1 WHERE T_Type=1 AND [T_date]<a.[T_date]) AS bWHERE a.T_Type=2ORDER BY 1,2/*T_date    T_Type2011-12-12 11:10:01.000    12011-12-12 11:10:11.000    22011-12-12 11:10:21.000    12011-12-12 11:10:31.000    22011-12-12 11:11:11.000    12011-12-12 11:11:21.000    2*/
------解决方案--------------------
探讨
这个方法数据是对的，但是，假如tb的数据量是6000左右呢？效率会不会很低？

------解决方案--------------------
探讨

引用:
SQL code
create table tb(T_date datetime,T_Type int)
insert into tb select '2011-12-12 11:11:51',1
insert into tb select '2011-12-12 11:11:41',1
insert into tb select '20……