我想找到最接近returndate的前一个sdt,并通过idno获取相关的id_reference,并更新@sampleship表中的id_reeference。id_reference是idno和returndate的唯一数字组合。
我在TSQL中进行了一个查询,是否有任何帮助将其转换为mySQL以满足我的需求,或者是否有其他替代方法。
我使用的是MySQL Workbench 8.0 CE
以下是DDL示例和所需结果。提前谢谢。
create table sampleReturns(idno varchar(35),id varchar(35), returndate
datetime, id_reference varchar(60))
insert into sampleReturns (idno,id, returndate, id_reference )
values ('12345670','123456PH','2022-04-26 00:00:00','2022-04-26 00:00:0012345670'),
('12345678','123456AB','022-01-31 00:00:00',2022-01-31 00:00:0012345678'),
('12345678','123456AB','2022-02-18 00:00:00','2022-02-18 00:00:0012345678'),
('12345678','123456AB','2022-05-25 00:00:00','2022-05-25 00:00:0012345678'),
('12345650','123456TW','2022-03-01 00:00:00','2022-03-01 00:00:0012345650'),
('22345688','223456PK','2022-01-21 00:00:00','2022-01-21 00:00:0022345688'),
('22345688','223456PK','2022-03-08 00:00:00','2022-03-08 00:00:0022345688')
create table @sampleship table(idno varchar(35),id varchar(35), sdt datetime, id_reference varchar(60))
insert into @sampleship table (idno,into, sdt, id_reference )
values ('12345670','123456PH','2020-11-26 00:00:00',NULL),
('12345670','123456PH','2022-04-23 00:00:00',NULL),
('12345670','123456PH','2022-07-25 00:00:00',NULL),
('12345678','123456AB','2022-01-31 00:00:00',NULL),
('12345678','123456AB','2022-08-25 00:00:00',NULL),
('12345678','123456AB','2022-02-22 00:00:00',NULL),
('12345650','123456TW','2022-02-25 00:00:00',NULL),
('22345688','223456PK','2022-01-31 00:00:00',NULL),
('22345688','223456PK','2022-05-25 00:00:00',NULL)
所需结果:@sampleship表
idno | id | sdtid_reference[/tr>||
---|---|---|---|
12345670 | 123456PH | 2020-11-26 00:00:00 | 空 |
12345670 | 12346PH | 2022-04-23 00:00:00 | 2022:04-26 00:00:0012345670 |
12345670 | 123456PH | 2022-07-25 00:00:00 | NULL |
12345678 | 123456AB | 2022-01-31 00:00:00 | 2022:02-18 00:00:0012345678 |
12345678 | 123456AB | 2022-08-25 00:00:00 | 2022:05-25 00:00:0012345678 |
12345678 | 123456AB | 2022-02-22 00:00:00 | 空 |
12345650 | 12346TW | 2022-02-25 00:00:00 | 2022:03-01 00:00:0012345650 |
22345688 | 223456PK | 2022-01-31 00:00:00 | 2022:02-03-08 00:00:0022345688 |
22345688 | 223456PK | 2022-05-25 00:00:00 | 空 |
从逻辑上讲,我认为退货只能发生在发货之后,因此两个发货之间的退货属于两个发货日期中最早的一个。如果发货表终止而没有找到下一个发货(即null(,那么替换now((可能会有所帮助。在mysql中,多表更新语法适用,不需要cte
update shipped
join
(
select s1.*,r.id_reference ridref
from
(select s.*,
case when lead(sdt) over (partition by idno,id order by sdt) is null then now()
else lead(sdt) over (partition by idno,id order by sdt)
end nextsdt
from shipped s
) s1
left join returns r on r.idno = s1.idno and r.id = s1.id and r.returndate between s1.sdt and s1.nextsdt
#order by s1.idno,s1.id,s1.sdt
) x
on x.idno = shipped.idno and x.id = shipped.id and x.sdt = shipped.sdt
set shipped.id_reference = x.ridref;
或者,如果您使用的是8.0 之前的版本
update shipped
join
(
select s1.*, #sdt ,
case when
(select sdt
from shipped s2
where s2.idno = s1.idno and s2.id = s1.id and s2.sdt > s1.sdt order by s2.sdt asc limit 1)
is null then
now()
else
(select sdt
from shipped s2
where s2.idno = s1.idno and s2.id = s1.id and s2.sdt > s1.sdt order by s2.sdt asc limit 1)
end
nextstd ,
r.id_reference newidref
from shipped s1
left join returns r on r.idno = s1.idno and r.id = s1.id and r.returndate between s1.sdt and
case when
(select sdt
from shipped s2
where s2.idno = s1.idno and s2.id = s1.id and s2.sdt > s1.sdt order by s2.sdt asc limit 1)
is null then
now()
else
(select sdt
from shipped s2
where s2.idno = s1.idno and s2.id = s1.id and s2.sdt > s1.sdt order by s2.sdt asc limit 1)
end
order by idno,id,sdt
) x
on x.idno = shipped.idno and x.id = shipped.id and x.sdt = shipped.sdt
set shipped.id_reference = newidref
;