是这样吗:
select *
from 表
where DATEDIFF(day,getdate(),你的日期字段) = -1 -- 前1天select *
from 表
where DATEDIFF(day,getdate(),你的日期字段) = -2 -- 前2天
select *
from 表
where DATEDIFF(day,getdate(),你的日期字段) = -1 -- 前1天select *
from 表
where DATEDIFF(day,getdate(),你的日期字段) = -2 -- 前2天
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]( [日期] datetime, [状态] int)
insert [a]
select'2014/1/1',0 union all
select'2014/1/2',1 union all
select'2014/1/3',1 union all
select'2014/1/4',0 union all
select'2014/1/5',0 union all
select'2014/1/6',1 union all
select'2014/1/7',1 union all
select'2014/1/8',1 union all
select'2014/1/9',1 union all
select'2014/1/10',1 select convert(varchar(10),a.[日期],120) as [日期] ,a.[状态],convert(varchar(10),max(b.[日期]),120) as [前一个工作日]
from a left join
(
select * from a where [状态] =1
) b on a.[日期]>b.[日期]
group by a.[日期],a.[状态]
order by a.[日期]
日期 状态 前一个工作日
---------- ----------- ----------
2014-01-01 0 NULL
2014-01-02 1 NULL
2014-01-03 1 2014-01-02
2014-01-04 0 2014-01-03
2014-01-05 0 2014-01-03
2014-01-06 1 2014-01-03
2014-01-07 1 2014-01-06
2014-01-08 1 2014-01-07
2014-01-09 1 2014-01-08
2014-01-10 1 2014-01-09
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]( [日期] datetime, [状态] int)
insert [a]
select'2014/1/1',0 union all
select'2014/1/2',1 union all
select'2014/1/3',1 union all
select'2014/1/4',0 union all
select'2014/1/5',0 union all
select'2014/1/6',1 union all
select'2014/1/7',1 union all
select'2014/1/8',1 union all
select'2014/1/9',1 union all
select'2014/1/10',1 select convert(varchar(10),a.[日期],120) as [日期] ,a.[状态],
convert(varchar(10),max(b.[日期1]),120) as [前两个工作日]
from a left join
(
select a.[日期],max(b.[日期]) as [日期1] from a a left join a b
on a.[日期]>b.[日期]
where a.[状态] =1 and b.[状态] =1
group by a.[日期]
) b on a.[日期]>b.[日期]
group by a.[日期],a.[状态]
order by a.[日期]
日期 状态 前两个工作日
---------- ----------- ----------
2014-01-01 0 NULL
2014-01-02 1 NULL
2014-01-03 1 NULL
2014-01-04 0 2014-01-02
2014-01-05 0 2014-01-02
2014-01-06 1 2014-01-02
2014-01-07 1 2014-01-03
2014-01-08 1 2014-01-06
2014-01-09 1 2014-01-07
2014-01-10 1 2014-01-08
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]( [日期] datetime, [状态] int)
insert [a]
select'2014/1/1',0 union all
select'2014/1/2',1 union all
select'2014/1/3',1 union all
select'2014/1/4',0 union all
select'2014/1/5',0 union all
select'2014/1/6',1 union all
select'2014/1/7',1 union all
select'2014/1/8',1 union all
select'2014/1/9',1 union all
select'2014/1/10',1
--建立b表
select *,row_number() over(order by [日期]) as rn into b from a where [状态] =1 select convert(varchar(10),a.[日期],120) as [日期] ,a.[状态],
convert(varchar(10),max(b.[日期1]),120) as [前N个工作日]
from a left join
(
select a.[日期],b.[日期]as [日期1] from
(
select a.[日期],max(b.rn) as rn from b a left join b b
on a.rn-1 >b.rn-2--'-1是参数'
group by a.[日期]
)a left join b b on a.rn=b.rn
) b on a.[日期]>b.[日期]
group by a.[日期],a.[状态]
order by a.[日期]整个活的~~动态的!
日期 状态 前N个工作日
---------- ----------- ----------
2014-01-01 0 NULL
2014-01-02 1 NULL
2014-01-03 1 2014-01-02
2014-01-04 0 2014-01-03
2014-01-05 0 2014-01-03
2014-01-06 1 2014-01-03
2014-01-07 1 2014-01-06
2014-01-08 1 2014-01-07
2014-01-09 1 2014-01-08
2014-01-10 1 2014-01-09