如何使用sql语句查找上下条符合实际需求的数据
最近做一个程序，需要上下查找需要满足条件的信息，数据如下：
其中TA001为单别，TA002为单号，TA003为加工顺序，TA004为工艺编号，TA006为车间编号，TA007为车间名称，TA031为完工日期，UDF05为是否为关键工艺
TA001TA002TA003TA004TA006TA007TA031UDF05
5111307160303207610车间二20130826Y
5111307160304107610车间二
5111307160304201910车间二
5111307160304304410车间二
5111307160304405310车间二20130826Y
5111307160304501513车间三20130828Y
5111307160305101513车间三
51113071603051515413车间三20130831Y
5111307160313207610车间二20130807Y
5111307170001801213车间三
5111307170001901313车间三20130823Y
51113071700011001513车间三
create table cl
(TA001 int, TA002 int,TA003 int,TA004 int,TA006 int,TA007 varchar(10),
TA031 varchar(8),UDF05 varchar(2))
insert into cl
select 511,1307160303,10,1,11,'车间一','20130722','Y' union all
select 511,1307160303,20,76,10,'车间二','20130826','Y' union all
select 511,1307160304,10,76,10,'车间二','','' union all
select 511,1307160304,20,19,10,'车间二', '', '' union all
select 511,1307160304,30,44,10,'车间二','','' union all
select 511,1307160304,40,53,10,'车间二','20130826','Y' union all
select 511,1307160304,50,15,13,'车间三','20130828','Y' union all
select 511,1307160305,10,15,13,'车间三','','' union all
select 511,1307160305,15,154,13,'车间三','20130831','Y' union all
select 511,1307160311,10,1,11,'车间一','20130724','Y' union all
select 511,1307160313,10,1,11,'车间一','20130717','Y' union all
select 511,1307160313,20,76,10,'车间二','20130807','Y' union all
select 511,1307170001,80,12,13,'车间三','','' union all
select 511,1307170001,90,13,13,'车间三','20130823','Y' union all
select 511,1307170001,100,15,13,'车间三','','' union all
select 511,1307170002,10,1,11,'车间一','20130922','Y'
-- 更新
update a
set a.TA031=case when d.TA031 is not null then d.TA031
else '' end
from cl a
inner join
(select TA001,TA002,TA006,max(TA003) 'maxTA003'
from cl
group by TA001,TA002,TA006) b on a.TA001=b.TA001 and a.TA002=b.TA002 and a.TA006=b.TA006
outer apply
(select c.TA031 from cl c
where c.TA001=a.TA001 and c.TA002=a.TA002 and c.TA006=a.TA006
and c.UDF05='Y' and
(c.TA003=b.maxTA003
or
c.TA003 in(a.TA003+10,a.TA003-10))) d
where a.TA031=''
-- 结果
select * from cl
/*
TA001 TA002 TA003 TA004 TA006 TA007 TA031 UDF05
----------- ----------- ----------- ----------- ----------- ---------- -------- -----
511 1307160303 10 1 11 车间一 20130722 Y
511 1307160303 20 76 10 车间二 20130826 Y
511 1307160304 10 76 10 车间二 20130826
511 1307160304 20 19 10 车间二 20130826
511 1307160304 30 44 10 车间二 20130826
511 1307160304 40 53 10 车间二 20130826 Y
511 1307160304 50 15 13 车间三 20130828 Y
511 1307160305 10 15 13 车间三 20130831
511 1307160305 15 154 13 车间三 20130831 Y
511 1307160311 10 1 11 车间一 20130724 Y
511 1307160313 10 1 11 车间一 20130717 Y
511 1307160313 20 76 10 车间二 20130807 Y
511 1307170001 80 12 13 车间三 20130823
511 1307170001 90 13 13 车间三 20130823 Y
511 1307170001 100 15 13 车间三 20130823
511 1307170002 10 1 11 车间一 20130922 Y
(16 row(s) affected)
*/
if object_id('cl') is not null
drop table cl
go
create table cl
(TA001 int, TA002 int,TA003 int,TA004 int,TA006 int,TA007 varchar(10),
TA031 varchar(8),UDF05 varchar(2))
insert into cl
select 511,1307160303,10,1,11,'车间一','20130722','Y' union all
select 511,1307160303,20,76,10,'车间二','20130826','Y' union all
select 511,1307160304,10,76,10,'车间二','','' union all
select 511,1307160304,20,19,10,'车间二', '', '' union all
select 511,1307160304,30,44,10,'车间二','','' union all
select 511,1307160304,40,53,10,'车间二','20130826','Y' union all
select 511,1307160304,50,15,13,'车间三','20130828','Y' union all
select 511,1307160305,10,15,13,'车间三','','' union all
select 511,1307160305,15,154,13,'车间三','20130831','Y' union all
select 511,1307160311,10,1,11,'车间一','20130724','Y' union all
select 511,1307160313,10,1,11,'车间一','20130717','Y' union all
select 511,1307160313,20,76,10,'车间二','20130807','Y' union all
select 511,1307170001,80,12,13,'车间三','','' union all
select 511,1307170001,90,13,13,'车间三','20130823','Y' union all
select 511,1307170001,100,15,13,'车间三','','' union all
select 511,1307170002,10,1,11,'车间一','20130922','Y'
;with t
as
(
select *,
row_number() over(partition by ta001,ta002
order by @@servername) as rownum
from cl
),
tt
as
(
select t1.*,
(select top 1 ta031 from t t2
where t2.ta001 = t1.ta001
and t2.ta002 = t1.ta002
and t2.rownum > t1.rownum
and t2.ta031 <> ''
and t1.ta031 = ''
and t2.udf05 = 'Y'
order by t2.rownum asc) as next_row,
(select top 1 ta031 from t t2
where t2.ta001 = t1.ta001
and t2.ta002 = t1.ta002
and t2.rownum < t1.rownum
and t2.ta031 <> ''
and t1.ta031 = ''
and t2.udf05 = 'Y'
order by t2.rownum desc) as prior_row
from t t1
)
select ta001,ta002,
ta003,ta004,ta006,ta007,
ta031,udf05,
temp_ta031 as 补全的ta031
from
(
select ta001,ta002,
ta003,ta004,ta006,ta007,
ta031,udf05,
case when ta031 = ''
then isnull(next_row,prior_row)
else ta031
end as temp_ta031 --已经补全,作为过滤条件
from tt
)cl
where cl.temp_ta031 >='2013-08-01' and
cl.temp_ta031 < '2013-09-01'
/*
ta001 ta002 ta003 ta004 ta006 ta007 ta031 udf05 补全的ta031
----------- ----------- ----------- ----------- ----------- ---------- -------- ----- --------
511 1307160303 20 76 10 车间二 20130826 Y 20130826
511 1307160304 10 76 10 车间二 20130826
511 1307160304 20 19 10 车间二 20130826
511 1307160304 30 44 10 车间二 20130826
511 1307160304 40 53 10 车间二 20130826 Y 20130826
511 1307160304 50 15 13 车间三 20130828 Y 20130828
511 1307160305 10 15 13 车间三 20130831
511 1307160305 15 154 13 车间三 20130831 Y 20130831
511 1307160313 20 76 10 车间二 20130807 Y 20130807
511 1307170001 80 12 13 车间三 20130823
511 1307170001 90 13 13 车间三 20130823 Y 20130823
511 1307170001 100 15 13 车间三 20130823*/
