mysql - 有没有办法编写查询来显示给定日期的内联作业数据以及后续的任何先前作业数据？

Question

我需要从作业表中收集昨天创建的所有作业，并按作业编号与作业标题表连接。从那里我需要找到过去三次我们为相同的 i-no (Item#) 预订的工作，并将数据显示在与昨天的工作相同的行中。如果没有以前的作业，这些字段将为空(当前为#NUM!因为我们在 Excel 中手动执行此操作)。如果只有一项先前的作业，则将填充相关字段，其余字段为空，依此类推。

show CREATE TABLE jobjob CREATE TABLE `job` (`job-no` varchar(12) DEFAULT NULL,`j2` int(11) DEFAULT NULL,`fix-cost` double DEFAULT NULL,`lab-cost` double DEFAULT NULL,`mat-cost` double DEFAULT NULL,`tot-cost` double DEFAULT NULL,`var-cost` double DEFAULT NULL,`create-date` date DEFAULT NULL,) ENGINE=InnoDB DEFAULT CHARSET=utf8    show CREATE TABLE jobheader jobheader   CREATE TABLE `jobheader` (`sdate` date DEFAULT NULL,`qty` double DEFAULT NULL,`tot-cost` double DEFAULT NULL,`mat-cost` double DEFAULT NULL,`lab-cost` double DEFAULT NULL,`fix-cost` double DEFAULT NULL,`var-cost` double DEFAULT NULL,`i-no` varchar(30) DEFAULT NULL,`j2` int(11) DEFAULT NULL,`job-no` varchar(12) DEFAULT NULL,) ENGINE=InnoDB DEFAULT CHARSET=utf8    

这是我的表格中的数据:

job table:  ║sdate║qty║tot-cost ║mat-cost║lab-cost║fix-cost║var-cost║i-no        ║j2║job-no║  ║NULL ║200║1297.6372║942.9434║140.078 ║117.781 ║96.8348 ║NEOIN77886NX║1 ║734701║  ║NULL ║250║1230.327 ║918.7153║123.0637║103.475 ║85.073  ║NEOIN77886NX║3 ║762822║  ║NULL ║200║2346.3662║1283.929║515.1117║177.2188║370.1067║MEDV25653   ║0 ║786993║  ║NULL ║500║1065.5539║886.2611║70.8075 ║59.5367 ║48.9486 ║NEOIN77886NX║0 ║787041║  jobheader table:  ║sdate║qty║tot-cost ║mat-cost║lab-cost║fix-cost║var-cost║i-no        ║j2║job-no║  ║NULL ║200║1297.6372║942.9434║140.078 ║117.781 ║96.8348 ║NEOIN77886NX║1 ║734701║  ║NULL ║250║1230.327 ║918.7153║123.0637║103.475 ║85.073  ║NEOIN77886NX║3 ║762822║  ║NULL ║200║2346.3662║1283.929║515.1117║177.2188║370.1067║MEDV25653   ║0 ║786993║  ║NULL ║500║1065.5539║886.2611║70.8075 ║59.5367 ║48.9486 ║NEOIN77886NX║0 ║787041║  

预期输出(这会很困惑，因为有很多数据)。我还附上了一张图片，以便更好地表示我正在寻找的内容。 enter image description here :

║job-no║j2║create-date║i-no        ║qty║sdate║tot-cost║mat-cost║lab-cost║fix-cost║var-cost║job-no║j2║Qty║tot-cost║mat-cost║lab-cost║fix-cost║var-cost║job-no║j2║Qty║tot-cost ║mat-cost║lab-cost║fix-cost║var-cost║job-no║j2   ║Qty  ║tot-cost║mat-cost║lab-cost║fix-cost║var-cost║  ║787041║0 ║4/4/2019   ║NEOIN77886NX║200║     ║1,349.54║994.85  ║140.08  ║ 117.78 ║96.83   ║762822║3 ║250║1230.327║918.7153║123.0637║103.475 ║85.073  ║734701║1 ║200║1297.6372║942.9434║140.078 ║117.781 ║96.8348 ║#NUM! ║#NUM!║#NUM!║#NUM!   ║#NUM!   ║#NUM!   ║#NUM!   ║#NUM!   ║  ║786993║0 ║4/4/2019   ║MEDV25653   ║200║     ║2,346.37║1,283.93║515.11  ║177.22  ║370.11  ║#NUM! ║#NUM!║#NUM!║#NUM!║#NUM!║#NUM!║#NUM!║#NUM!║#NUM!║#NUM!║#NUM! ║#NUM!║#NUM!║#NUM!║#NUM!║#NUM!║#NUM!║#NUM!║#NUM!║#NUM!║#NUM!║#NUM!║#NUM!║#NUM!║  

希望这能更好地解释事情，但信息并不是太多。

以下是我的查询。现在消除计算字段，它运行没有错误，只是为我昨天创建的每个作业提供了太多结果。我将这些编写为子查询，以便我可以限制结果的数量，但我似乎无法弄清楚如何将 jobh.i-no 传递到子查询中，因此不允许我限制结果。如果我能弄清楚这一点，并对每个子查询使用 LIMIT1、LIMIT1,1 和 LIMIT 2,1，我认为这将产生我正在寻找的结果。关于如何更改我的查询或如何将 Item# 传递给子查询有什么建议吗？

selectjob.`job-no`,job.`job-no2`,job.`create-date`,jobh.`i-no`,jobh.qty,jobh.`start-date`,jobh.`std-tot-cost`,jobh.`std-mat-cost`,jobh.`std-lab-cost`,jobh.`std-fix-cost`,jobh.`std-var-cost`,tmp.`std-tot-cost` as PreviousJobStdTotCost,tmp.`std-mat-cost` as PreviousJobStdMatCost,tmp.`std-lab-cost` as PreviousJobStdLabCost,tmp.`std-fix-cost` as PreviousJobStdFixCost,tmp.`std-var-cost` as PreviousJobStdVarCost,tmp2.`std-tot-cost` as PreviousJob2StdTotCost,tmp2.`std-mat-cost` as PreviousJob2StdMaCost,tmp2.`std-lab-cost` as PreviousJob2StdLabCost,tmp2.`std-fix-cost` as PreviousJob2StdFixCost,tmp2.`std-var-cost` as PreviousJob2StdVarCost,tmp3.`std-tot-cost` as PreviousJob3StdTotCost,tmp3.`std-mat-cost` as PreviousJob3StdMatCost,tmp3.`std-lab-cost` as PreviousJob3StdLabCost,tmp3.`std-fix-cost` as PreviousJob3StdFixCost,tmp3.`std-var-cost` as PreviousJob3StdVarCostfrom asi.job as jobleft join asi.jobheader  as jobh on job.`job-no`=jobh.`job-no` and job.`job-no2`=jobh.`job-no2`left join (select jobh1.`i-no`, jobh1.`job-no`, jobh1.`std-tot-cost`, jobh1.`std-mat-cost`, jobh1.`std-lab-cost`, jobh1.`std-fix-cost`, jobh1.`std-var-cost` from asi.jobheader as jobh1 order by jobh1.`job-no` desc) tmp on tmp.`i-no`=jobh.`i-no` and tmp.`job-no`jobh.`job-no`left join (select jobh2.`i-no`, jobh2.`job-no`, jobh2.`std-tot-cost`, jobh2.`std-mat-cost`, jobh2.`std-lab-cost`, jobh2.`std-fix-cost`, jobh2.`std-var-cost` from asi.jobheader as jobh2 order by jobh2.`job-no` desc ) tmp2 on tmp2.`i-no`=tmp.`i-no` and tmp2.`job-no`tmp.`job-no`left join (select jobh3.`i-no`, jobh3.`job-no`, jobh3.`std-tot-cost`, jobh3.`std-mat-cost`, jobh3.`std-lab-cost`, jobh3.`std-fix-cost`, jobh3.`std-var-cost` from asi.jobheader as jobh3 order by jobh3.`job-no` desc) tmp3 on tmp3.`i-no`=tmp2.`i-no` and tmp3.`job-no`tmp2.`job-no`where asi.job.`create-date`="2019-04-04" and asi.job.`job-no` is not null and asi.job.`job-no`"" 

Answer 1

你可以这样尝试

SELECT Order#, OrderDate, Item#, Price, Price_1, Price_2, Price_3, 
100 * (Price - COALESCE(Price_1,0)) / Price AS Diff_1,
100 * (Price - COALESCE(Price_2,0)) / Price AS Diff_2,
100 * (Price - COALESCE(Price_3,0)) / Price AS Diff_3
FROM
(SELECT Order#, OrderDate, Item#, Price, 
(SELECT Price FROM Orders AS Ord WHERE Ord.Order# < Orders.Order# AND Ord.Item# = Orders.Item# ORDER BY Order# DESC LIMIT 1) AS Price_1,
(SELECT Price FROM Orders AS Ord WHERE Ord.Order# < Orders.Order# AND Ord.Item# = Orders.Item# ORDER BY Order# DESC LIMIT 2,1) AS Price_2,
(SELECT Price FROM Orders AS Ord WHERE Ord.Order# < Orders.Order# AND Ord.Item# = Orders.Item# ORDER BY Order# DESC LIMIT 3,1) AS Price_3
FROM Orders
WHERE OrderDate = "2019-04-02"
ORDER BY Order#) AS TMP