求上SQL上一记录值
ORDER_NOOPERATION_NOOPERATION_DESCRIPTIONWORK_CENTER_NOOP_ID
1010剪板N0203101
1030校正N0205102
1040打磨N0213103
1050外协抛光W0006104
1110剪板N0203111
1130校正N0205112
1140打磨N0213113
1150外协抛光W0006114
求上表中WORK_CENTER_NO列有W前缀记录的上一记录,若没有上一记录则是W行本身.
即同一订单ORDER_NO(订单号)的WORK_CENTER_NO为W开始上一记录,可从WORK_CENTER_NO为W的OPERATION_NO次小的记录就是上一记录.得到如下表
ORDER_NOOPERATION_NOOPERATION_DESCRIPTIONWORK_CENTER_NOOP_ID
1040打磨N0213103
1050外协抛光W0006104
1110剪板N0203111
1150外协抛光W0006114
[解决办法]
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_NOOPERATION_NOOPERATION_DESCRIPTIONWORK_CENTER_NOOP_ID
1040打磨N0213103
1050外协抛光W0006104
1140打磨N0213113
1150外协抛光W0006114
（所影响的行数为 4 行）
*/
