我一直在四处寻找并尝试搜索，但是没有运气。我将继续这样做，但是希望获得一些帮助或指出正确的方向！
我有2个数据表（类似于下面的数据表）
sales_quotes
myIDX inStock QDate MFGname MFGNumber Vendor
+-----+-----+----------+--------+--------+----------+
| 1 | 20 | 3/4/2018 | Burton | snow1 | Christie |
+-----+-----+----------+--------+--------+----------+
| 2 | 100 | 3/8/2018 | Burton | snow1 | Christie |
+-----+-----+----------+--------+--------+----------+
| 3 | 35 | 3/8/2018 | Jones | proto1 | Christie |
+-----+-----+----------+--------+--------+----------+
| 4 | 45 | 3/4/2018 | Jones | proto1 | Christie |
+-----+-----+----------+--------+--------+----------+
sales_quotes_prices
myIDX myQTY myPrice
+-----+------+-------+
| 1 | 20 | 250 |
+-----+------+-------+
| 1 | 50 | 225 |
+-----+------+-------+
| 2 | 20 | 250 |
+-----+------+-------+
| 2 | 50 | 225 |
+-----+------+-------+
| 3 | 20 | 350 |
+-----+------+-------+
| 4 | 10 | 300 |
+-----+------+-------+
| 4 | 50 | 250 |
+-----+------+-------+
我已经能够使用内部联接来将sales_quotes分解为多行，其中包含它们可用的每个数量和价格。
SELECT q.myIDX
, q.MFG
, q.MFGN
, q.Vendor
, q.QDate
, q.InStock
, p.myQTY
, p.myPrice
FROM sales_quotes_prices p
JOIN sales_quotes q
ON p.myIDX = q.myIDX
加入的结果如下
+-------+---------+----------+---------+-----------+----------+-------+---------+
| myIDX | inStock | Qdate | MFGname | MFGNumber | Vendor | myQTY | myPrice |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 1 | 20 | 3/4/2018 | Burton | snow1 | Christie | 20 | 250 |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 1 | 20 | 3/4/2018 | Burton | snow1 | Christie | 50 | 225 |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 2 | 100 | 3/8/2018 | Burton | snow1 | Christie | 20 | 250 |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 2 | 100 | 3/8/2018 | Burton | snow1 | Christie | 50 | 225 |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 3 | 35 | 3/8/2018 | Jones | proto1 | Christie | 20 | 350 |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 4 | 45 | 3/4/2018 | Jones | proto1 | Christie | 10 | 300 |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 4 | 45 | 3/4/2018 | Jones | proto1 | Christie | 50 | 250 |
+-------+---------+----------+---------+-----------+----------+-------+---------+
目标：我试图将所有匹配报价上的最新“ QDate”中的“库存”十进制值替换为相同的[mfgname，mfgnumber，供应商]
但是我仍然坚持如何实现这一目标（下面已适当分配了“库存”！）
+-------+---------+----------+---------+-----------+----------+-------+---------+
| myIDX | inStock | Qdate | MFGname | MFGNumber | Vendor | myQTY | myPrice |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 1 | 100 | 3/4/2018 | Burton | snow1 | Christie | 20 | 250 |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 1 | 100 | 3/4/2018 | Burton | snow1 | Christie | 50 | 225 |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 2 | 100 | 3/8/2018 | Burton | snow1 | Christie | 20 | 250 |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 2 | 100 | 3/8/2018 | Burton | snow1 | Christie | 50 | 225 |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 3 | 35 | 3/8/2018 | Jones | proto1 | Christie | 20 | 350 |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 4 | 35 | 3/4/2018 | Jones | proto1 | Christie | 10 | 300 |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 4 | 35 | 3/4/2018 | Jones | proto1 | Christie | 50 | 250 |
+-------+---------+----------+---------+-----------+----------+-------+---------+
感谢您的建议！
最佳答案
这不是很漂亮，但是：
SELECT q.myIDX
, B.InStock/*Instock data coming from subquery*/
, q.QDate
, q.MFGname
, q.MFGNumber
, q.Vendor
, p.myQTY
, p.myPrice
FROM sales_quotes_prices p
JOIN sales_quotes q
ON p.myIDX = q.myIDX
/*the subquery starts here*/
LEFT JOIN
(SELECT a.myidx,a.instock,a.qdate,a.mfgname,a.mfgnumber,a.vendor FROM sales_quotes A INNER JOIN
(SELECT *,MAX(qdate) AS latestdate FROM sales_quotes GROUP BY mfgname, mfgnumber, vendor) LATEST
ON a.qdate=latest.latestdate AND a.mfgname=latest.mfgname AND a.mfgnumber=latest.mfgnumber AND a.vendor=latest.vendor) B
ON q.mfgname=B.mfgname AND q.mfgnumber=B.mfgnumber AND q.vendor=b.vendor;
我正在使用子查询来自定义“ InStock”列，仅显示最新数据。因此，第一个子查询（位于另一个子查询中）是这样的：
SELECT *,MAX(qdate) AS latestdate FROM sales_quotes GROUP BY mfgname, mfgnumber, vendor。MAX(qdate)将根据您的制造商名称，制造商编号，供应商（我定义为“ latestdate”）的条件返回最新的日期组。然后，我用INNER JOIN上的第一个子查询将另一个查询写入qdate=latestdate sales_qoutes表，您可以在这里看到：SELECT a.myidx,a.instock,a.qdate,a.mfgname,a.mfgnumber,a.vendor FROM sales_quotes A INNER JOIN(SELECT *,MAX(qdate) AS latestdate FROM sales_quotes GROUP BY mfgname, mfgnumber, vendor) LATESTON a.qdate=latest.latestdate AND a.mfgname=latest.mfgname AND a.mfgnumber=latest.mfgnumber AND a.vendor=latest.vendor当然，我需要声明mfgname，mfgnumber和供应商也必须匹配。这将强制第二个子查询仅返回与第一个子查询中与
MAX(qdate)匹配的最新数据。最后一个只是通过将q.instock更改为b.instock（或为子查询定义的任何派生表名称）并通过子查询LEFT JOIN来编辑原始语法。关于mysql - MySQL内部联接并在所有行上按日期用最新值替换值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/55070694/