34,575
社区成员
发帖
与我相关
我的任务
分享
--xiugai
select [day],data,
data2=(select top 1 data from tab2 where a.[day]<=[day] order by [day] desc)
from tab1 a
select *,(select top 1 Data from Tab2 where a.[Day]>=[Day] order by [Day] desc) as c1 from Tab1 a
select day,data,
data2=(select top 1 data from tab2 where day<=a.day order by day desc)
from tab1 a
create table Tab1([Day] varchar(10),Data int)
insert into tab1 values('20120201', 23)
insert into tab1 values('20120202', 45)
insert into tab1 values('20120203', 12)
insert into tab1 values('20120204', 34)
insert into tab1 values('20120205', 54)
insert into tab1 values('20120206', 75)
insert into tab1 values('20120207', 35)
insert into tab1 values('20120208', 23)
insert into tab1 values('20120209', 22)
insert into tab1 values('20120210', 89)
create table Tab2([Day] varchar(10),Data int)
insert into tab2 values('20120201', 3)
insert into tab2 values('20120204', 5)
insert into tab2 values('20120206', 4)
go
select m.* , data = (select top 1 data from tab2 n where n.[Day] <= m.[Day] order by n.[day] desc) from tab1 m
drop table tab1 , tab2
/*
Day Data data
---------- ----------- -----------
20120201 23 3
20120202 45 3
20120203 12 3
20120204 34 5
20120205 54 5
20120206 75 4
20120207 35 4
20120208 23 4
20120209 22 4
20120210 89 4
(所影响的行数为 10 行)
*/
--> 测试数据:[tbl1]
if object_id('[tbl1]') is not null drop table [tbl1]
create table [tbl1]([Day] datetime,[Data] int)
insert [tbl1]
select '20120201',23 union all
select '20120202',45 union all
select '20120203',12 union all
select '20120204',34 union all
select '20120205',54 union all
select '20120206',75 union all
select '20120207',35 union all
select '20120208',23 union all
select '20120209',22 union all
select '20120210',89
--> 测试数据:[tbl2]
if object_id('[tbl2]') is not null drop table [tbl2]
create table [tbl2]([Day] datetime,[Data] int)
insert [tbl2]
select '20120201',3 union all
select '20120204',5 union all
select '20120206',4
select c.daya as [Day],c.[Data],d.[Data] from(
select a.[Day] as daya,a.[Data],case
when a.[Day]<'20120204' then 1
when a.[Day] between '20120204' and '20120205' then 2
else 3 end as [state] from tbl1 a)c
inner join(select *,
case when a.[Day]='20120201' then 1
when a.[Day]='20120204' then 2
when a.[Day]='20120206' then 3
else 0 end as [state] from tbl2 a)d
on c.[state]=d.[state]
/*
Day Data Data
2012-02-01 00:00:00.000 23 3
2012-02-02 00:00:00.000 45 3
2012-02-03 00:00:00.000 12 3
2012-02-04 00:00:00.000 34 5
2012-02-05 00:00:00.000 54 5
2012-02-06 00:00:00.000 75 4
2012-02-07 00:00:00.000 35 4
2012-02-08 00:00:00.000 23 4
2012-02-09 00:00:00.000 22 4
2012-02-10 00:00:00.000 89 4
*/