MySQL查询以找到制造的SKU项目的状态

发布于 2025-01-24 02:25:05 字数 5346 浏览 3 评论 0原文

和snapdate的组合识别sku。

我有一个延迟表（如下），其中通过mmodel和srno

mmodel  Srno    Snapdate
MX201   C12341  3/21/2021
NM213   L1234   3/21/2021
JK1231  K1561   3/21/2021
KL1234  MJ1234  3/21/2021
MX201   C12341  3/22/2021
NM213   L1234   3/22/2021
OP1231  JK123   3/22/2021
MB321   LK123   3/22/2021
MX201   C12341  3/23/2021
BX378   H1231   3/23/2021
LZ231   MD321   3/23/2021
LO3412  LP1231  3/23/2021
MX201   C12341  3/24/2021
BX378   H1231   3/24/2021
FE221   J6571   3/24/2021

MFG表

mfgdate mmodel  Srno
3/21/2021   JK123   K21310
3/21/2021   KL378   L8941
3/22/2021   JK1231  K1561
3/22/2021   KL1234  MJ1234
3/22/2021   KL918   S21367
3/23/2021   NM213   L1234
3/23/2021   OP1231  JK123
3/23/2021   MB321   LK123
3/23/2021   XC2312  M89321
3/24/2021   LZ231   MD321
3/24/2021   LO3412  LP1231
3/24/2021   KL5612  D3489

上表具有MFGDATE（SKU制造的日期）。现在，我想创建一个状态表（如下），并忽略上面两个表中的第一个日期，然后从第二个日期开始。

状态输出表

Date    mmodel  Srno    SKUComplete     Goodstock   offdelay    NewDelay
3/22/2021   JK1231  K1561   1   0   1   0
3/22/2021   KL1234  MJ1234  1   0   1   0
3/22/2021   KL918   S21367  1   1   0   0
3/22/2021   OP1231  JK123   0   0   0   1
3/22/2021   MB321   LK123   0   0   0   1
3/23/2021   NM213   L1234   1   0   1   0
3/23/2021   OP1231  JK123   1   0   1   0
3/23/2021   MB321   LK123   1   0   1   0
3/23/2021   XC2312  M89321  1   1   0   0
3/23/2021   BX378   H1231   0   0   0   1
3/23/2021   LZ231   MD321   0   0   0   1
3/23/2021   LO3412  LP1231  0   0   0   1
3/24/2021   LO3412  LP1231  1   0   1   0
3/24/2021   KL5612  D3489   1   1   0   0
3/24/2021   FE221   J6571   0   0   0   1

首先查找MFG表，并在MFG表中的SKU标记为Skucomplete 1 ..现在应该在延迟中查找相同的SKU。上一个日期的表（MFGDATE-1）..如果在延迟表中的上一个日期中存在SKU，则OffDelay将成为1个，否则GoodStock将

在MFG表中完成所有SKU之后成为1个特定制造日期的SKU剩下的SKU应该在延迟表中查找相同日期（不匹配的SKU），应将它们输入状态表中，并以新的延迟为1 ..请找到

其他一件事的DDL关心的是，如果特定MFG日期的MFG表中没有SKU，并且SKU仅在该特定MFGDATE的延迟表中在那个日期 延迟表

Create table delay
(mmodel varchar(40),
srno varchar(40),
snapdate date)

insert into delay values
('MX201','C12341','3/21/2021'),
('NM213','L1234','3/21/2021'),
('JK1231','K1561','3/21/2021'),
('KL1234','MJ1234','3/21/2021'),
('MX201','C12341','3/22/2021'),
('NM213','L1234','3/22/2021'),
('OP1231','JK123','3/22/2021'),
('MB321','LK123','3/22/2021'),
('MX201','C12341','3/23/2021'),
('BX378','H1231','3/23/2021'),
('LZ231','MD321','3/23/2021'),
('LO3412','LP1231','3/23/2021'),
('MX201','C12341','3/24/2021'),
('BX378','H1231','3/24/2021'),
('FE221','J6571','3/24/2021')

**Mfg table** 

