选择一个项目在一个月内的最后一笔交易

Question

我正在开发一个库存系统。这是我的表：

药表：

medID| medDescription | medBeginningQty |

+++++++++++++++++++++++++++++++++++++++++

1    | Amplodipine    |5000             |

2    | Losartan 5mg   |5000             |

3    | Amoxicillin    |5000             |

4    | Paracetamol    |5000             |

5    | Ascorbic Acid  |5000             |

6    | Co-amoxiclav   |5000             |

7    | Alcohol        |5000             |

8    | Losartan 10mg  |5000             |

医药交易表：

medTransacID|medID|transacType|transacQuantity|transacDate|transacCurrentBal 

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1           |  1  | NewItem   | 5000          | 2018-10-01| 5000

2           |  2  | NewItem   | 5000          | 2018-10-01| 5000

3           |  3  | NewItem   | 5000          | 2018-10-01| 5000

4           |  4  | NewItem   | 5000          | 2018-10-01| 5000

5           |  5  | NewItem   | 5000          | 2018-10-01| 5000

6           |  6  | NewItem   | 5000          | 2018-10-01| 5000

7           |  7  | NewItem   | 5000          | 2018-10-01| 5000

8           |  8  | NewItem   | 5000          | 2018-10-01| 5000

9           |  1  | issuance  | 100           | 2019-07-01| 4900

10          |  2  | issuance  | 100           | 2019-07-01| 4900

11          |  3  | issuance  | 100           | 2019-07-01| 4900

12          |  1  | issuance  | 200           | 2019-07-15| 4700

我想生成一个月度报告，在那里我可以获取该月某项目的最后一笔交易，以及该月之前该项目的最后一笔交易，用作给定月份的期初余额。

另外，如果给定月份没有交易，但数量仍然大于零，那么也可以显示它。这是我不起作用的示例代码Select * from medicine m left join medicinetransac mt on m.medID = mt.medID where month(mt.transacDate) = '$month'。

medID = 4, 7, 8即使在给定月份没有交易的项目也会被捕获，因为数量仍然 > 0。

谢谢！我希望你能理解我如何写我的问题！顺便说一句，我使用 PHP，我不希望一切都通过查询来完成。

Answer 1

尽管您对表结构的处理方式似乎有些无效，但我会尝试解决您的问题，忽略这一点。因此，假设您有包含以下内容的 PHP 变量：$target_month = '08';$target_year = date("Y");我们想要收集年份，因为我们不希望 sql 假设 2019 年 8 月与 2018 年 8 月相同并将它们混合。调整到您当前的结构，我首先要为每个项目获取本月最后一笔交易的子查询。我们使用 GROUP BY 语句和 MAX() 聚合函数的组合来做到这一点。SELECT medID, MAX(medTransacID) AS lastTransacIDFROM medicinetransac&nbsp;WHERE MONTH(transacDate) = {$target_month} AND YEAR(transacDate) = {$target_year}GROUP BY medId请注意，我们没有获得最后一个日期（因为我们每个日期可以有多个交易），因此我们获得了最高的交易 ID。只要确保它是自动递增的。现在，要获取上个月的数据，需要做一些假设。我们需要在最后这一个月之前一个月的数据，或最后一笔交易？有很大的不同，因为如果我们上个月没有交易怎么办？如果最后一次购买商品是在 5 个月前怎么办？我们用那个？还是我们显示空白？对于此示例，我将假设您想要本月之前的最后一笔交易，无论是上个月还是 3 个月前。SELECT medId, transacDate, transacCurrentBalFROM medicinetransac aWHERE medTransacID = (&nbsp; &nbsp; SELECT MAX(medTransacID) FROM medicinetransac b&nbsp; &nbsp; WHERE MONTH(transacDate) < {$target_month}&nbsp; &nbsp; &nbsp; AND YEAR(transacDate) <= {$target_year}&nbsp; &nbsp; &nbsp; AND b.medId = a.medId&nbsp; &nbsp; &nbsp; LIMIT 1)因此，在该查询中，我们提取在本月之前的任何日期找到的最大交易 ID。请注意我如何使用 < 和 <= 作为日期。我们现在可以像这样构建这个大查询：SELECT&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; med.medID,&nbsp; &nbsp; &nbsp; &nbsp; med.medDescription,&nbsp; &nbsp; &nbsp; &nbsp; tmd.transacType,&nbsp; &nbsp; &nbsp; &nbsp; topmt.transacCurrentBal AS beginningBalance,&nbsp; &nbsp; &nbsp; &nbsp; topmt.transacDate AS begginingBalanceDate,&nbsp; &nbsp; &nbsp; &nbsp; tmd.transacDate AS lastTransactionDate,&nbsp; &nbsp; &nbsp; &nbsp; tmd.transacQuantity AS lastTransactionQuantityFROM medicine medLEFT JOIN (&nbsp; &nbsp; SELECT medID, MAX(medTransacID) AS lastTransacID&nbsp; &nbsp; FROM medicinetransac&nbsp;&nbsp; &nbsp; WHERE MONTH(transacDate) = {$target_month} AND YEAR(transacDate) = {$target_year}&nbsp; &nbsp; GROUP BY medId) table_of_last_transaction_ids tolti ON med.medID = tolti.medIDLEFT JOIN medicinetransac target_month_data tmd ON tmd.medTransacID = tolti.medTransacIdLEFT JOIN (&nbsp; &nbsp; SELECT medId, transacDate, transacCurrentBal&nbsp; &nbsp; FROM medicinetransac a&nbsp; &nbsp; WHERE medTransacID = (&nbsp; &nbsp; &nbsp; &nbsp; SELECT MAX(medTransacID) FROM medicinetransac b&nbsp; &nbsp; &nbsp; &nbsp; WHERE MONTH(transacDate) < {$target_month}&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND YEAR(transacDate) <= {$target_year}&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND b.medId = a.medId&nbsp; &nbsp; &nbsp; &nbsp; LIMIT 1&nbsp; &nbsp; )) table_of_previous_months_transaction topmt ON topmt.medId = med.medId但是，对此有一些注意事项。子查询通常很慢，如果您使用的是更新版本的 mysql，我建议您查看window functions。如果最后一个子查询不起作用，请考虑使用左连接而不是嵌套选择。我这样做只是为了向您展示差异。另外，一些提示；尽量避免使用非字母字符作为列名和别名使表和列更具描述性使用数据库时坚持使用 underscore_notation（更干净）避免表中的冗余。注意 transacType 有多么冗余？为什么不使用两个单独的表进行输入/输出交易？希望这会帮助你。祝你好运！

Answer 2

你的表结构似乎有问题。因为当我在我的电脑上复制表格时。查询运行良好，没有任何错误。