日期 列A 列B 列C
2011-01-02 1 0 1
2011-01-09 0 1 1
2011-01-15 -1 1 0
2011-01-18 1 -1 0有上述数据格式,需要安装输入的日期返回列值的最新状态值,
状态值只有1和-1,0代表无状态信息,例:如果日期条件为 <= 2011-01-16 则结果为:
小于等于1月16日的最新数据位1号的数据,列A 列B的状态分别为 -1 和 1,
列C因为是 0 没有状态信息,需要取它的最近状态信息,最近的状态数据为9号数据,为1,
所以最后结果为:
2011-01-15 -1 1 1如果日期条件为 <= 2011-02-01 则结果为:
2011-01-18 1 -1 1需求就是上面描述这样的,不知道描述的是否清晰,还请各位帮忙想想解决方案,谢谢了!
2011-01-02 1 0 1
2011-01-09 0 1 1
2011-01-15 -1 1 0
2011-01-18 1 -1 0有上述数据格式,需要安装输入的日期返回列值的最新状态值,
状态值只有1和-1,0代表无状态信息,例:如果日期条件为 <= 2011-01-16 则结果为:
小于等于1月16日的最新数据位1号的数据,列A 列B的状态分别为 -1 和 1,
列C因为是 0 没有状态信息,需要取它的最近状态信息,最近的状态数据为9号数据,为1,
所以最后结果为:
2011-01-15 -1 1 1如果日期条件为 <= 2011-02-01 则结果为:
2011-01-18 1 -1 1需求就是上面描述这样的,不知道描述的是否清晰,还请各位帮忙想想解决方案,谢谢了!
--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([日期] datetime,[列A] int,[列B] int,[列C] int)
insert [tbl]
select '2011-01-02',1,0,1 union all
select '2011-01-09',0,1,1 union all
select '2011-01-15',-1,1,0 union all
select '2011-01-18',1,-1,0
select [日期],[列A],[列B],[列C] from(
select *,DATEDIFF(DD,[日期],'2011-01-16') as dd
from tbl)a where dd=(
select MIN(dd) from (select DATEDIFF(DD,[日期],'2011-01-16') as dd
from tbl)d where dd>=0
)日期 列A 列B 列C
2011-01-15 00:00:00.000 -1 1 0
from T,
(
select top 1 列A
from T
where 日期 <= @date and 列A <> 0
order by 日期 desc
) A,
(
select top 1 列B
from T
where 日期 <= @date and 列B <> 0
order by 日期 desc
) B,
(
select top 1 列C
from T
where 日期 <= @date and 列C <> 0
order by 日期 desc
) C
where 日期 <= @date
order by 日期 desc
--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([日期] datetime,[列A] int,[列B] int,[列C] int)
insert [tbl]
select '2011-01-02',1,0,1 union all
select '2011-01-09',0,1,1 union all
select '2011-01-15',-1,1,0 union all
select '2011-01-18',1,-1,0
select [日期],[列A],[列B],[列C] from(
select *,DATEDIFF(DD,[日期],'2011-02-01') as dd
from tbl)a where dd=(
select MIN(dd) from (select DATEDIFF(DD,[日期],'2011-02-01') as dd
from tbl )d where dd>=0
)日期 列A 列B 列C
2011-01-18 00:00:00.000 1 -1 0
create table tb(dt datetime,A int, B int,C int)
insert into tb values('2011-01-02', 1 ,0 ,1)
insert into tb values('2011-01-09', 0 ,1 ,1)
insert into tb values('2011-01-15', -1 ,1 ,0)
insert into tb values('2011-01-18', 1 ,-1 ,0)
godeclare @dt as datetimeset @dt = '2011-01-16'
select m.dt , m.a , m.b ,
(case when m.c = 0 then (select top 1 c from tb where C <> 0 and dt < m.dt order by dt desc) else m.c end) c from
(select top 1 t.dt , t.a , t.b , t.c from tb t where t.dt <= @dt order by t.dt desc) m
/*
dt a b c
------------------------------------------------------ ----------- ----------- -----------
2011-01-15 00:00:00.000 -1 1 1(所影响的行数为 1 行)
*/set @dt = '2011-02-01'
select m.dt, m.a , m.b ,
(case when m.c = 0 then (select top 1 c from tb where C <> 0 and dt < m.dt order by dt desc) else m.c end) c from
(select top 1 t.dt , t.a , t.b , t.c from tb t where t.dt <= @dt order by t.dt desc) m
/*
dt a b c
------------------------------------------------------ ----------- ----------- -----------
2011-01-18 00:00:00.000 1 -1 1(所影响的行数为 1 行)
*/drop table tb