Create table mfg
(mfgdate date),
mmodel varchar(40),
srno varchar(40),
)
insert into mfg values
('3/21/2021','JK123','K21310'),
('3/21/2021','KL378','L8941'),
('3/22/2021','JK1231','K1561'),
('3/22/2021','KL1234','MJ1234'),
('3/22/2021','KL918','S21367'),
('3/23/2021','NM213','L1234'),
('3/23/2021','OP1231','JK123'),
('3/23/2021','MB321','LK123'),
('3/23/2021','XC2312','M89321'),
('3/24/2021','LZ231','MD321'),
('3/24/2021','LO3412','LP1231'),
('3/24/2021','KL5612','D3489')

**Output table**
create table output 
(Dated date,
mmodel varchar(40),
srno varchar(40),
skucomplete int,
goodstock int,
offdelay int,
newdelay int
)

inert into output values
('3/22/2021','JK1231','K1561','1','0','1','0'),
('3/22/2021','KL1234','MJ1234','1','0','1','0'),
('3/22/2021','KL918','S21367','1','1','0','0'),
('3/22/2021','OP1231','JK123','0','0','0','1'),
('3/22/2021','MB321','LK123','0','0','0','1'),
('3/23/2021','NM213','L1234','1','0','1','0'),
('3/23/2021','OP1231','JK123','1','0','1','0'),
('3/23/2021','MB321','LK123','1','0','1','0'),
('3/23/2021','XC2312','M89321','1','1','0','0'),
('3/23/2021','BX378','H1231','0','0','0','1'),
('3/23/2021','LZ231','MD321','0','0','0','1'),
('3/23/2021','LO3412','LP1231','0','0','0','1'),
('3/24/2021','LO3412','LP1231','1','0','1','0'),
('3/24/2021','KL5612','D3489','1','1','0','0'),
('3/24/2021','FE221','J6571','0','0','0','1')
('3/24/2021','LZ231','MD321','1','0','1','0')

总结

应该从MFG表和日期中获取SKU，如果在延迟表中的前一个日期中存在同一SKU，则其状态将完成-1和OffDelay -1
SKU来自MFG表和日期应采取以及在延迟表中的直接日期中不存在相同的SKU，则其状态将完成-1，而GoodStock -1
不存在的SKU特定日期的MFG表，但在该特定日期的延迟表中存在（但是在延迟表中的前一个日期中不存在同一SKU，其状态将只是新的延迟-1

4）在特定日期中在MFG表中不存在的SKU，但在该特定日期的延迟表中存在（但是在延迟表中的直接前几个日期中存在相同的SKU，那么SKU不会是在该日期的输出中...

查询从上述查询的结果尝试，

SELECT M.mfgdate AS Dated,M.mmodel AS mmodel,M.srno AS srno
       ,1 AS skucomplete
       ,CASE WHEN D.mmodel IS NULL THEN 1 ELSE 0 END AS goodstock
       ,CASE WHEN D.mmodel IS NOT NULL THEN 1 ELSE 0 END AS offdelay
       ,0 AS newdelay
 FROM mfg M LEFT JOIN delay D ON M.mfgdate=DATE_ADD(D.snapdate,INTERVAL 1 DAY) AND M.mmodel=D.mmodel
 WHERE M.mfgdate>'3/21/2021'
 UNION ALL
 (
 SELECT snapdate,mmodel,srno,0 AS skucomplete,0 AS goodstock,0 AS offdelay,1 AS newdelay 
 FROM delay
 WHERE snapdate>'3/21/2021'
 AND NOT EXISTS(
 SELECT D1.snapdate,D1.mmodel,D1.srno,0 AS skucomplete,0 AS goodstock,0 AS offdelay,1 AS newdelay
 FROM delay D1 JOIN delay D2 ON D1.snapdate=DATE_ADD(D2.snapdate,INTERVAL 1 DAY) AND D1.mmodel=D2.mmodel)
 )
 ORDER BY Dated

我仅获得skuctleded and goodstock的值，为1 ..我没有获得任何值的offdelay和Newdelay ..

谢谢，阿伦

原文

I have a delay table (as below) where a SKU is identified by the combination of mmodel and srno and the snapdate.. A SKU present on a specific snapdate indicates the SKU was delayed on that date

Delay Table

mmodel  Srno    Snapdate
MX201   C12341  3/21/2021
NM213   L1234   3/21/2021
JK1231  K1561   3/21/2021
KL1234  MJ1234  3/21/2021
MX201   C12341  3/22/2021
NM213   L1234   3/22/2021
OP1231  JK123   3/22/2021
MB321   LK123   3/22/2021
MX201   C12341  3/23/2021
BX378   H1231   3/23/2021
LZ231   MD321   3/23/2021
LO3412  LP1231  3/23/2021
MX201   C12341  3/24/2021
BX378   H1231   3/24/2021
FE221   J6571   3/24/2021

