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

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| 50002           |  2  | NewItem   | 5000          | 2018-10-01| 50003           |  3  | NewItem   | 5000          | 2018-10-01| 50004           |  4  | NewItem   | 5000          | 2018-10-01| 50005           |  5  | NewItem   | 5000          | 2018-10-01| 50006           |  6  | NewItem   | 5000          | 2018-10-01| 50007           |  7  | NewItem   | 5000          | 2018-10-01| 50008           |  8  | NewItem   | 5000          | 2018-10-01| 50009           |  1  | issuance  | 100           | 2019-07-01| 490010          |  2  | issuance  | 100           | 2019-07-01| 490011          |  3  | issuance  | 100           | 2019-07-01| 490012          |  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 lastTransacID

FROM medicinetransac 

WHERE MONTH(transacDate) = {$target_month} AND YEAR(transacDate) = {$target_year}

GROUP BY medId

请注意，我们没有获得最后一个日期（因为我们每个日期可以有多个交易），因此我们获得了最高的交易 ID。只要确保它是自动递增的。

现在，要获取上个月的数据，需要做一些假设。我们需要在最后这一个月之前一个月的数据，或最后一笔交易？有很大的不同，因为如果我们上个月没有交易怎么办？如果最后一次购买商品是在 5 个月前怎么办？我们用那个？还是我们显示空白？对于此示例，我将假设您想要本月之前的最后一笔交易，无论是上个月还是 3 个月前。

SELECT medId, transacDate, transacCurrentBal

FROM medicinetransac a

WHERE medTransacID = (

    SELECT MAX(medTransacID) FROM medicinetransac b

    WHERE MONTH(transacDate) < {$target_month}

      AND YEAR(transacDate) <= {$target_year}

      AND b.medId = a.medId

      LIMIT 1

)

因此，在该查询中，我们提取在本月之前的任何日期找到的最大交易 ID。请注意我如何使用 < 和 <= 作为日期。

我们现在可以像这样构建这个大查询：

SELECT 

        med.medID,

        med.medDescription,

        tmd.transacType,

        topmt.transacCurrentBal AS beginningBalance,

        topmt.transacDate AS begginingBalanceDate,

        tmd.transacDate AS lastTransactionDate,

        tmd.transacQuantity AS lastTransactionQuantity

FROM medicine med

LEFT JOIN (

    SELECT medID, MAX(medTransacID) AS lastTransacID

    FROM medicinetransac 

    WHERE MONTH(transacDate) = {$target_month} AND YEAR(transacDate) = {$target_year}

    GROUP BY medId

) table_of_last_transaction_ids tolti ON med.medID = tolti.medID

LEFT JOIN medicinetransac target_month_data tmd ON tmd.medTransacID = tolti.medTransacId

LEFT JOIN (

    SELECT medId, transacDate, transacCurrentBal

    FROM medicinetransac a

    WHERE medTransacID = (

        SELECT MAX(medTransacID) FROM medicinetransac b

        WHERE MONTH(transacDate) < {$target_month}

          AND YEAR(transacDate) <= {$target_year}

          AND b.medId = a.medId

        LIMIT 1

    )

) table_of_previous_months_transaction topmt ON topmt.medId = med.medId

但是，对此有一些注意事项。子查询通常很慢，如果您使用的是更新版本的 mysql，我建议您查看window functions。如果最后一个子查询不起作用，请考虑使用左连接而不是嵌套选择。我这样做只是为了向您展示差异。

另外，一些提示；

1. 尽量避免使用非字母字符作为列名和别名

2. 使表和列更具描述性

3. 使用数据库时坚持使用 underscore_notation（更干净）

4. 避免表中的冗余。注意 transacType 有多么冗余？为什么不使用两个单独的表进行输入/输出交易？

希望这会帮助你。祝你好运！

Answer 2

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