我正在開發(fā)一個庫存系統(tǒng)。這是我的表：藥表：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 |醫(yī)藥交易表：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我想生成一個月度報告，在那里我可以獲取該月某項目的最后一筆交易，以及該月之前該項目的最后一筆交易，用作給定月份的期初余額。另外，如果給定月份沒有交易，但數(shù)量仍然大于零，那么也可以顯示它。這是我不起作用的示例代碼Select * from medicine m left join medicinetransac mt on m.medID = mt.medID where month(mt.transacDate) = '$month'。medID = 4, 7, 8即使在給定月份沒有交易的項目也會被捕獲，因為數(shù)量仍然 > 0。謝謝！我希望你能理解我如何寫我的問題！順便說一句，我使用 PHP，我不希望一切都通過查詢來完成。
2 回答
ibeautiful
TA貢獻1993條經(jīng)驗 獲得超6個贊
盡管您對表結(jié)構(gòu)的處理方式似乎有些無效，但我會嘗試解決您的問題，忽略這一點。
因此，假設您有包含以下內(nèi)容的 PHP 變量：
$target_month = '08';
$target_year = date("Y");
我們想要收集年份，因為我們不希望 sql 假設 2019 年 8 月與 2018 年 8 月相同并將它們混合。
調(diào)整到您當前的結(jié)構(gòu)，我首先要為每個項目獲取本月最后一筆交易的子查詢。我們使用 GROUP BY 語句和 MAX() 聚合函數(shù)的組合來做到這一點。
SELECT medID, MAX(medTransacID) AS lastTransacID
FROM medicinetransac
WHERE MONTH(transacDate) = {$target_month} AND YEAR(transacDate) = {$target_year}
GROUP BY medId
請注意，我們沒有獲得最后一個日期（因為我們每個日期可以有多個交易），因此我們獲得了最高的交易 ID。只要確保它是自動遞增的。
現(xiàn)在，要獲取上個月的數(shù)據(jù)，需要做一些假設。我們需要在最后這一個月之前一個月的數(shù)據(jù)，或最后一筆交易？有很大的不同，因為如果我們上個月沒有交易怎么辦？如果最后一次購買商品是在 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。請注意我如何使用 < 和 <= 作為日期。
我們現(xiàn)在可以像這樣構(gòu)建這個大查詢：
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。如果最后一個子查詢不起作用，請考慮使用左連接而不是嵌套選擇。我這樣做只是為了向您展示差異。
另外，一些提示；
盡量避免使用非字母字符作為列名和別名
使表和列更具描述性
使用數(shù)據(jù)庫時堅持使用 underscore_notation（更干凈）
避免表中的冗余。注意 transacType 有多么冗余？為什么不使用兩個單獨的表進行輸入/輸出交易？
希望這會幫助你。祝你好運！
- 2 回答
- 0 關注
- 203 瀏覽
添加回答
舉報
0/150
提交
取消