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 '2011-12-12 11:11:31',1
insert into tb select '2011-12-12 11:11:21',2
insert into tb select '2011-12-12 11:11:11',1
insert into tb select '2011-12-12 11:11:01',1
insert into tb select '2011-12-12 11:10:51',1
insert into tb select '2011-12-12 11:10:41',1
insert into tb select '2011-12-12 11:10:31',2
insert into tb select '2011-12-12 11:10:21',1
insert into tb select '2011-12-12 11:10:11',2
insert into tb select '2011-12-12 11:10:01',1
insert into tb select '2011-12-12 11:09:51',1
go
select * from tb where t_type=2
union all
select * from tb a where t_type=1 and exists(
select * from tb b where t_date<a.t_date and t_type=2
and not exists(select 1 from tb where t_date>b.t_date and t_date<a.t_date
))
union all
select * from tb a where not exists(select 1 from tb where t_date<a.t_date)
union all
select * 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 1
2011-12-12 11:11:31.000 1
2011-12-12 11:11:21.000 2
2011-12-12 11:10:41.000 1
2011-12-12 11:10:31.000 2
2011-12-12 11:10:21.000 1
2011-12-12 11:10:11.000 2
2011-12-12 11:09:51.000 1

(8 行受影响)

*/
go
drop table tb

------解决方案--------------------
SQL code
use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T1') is null
    drop table #T1
Go
Create table #T1([T_date] Datetime,[T_Type] int)
Insert #T1
select '2011-12-12 11:11:51',1 union all
select '2011-12-12 11:11:41',1 union all
select '2011-12-12 11:11:31',1 union all
select '2011-12-12 11:11:21',2 union all
select '2011-12-12 11:11:11',1 union all
select '2011-12-12 11:11:01',1 union all
select '2011-12-12 11:10:51',1 union all
select '2011-12-12 11:10:41',1 union all
select '2011-12-12 11:10:31',2 union all
select '2011-12-12 11:10:21',1 union all
select '2011-12-12 11:10:11',2 union all
select '2011-12-12 11:10:01',1 union all
select '2011-12-12 11:09:51',1
Go

Select * from #T1  AS a  WHERE T_Type=2
UNION 
SELECT b.* 
FROM #T1 AS a 
OUTER APPLY
(SELECT TOP 1 * FROM #T1 WHERE T_Type=1 AND [T_date]<a.[T_date]) AS b
WHERE a.T_Type=2
ORDER BY 1,2
/*
T_date    T_Type
2011-12-12 11:10:01.000    1
2011-12-12 11:10:11.000    2
2011-12-12 11:10:21.000    1
2011-12-12 11:10:31.000    2
2011-12-12 11:11:11.000    1
2011-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……