Mfg table

mfgdate mmodel  Srno
3/21/2021   JK123   K21310
3/21/2021   KL378   L8941
3/22/2021   JK1231  K1561
3/22/2021   KL1234  MJ1234
3/22/2021   KL918   S21367
3/23/2021   NM213   L1234
3/23/2021   OP1231  JK123
3/23/2021   MB321   LK123
3/23/2021   XC2312  M89321
3/24/2021   LZ231   MD321
3/24/2021   LO3412  LP1231
3/24/2021   KL5612  D3489

The above table has the mfgdate (date when the SKU got manufactured). Now I want to create a status table (as below) and ignore the first date in the above two tables and start from the second date.

Status output table

Date    mmodel  Srno    SKUComplete     Goodstock   offdelay    NewDelay
3/22/2021   JK1231  K1561   1   0   1   0
3/22/2021   KL1234  MJ1234  1   0   1   0
3/22/2021   KL918   S21367  1   1   0   0
3/22/2021   OP1231  JK123   0   0   0   1
3/22/2021   MB321   LK123   0   0   0   1
3/23/2021   NM213   L1234   1   0   1   0
3/23/2021   OP1231  JK123   1   0   1   0
3/23/2021   MB321   LK123   1   0   1   0
3/23/2021   XC2312  M89321  1   1   0   0
3/23/2021   BX378   H1231   0   0   0   1
3/23/2021   LZ231   MD321   0   0   0   1
3/23/2021   LO3412  LP1231  0   0   0   1
3/24/2021   LO3412  LP1231  1   0   1   0
3/24/2021   KL5612  D3489   1   1   0   0
3/24/2021   FE221   J6571   0   0   0   1

The above table is got by looking up the mfg table first, and the SKU present in the mfg table would be marked as SKUComplete 1 ..Now the same SKU should be looked up in the delay table on the previous date (Mfgdate-1)..If the SKU is present in the previous date in the delay table then offdelay would become 1 otherwise goodstock would become 1

After completing all the SKU'S in the mfg table for a particular manufacturing date the remaining SKU'S should be looked up in the delay table for the same date (non matching SKU'S) and they should be entered in the Status table with New delay as 1..Please find the DDL for the tables below

one of the other things to take care of is that if a SKU is not available in the mfg table on a particular mfg date and the SKU is only in the delay table on that specific mfgdate and is also available on the immediate previous delay date then that SKU will not be accounted for on that date
delay table

Create table delay
(mmodel varchar(40),
srno varchar(40),
snapdate date)

insert into delay values
('MX201','C12341','3/21/2021'),
('NM213','L1234','3/21/2021'),
('JK1231','K1561','3/21/2021'),
('KL1234','MJ1234','3/21/2021'),
('MX201','C12341','3/22/2021'),
('NM213','L1234','3/22/2021'),
('OP1231','JK123','3/22/2021'),
('MB321','LK123','3/22/2021'),
('MX201','C12341','3/23/2021'),
('BX378','H1231','3/23/2021'),
('LZ231','MD321','3/23/2021'),
('LO3412','LP1231','3/23/2021'),
('MX201','C12341','3/24/2021'),
('BX378','H1231','3/24/2021'),
('FE221','J6571','3/24/2021')

**Mfg table** 

Create table mfg
(mfgdate date),
mmodel varchar(40),
srno varchar(40),
)
insert into mfg values
('3/21/2021','JK123','K21310'),
('3/21/2021','KL378','L8941'),
('3/22/2021','JK1231','K1561'),
('3/22/2021','KL1234','MJ1234'),
('3/22/2021','KL918','S21367'),
('3/23/2021','NM213','L1234'),
('3/23/2021','OP1231','JK123'),
('3/23/2021','MB321','LK123'),
('3/23/2021','XC2312','M89321'),
('3/24/2021','LZ231','MD321'),
('3/24/2021','LO3412','LP1231'),
('3/24/2021','KL5612','D3489')

**Output table**
create table output 
(Dated date,
mmodel varchar(40),
srno varchar(40),
skucomplete int,
goodstock int,
offdelay int,
newdelay int
)

