跪求一条查询语句
表A
flag begin_datetime end_datetime
0 2012-04-01 12:00 2012-04-01 13:25
1 2012-04-01 13:25 2012-04-01 15:24
0 2012-04-01 15:24 2012-04-01 16:20
表B
d_datetime name_oid
2012-04-01 12:00 124520
2012-04-01 12:01 2245454
2012-04-01 12:02 15454
,...
2012-04-01 13:25 121212
2012-04-01 13:26 454544
2012-04-01 13"27 1524541
...
2012-04-01 15:24 4415454
2012-04-01 15:26 454545
2012-04-01 16:00 45454
....
2012-04-01 16:20 1245454
如果表Aflag＝＝0时，查询的记录对应表B的时间只取第一条，最终结构要求如下
d_datetime name_oid flag
2012-04-01 12:00 124520 0
2012-04-01 13:25 121212 1
2012-04-01 13:26 454544 1
2012-04-01 13"27 1524541 1
....(中间还有很多行省略了)
2012-04-01 15:24 4415454 1
2012-04-01 15:24 1245454 0
[解决办法]
--表Acreate table tab1(flag int , begin_datetime datetime, end_datetime datetime)insert into tab1select 0, '2012-04-01 12:00','2012-04-01 13:25' union allselect 1, '2012-04-01 13:25','2012-04-01 15:24' union allselect 0, '2012-04-01 15:24','2012-04-01 16:20' create table tab2(d_datetime datetime, name_oid varchar(20))insert into tab2select '2012-04-01 12:00','124520' union allselect '2012-04-01 12:01','2245454' union allselect '2012-04-01 12:02','15454' union all--,...select '2012-04-01 13:25','121212' union allselect '2012-04-01 13:26','454544' union allselect '2012-04-01 13:27','1524541' union all--...select '2012-04-01 15:24','4415454' union allselect '2012-04-01 15:26','454545' union allselect '2012-04-01 16:00','45454' union all--....select '2012-04-01 16:20','1245454' select * from tab1--d_datetime name_oid flag--2012-04-01 12:00 124520 0--2012-04-01 13:25 121212 1--2012-04-01 13:26 454544 1--2012-04-01 13"27 1524541 1--2012-04-01 15:24 4415454 1--2012-04-01 15:24 1245454 0;with cte as ( select d_datetime,name_oid,flag from tab2 t2 inner join tab1 t1 on t2.d_datetime=begin_datetime where t1.flag=0 union all select d_datetime,name_oid,1 from tab2 t2 where exists (select 1 from tab1 t1 where t2.d_datetime between begin_datetime and end_datetime and flag=1 ))select * from cte order by d_datetime/*d_datetime name_oid flag----------------------- -------------------- -----------2012-04-01 12:00:00.000 124520 02012-04-01 13:25:00.000 121212 12012-04-01 13:26:00.000 454544 12012-04-01 13:27:00.000 1524541 12012-04-01 15:24:00.000 4415454 02012-04-01 15:24:00.000 4415454 1(6 行受影响)*/
