SQL查询

[lanmengxjh]

表A和表B 得出C
A:C1 C2 C3 C4 C5
a b c 10 2011-04-26 00:14:14
a b d 20 2011-04-26 12:14:14
a b e 20 2011-04-27 00:14:14
.......

B:C1 C2 C3
a 15 2011-04-26 11:53:18.29
a 30 2011-04-27 11:53:18.29
a 30 2011-04-27 12:53:18.29
.......

结果：
C:C1 C2 C3 C4 C5 C6
a b c 10 15 2011-04-26 00:14:14
a b d 20 2011-04-26 12:14:14
a b e 20 60 2011-04-27 00:14:14
........

就是根据表A的相邻行时间段，找出B表中符合这个时间段中的数据。

[叶子]

第二条的C4为空？why？

[lanmengxjh]

因为在2011-04-26 12:14:14 和 2011-04-27 00:14:14 中间B表没有相应的数据。。

[winnerxcf]

一个SQL语句，有点难度。
有个SQL存储过程，存放在临时表内。

[快溜]

select *,c6=(select top 1 C2 from 表B
         where C3 between a.C5 and (select min(c5) from 表A where c5>a.C5))
from 表A a

[叶子]

declare @A table (C1 varchar(1),C2 varchar(1),C3 varchar(1),C4 int,C5 datetime)
insert into @A
select 'a','b','c',10,'2011-04-26 00:14:14' union all
select 'a','b','d',20,'2011-04-26 12:14:14' union all
select 'a','b','e',20,'2011-04-27 00:14:14'

declare @B table (C1 varchar(1),C2 int,C3 datetime)
insert into @B
select 'a',15,'2011-04-26 11:53:18.29' union all
select 'a',30,'2011-04-27 11:53:18.29' union all
select 'a',30,'2011-04-27 12:53:18.29'

;with maco as 
(select row_number() over (order by C3) as rid,* from @A)

select c.C1,c.C2,c.C3,c.C4,
C5=(select sum(C2) from @B d where d.c1=c.c1 and d.C3 between c.C5 AND c.C6)
,C6=c.C5 from ( select a.*,isnull(b.C5,'9999-12-31') as C6 
from maco a left join maco b on a.rid=b.rid-1
) c

/*
C1   C2   C3   C4          C5          C6
---- ---- ---- ----------- ----------- -----------------------
a    b    c    10          15          2011-04-26 00:14:14.000
a    b    d    20          NULL        2011-04-26 12:14:14.000
a    b    e    20          60          2011-04-27 00:14:14.000
*/

[快溜]

 declare @A table (C1 varchar(1),C2 varchar(1),C3 varchar(1),C4 int,C5 datetime)
insert into @A
select 'a','b','c',10,'2011-04-26 00:14:14' union all
select 'a','b','d',20,'2011-04-26 12:14:14' union all
select 'a','b','e',20,'2011-04-27 00:14:14'

declare @B table (C1 varchar(1),C2 int,C3 datetime)
insert into @B
select 'a',15,'2011-04-26 11:53:18.29' union all
select 'a',30,'2011-04-27 11:53:18.29' union all
select 'a',30,'2011-04-27 12:53:18.29'


select *,c6=(select sum(C2) from @B
         where C3 between a.C5 and isnull((select min(c5) from @A where c5>a.C5),'9999-12-31'))
from @A a
/*
C1   C2   C3   C4          C5                      c6
---- ---- ---- ----------- ----------------------- -----------
a    b    c    10          2011-04-26 00:14:14.000 15
a    b    d    20          2011-04-26 12:14:14.000 NULL
a    b    e    20          2011-04-27 00:14:14.000 60

[lanmengxjh]

佩服一下，然后结贴。