inert into output values
('3/22/2021','JK1231','K1561','1','0','1','0'),
('3/22/2021','KL1234','MJ1234','1','0','1','0'),
('3/22/2021','KL918','S21367','1','1','0','0'),
('3/22/2021','OP1231','JK123','0','0','0','1'),
('3/22/2021','MB321','LK123','0','0','0','1'),
('3/23/2021','NM213','L1234','1','0','1','0'),
('3/23/2021','OP1231','JK123','1','0','1','0'),
('3/23/2021','MB321','LK123','1','0','1','0'),
('3/23/2021','XC2312','M89321','1','1','0','0'),
('3/23/2021','BX378','H1231','0','0','0','1'),
('3/23/2021','LZ231','MD321','0','0','0','1'),
('3/23/2021','LO3412','LP1231','0','0','0','1'),
('3/24/2021','LO3412','LP1231','1','0','1','0'),
('3/24/2021','KL5612','D3489','1','1','0','0'),
('3/24/2021','FE221','J6571','0','0','0','1')
('3/24/2021','LZ231','MD321','1','0','1','0')

To summarize

The SKU from the mfg table and date should be taken and if the same SKU is present in the immediate previous date in the delay table then its status would be complete -1 and offdelay -1
The SKU from the mfg table and date should be taken and if the same SKU is not present in the immediate previous date in the delay table then its status would be complete -1 and goodstock-1
The SKU that is not present in mfg table on a specific date but is present in the delay table on that specific date (but the same SKU is not present in the immediate previous date in the delay table then its status would be just new delay -1

4)The SKU that is not present in mfg table on a specific date but is present in the delay table on that specific date (but the same SKU is present in the immediate previous date in the delay table then that SKU won't be in the output at all for that date...

Query Tried

SELECT M.mfgdate AS Dated,M.mmodel AS mmodel,M.srno AS srno
       ,1 AS skucomplete
       ,CASE WHEN D.mmodel IS NULL THEN 1 ELSE 0 END AS goodstock
       ,CASE WHEN D.mmodel IS NOT NULL THEN 1 ELSE 0 END AS offdelay
       ,0 AS newdelay
 FROM mfg M LEFT JOIN delay D ON M.mfgdate=DATE_ADD(D.snapdate,INTERVAL 1 DAY) AND M.mmodel=D.mmodel
 WHERE M.mfgdate>'3/21/2021'
 UNION ALL
 (
 SELECT snapdate,mmodel,srno,0 AS skucomplete,0 AS goodstock,0 AS offdelay,1 AS newdelay 
 FROM delay
 WHERE snapdate>'3/21/2021'
 AND NOT EXISTS(
 SELECT D1.snapdate,D1.mmodel,D1.srno,0 AS skucomplete,0 AS goodstock,0 AS offdelay,1 AS newdelay
 FROM delay D1 JOIN delay D2 ON D1.snapdate=DATE_ADD(D2.snapdate,INTERVAL 1 DAY) AND D1.mmodel=D2.mmodel)
 )
 ORDER BY Dated

From the result of the above query I am getting values for only the skucompleted and goodstock as 1 ..I am not getting any values for offdelay and newdelay..

Thanks,
Arun

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

落叶缤纷 2025-01-31 02:25:05

似乎

SELECT mmodel, Srno, MAX(Snapdate) AS latest_delay
    FROM delays
    GROUP BY mmodel, Srno;

对于任何mmodel+srno，提供最新_DELAY 很方便。也许在终极查询中包含在某种程度上？

It seems like

SELECT mmodel, Srno, MAX(Snapdate) AS latest_delay
    FROM delays
    GROUP BY mmodel, Srno;

would be handy in providing the latest_delay for any mmodel+srno. Maybe include that somehow in the ultimate query?

回复收藏 0 原文

~没有更多了~

关于作者

剩一世无双

暂无简介

文章

26 人气

关注发私信

友情链接

文江博客

MySQL查询以找到制造的SKU项目的状态

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（1）

关于作者

相关话题

热门标签

推荐作者

Mr.HU

疯到世界奔溃

隔纱相望

萌无敌

梦幻的味道

自在安然

友情链接

MySQL查询以找到制造的SKU项目的状态

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（1）

关于作者

相关话题

热门标签

推荐作者

Mr.HU

疯到世界奔溃

隔纱相望

萌无敌

梦幻的味道

自在安然

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。