表结构
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都记录
查出如下数据:
红色字体
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都记录
查出如下数据:
红色字体
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
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
GoSelect * 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是个比较复杂的查询,所以将查询结果放在临时表做为tb,速度不错
还试了种用游标的方法,也可以实现,比上面的方法慢些,不好。