ORDER_NO OPERATION_NO OPERATION_DESCRIPTION WORK_CENTER_NO OP_ID
10 10 剪板 N0203 101
10 30 校正 N0205 102
10 40 打磨 N0213 103
10 50 外协抛光 W0006 104
11 10 剪板 N0203 111
11 30 校正 N0205 112
11 40 打磨 N0213 113
11 50 外协抛光 W0006 114
求上表中WORK_CENTER_NO列有W前缀记录的上一记录,若没有上一记录则是W行本身.
即同一订单ORDER_NO(订单号)的WORK_CENTER_NO为W开始上一记录,可从WORK_CENTER_NO为W的OPERATION_NO次小的记录就是上一记录.得到如下表
ORDER_NO OPERATION_NO OPERATION_DESCRIPTION WORK_CENTER_NO OP_ID
10 40 打磨 N0213 103
10 50 外协抛光 W0006 104
11 10 剪板 N0203 111
11 50 外协抛光 W0006 114
------解决方案--------------------
create table tb(ORDER_NO int,OPERATION_NO int ,OPERATION_DESCRIPTION varchar(10),WORK_CENTER_NO varchar(10),OP_ID int)
insert into tb values(10,10, '剪板 ', 'N0203 ',101)
insert into tb values(10,30, '校正 ', 'N0205 ',102)
insert into tb values(10,40, '打磨 ', 'N0213 ',103)
insert into tb values(10,50, '外协抛光 ', 'W0006 ',104)
insert into tb values(11,10, '剪板 ', 'N0203 ',111)
insert into tb values(11,30, '校正 ', 'N0205 ',112)
insert into tb values(11,40, '打磨 ', 'N0213 ',113)
insert into tb values(11,50, '外协抛光 ', 'W0006 ',114)
go
select a.* from tb a,
(
select order_no , max(operation_no) operation_no from tb where left(work_center_no,1) <> 'W ' group by order_no
) b
where a.order_no = b.order_no and a.operation_no = b.operation_no
union all
select * from tb where left(work_center_no,1) = 'W '
order by a.order_no , a.operation_no
drop table tb
/*
ORDER_NO OPERATION_NO OPERATION_DESCRIPTION WORK_CENTER_NO OP_ID
----------- ------------ --------------------- -------------- -----------
10 40 打磨 N0213 103
10 50 外协抛光 W0006 104
11 40 打磨 N0213 113
11 50 外协抛光 W0006 114
（所影响的行数为 4 行）
*/
------解决方案--------------------
declare @Test table (ORDER_NO int, OPERATION_NO int, OPERATION_DESCRIPTION varchar(20), WORK_CENTER_NO varchar(10), OP_ID int)
insert @Test
select '10 ', '10 ', '剪板 ', 'N0203 ', '101 ' union all
select '10 ', '30 ', '校正 ', 'N0205 ', '102 ' union all
select '10 ', '40 ', '打磨 ', 'N0213 ', '103 ' union all
select '10 ', '50 ', '外协抛光 ', 'W0006 ', '104 ' union all
select '11 ', '10 ', '剪板 ', 'N0203 ', '111 ' union all
select '11 ', '30 ', '校正 ', 'N0205 ', '112 ' union all
select '11 ', '40 ', '打磨 ', 'N0213 ', '113 ' union all
select '11 ', '50 ', '外协抛光 ', 'W0006 ', '114 '
select * from @Test a where left(WORK_CENTER_NO,1)= 'W ' union all
select b.* from @Test a join @Test b on a.ORDER_NO=b.ORDER_NO where left(a.WORK_CENTER_NO,1)= 'W ' and b.OPERATION_NO = (select top 1 OPERATION_NO from @Test where ORDER_NO=a.ORDER_NO and OPERATION_NO <a.OPERATION_NO order by OPERATION_NO desc)
order by order_no,OPERATION_NO
/*
ORDER_NO OPERATION_NO OPERATION_DESCRIPTION WORK_CENTER_NO OP_ID
10 40 打磨 N0213 103
10 50 外协抛光 W0006 104
11 40 打磨 N0213 113
11 50 外协抛光 W0006 114