客戶表
------------ ---------- ------- ------- ------------- ----------------------------- ---------- --------------
| CustomerID | Fname | Mname | Lname | Contact_no | Address | Valid_id | Credit_Limit |
------------ ---------- ------- ------- ------------- ----------------------------- ---------- --------------
| 7 | John | Dale | Doe | 09123654789 | Asan Sur, Sison, Pangasinan | NULL | 5000.000 |
| 8 | Jane | Dale | Doe | 09987654123 | Asan Sur, Sison, Pangasinan | NULL | 1500.000 |
| 91 | Kurdapya | Buang | Selos | 09741258963 | Paldit, Sison, Pangasinan | NULL | 5000.000 |
------------ ---------- ------- ------- ------------- ----------------------------- ---------- --------------
訂單表
--------- ------------ ---------------- ---------- ------------
| OrderID | CustomerID | DateOfPurchase | Discount | DueDate |
--------- ------------ ---------------- ---------- ------------
| 82 | 7 | 2022-04-17 | 0 | 2022-05-17 |
| 83 | 91 | 2022-04-17 | 0 | 2022-05-17 |
| 84 | 8 | 2022-04-17 | 0 | 2022-05-17 |
| 85 | 91 | 2022-04-17 | 0 | 2022-05-17 |
| 86 | 7 | 2022-04-17 | 0 | 2022-05-17 |
| 87 | 91 | 2022-04-18 | 0 | 2022-05-18 |
| 109 | 7 | 2022-04-25 | 0 | 2022-05-25 |
--------- ------------ ---------------- ---------- ------------
付款表
----------- ------------ --------- ------------ ----------
| PaymentID | CustomerID | OrderID | PayDate | Amount |
----------- ------------ --------- ------------ ----------
| 20 | 7 | 82 | 2022-04-25 | 800.000 |
| 21 | 91 | 83 | 2022-04-17 | 2500.000 |
| 22 | 91 | 85 | 2022-04-17 | 200.000 |
| 23 | 95 | 88 | 2022-04-18 | 2122.000 |
| 24 | 96 | 90 | 2022-04-25 | 577.000 |
| 25 | 97 | 111 | 2022-04-25 | 0.000 |
| 26 | 98 | 114 | 2022-04-25 | 166.000 |
| 27 | 99 | 115 | 2022-04-25 | 1740.000 |
----------- ------------ --------- ------------ ----------
訂單詳情表
------- --------- ----------- ------
| OR_ID | OrderID | ProductID | QTY |
------- --------- ----------- ------
| 173 | 82 | 5 | 1 |
| 174 | 82 | 9 | 1 |
| 184 | 86 | 5 | 1 |
| 185 | 86 | 9 | 1 |
| 186 | 86 | 13 | 1 |
| 187 | 86 | 17 | 1 |
| 224 | 109 | 3 | 3 |
| 225 | 109 | 6 | 3 |
| 292 | 145 | 20 | 2 |
| 293 | 145 | 12 | 1 |
| 294 | 145 | 8 | 2 |
| 295 | 146 | 14 | 1 |
| 296 | 146 | 11 | 1 |
| 297 | 146 | 12 | 1 |
| 298 | 146 | 3 | 1 |
| 299 | 146 | 6 | 1 |
| 300 | 146 | 7 | 1 |
| 301 | 146 | 16 | 1 |
------- --------- ----------- ------
我不知道這是否需要，但這是我的產品表：
----------- --------------- ----------------------- ------------ ----------- ------
| ProductID | Pname | Pdesc | PUnitPrice | weight | Unit |
----------- --------------- ----------------------- ------------ ----------- ------
| 2 | Pepsi | 1 Case Glass Bottle | 313.000 | 1 Litre | 12 |
| 3 | Mountain Dew | 1 Case Glass Bottle | 231.000 | 750 ML | 12 |
| 4 | Pepsi | 1 Case Plastic Bottle | 620.000 | 1.5 Litre | 12 |
| 5 | Mirinda | 1 Case Plastic Bottle | 620.000 | 1.5 Litre | 12 |
| 6 | Mountain Dew | 1 Case Plastic Bottle | 620.000 | 1.5 Litre | 12 |
| 7 | Mountain Dew | 1 Case Glass Bottle | 145.000 | 8 oz | 24 |
| 8 | Pepsi | 1 Case Glass Bottle | 145.000 | 8 oz | 24 |
| 9 | Mirinda | 1 Case Glass Bottle | 145.000 | 8 oz | 24 |
| 10 | 7up | 1 Case Glass Bottle | 145.000 | 8 oz | 24 |
| 11 | Sting | 1 Case Glass Bottle | 266.000 | 240 ml | 24 |
| 12 | Tropicana | 1 Case Glass Bottle | 266.000 | 240 ml | 24 |
| 13 | Cobra | 1 Case Glass Bottle | 266.000 | 240 ml | 24 |
| 14 | Sting | 1 Case Plastic Bottle | 166.000 | 300 ml | 12 |
| 15 | Cobra | 1 Case Plastic Bottle | 166.000 | 300 ml | 12 |
| 16 | Mountain Dew | 1 Case Plastic Bottle | 135.000 | 295 ml | 12 |
| 17 | Mirinda | 1 Case Plastic Bottle | 135.000 | 295 ml | 12 |
| 18 | Pepsi | 1 Case Plastic Bottle | 135.000 | 295 ml | 12 |
| 19 | Ginebra | 1 Case Glass Bottle | 129.000 | 350 ml | 24 |
| 20 | San Mig Light | 1 Case Glass Bottle | 1070.000 | 330 ml | 24 |
| 21 | Red Horse | 1 Case Glass Bottle | 535.000 | 500 ml | 12 |
| 22 | Red Horse | 1 Case Glass Bottle | 545.000 | 1 Litre | 6 |
----------- --------------- ----------------------- ------------ ----------- ------
我想發生什么
我只想在一行中顯示 customerID 7 的所有已付款訂單和他的未付款訂單。我不知道如何從查詢開始。我應該使用 If() 條件嗎？如何正確查詢它以實作我想要的輸出？
我想要的輸出：
-------- ------ ------- ------- ------- ------------- ----------------------------- -------------- ----------
| unpaid | paid | Fname | Mname | Lname | Contact_no | Address | Credit_Limit | total |
-------- ------ ------- ------- ------- ------------- ----------------------------- -------------- ----------
| 2995 | 6014 | John | Dale | Doe | 09123654789 | Asan Sur, Sison, Pangasinan | 5000.000 | 9009.000 |
-------- ------ ------- ------- ------- ------------- ----------------------------- -------------- ----------
編輯
這是我迄今為止嘗試過的
第一次嘗試： 我正在嘗試計算未付訂單，這是輸出：
select
if(py.Amount IS NULL, sum(od.QTY * p.PUnitPrice), CONCAT(py.Amount - sum(od.QTY * p.PUnitPrice))) as remarks,
c.Fname, c.Mname, c.Lname, c.Contact_no, c.Address, c.Credit_Limit,
sum(od.QTY * p.PUnitPrice) as total
from customer c INNER JOIN orders r on r.CustomerID=c.CustomerID
INNER join order_details od on r.OrderID=od.OrderID
INNER JOIN product p on od.ProductID = p.ProductID
join payment py
where c.CustomerID=7
嘗試 1 的結果：
------------- ------- ------- ------- ------------- ----------------------------- -------------- ------------
| remarks | Fname | Mname | Lname | Contact_no | Address | Credit_Limit | total |
------------- ------- ------- ------- ------------- ----------------------------- -------------- ------------
| -134335.000 | John | Dale | Doe | 09123654789 | Asan Sur, Sison, Pangasinan | 5000.000 | 135135.000 |
------------- ------- ------- ------- ------------- ----------------------------- -------------- ------------
第二次嘗試：
SELECT IF(py.OrderID IS NULL, sum(od.QTY * p.PUnitPrice), 0) AS unpaid,
if(py.OrderID IS NOT NULL, sum(od.QTY * p.PUnitPrice), 0) as paid,
sum(od.QTY * p.PUnitPrice) as total,
O.OrderID, O.CustomerID, date_format(O.DateOfPurchase, '%M %d, %Y') AS DateOfPurchase, date_format(O.DueDate, '%M %d, %Y') AS DueDate
from Orders O INNER JOIN order_details od on od.OrderID=O.OrderID
INNER JOIN product p ON od.ProductID=p.ProductID
LEFT JOIN Payment py ON py.OrderID = O.OrderID
where O.CustomerID = 7
GROUP by O.OrderID
ORDER by O.OrderID desc
嘗試 2 的結果：
---------- ---------- ---------- --------- ------------ ---------------- --------------
| unpaid | paid | total | OrderID | CustomerID | DateOfPurchase | DueDate |
---------- ---------- ---------- --------- ------------ ---------------- --------------
| 1829.000 | 0.000 | 1829.000 | 146 | 7 | April 27, 2022 | May 27, 2022 |
| 0.000 | 2696.000 | 2696.000 | 145 | 7 | April 27, 2022 | May 27, 2022 |
| 0.000 | 2553.000 | 2553.000 | 109 | 7 | April 25, 2022 | May 25, 2022 |
| 1166.000 | 0.000 | 1166.000 | 86 | 7 | April 17, 2022 | May 17, 2022 |
| 0.000 | 765.000 | 765.000 | 82 | 7 | April 17, 2022 | May 17, 2022 |
---------- ---------- ---------- --------- ------------ ---------------- --------------
注意：如何匯總已付款和未付款列中的所有行？
uj5u.com熱心網友回復：
您接近解決方案的方式顯然不起作用，因此您正在尋求幫助。您的第一個查詢，您要加入付款表，但沒有條件限制只為客戶 7 付款。這可能巧合地解決了，因為只有一個付款記錄。但是對于擁有多個以上的客戶，您的總數將通過笛卡爾積結果出現偏差。
您應該做的是預先匯總購買與付款完全分開，這樣您將擁有最多，您正在尋找的一個客戶的單一記錄。現在，如果您想為所有客戶應用結果，那只會增加一點，稍后再介紹。
每個單獨的，防止多個混亂的混亂發生。
簡而言之，由于您只關心購買的總金額與已支付的金額，我不會關心每個訂單的總金額，而只關心每個客戶的總金額。
select
c.Fname,
c.Mname,
c.Lname,
c.Contact_no,
c.Address,
c.Credit_Limit,
coalesce( PQ_Orders.TotalOrders, 0 ) TotalOrders,
coalesce( PQ_Payments.TotalPaid, 0 ) TotalPayments,
coalesce( PQ_Orders.TotalOrders, 0 )
- coalesce( PQ_Payments.TotalPaid, 0 ) BalanceDue
from
customer c
LEFT JOIN
( select
o.customerID,
sum( od.qty * p.pUnitPrice ) TotalOrders
from
orders o
join order_details od
on o.orderID = od.orderID
join product p
on od.productid = p.productid
where
o.customerid = 7
group by
o.customerID ) PQ_Orders
on c.customerID = PQ_Orders.customerID
LEFT JOIN
( select
py.customerid,
sum( py.Amount ) TotalPaid
from
payments py
where
py.customerid = 7
group by
py.customerid ) PQ_Payments
on c.customerID = PQ_Payments.CustomerID
where
c.CustomerID = 7
現在，如果您想要所有客戶，只需洗掉所有 WHERE 子句，以便獲得所有客戶。每個相應的內部查詢都將客戶 ID 作為資料分組，因此如果一個人有 10 個訂單和 3 個付款，那么在每個 PRE-QUERY (PQ) 聚合中，每個客戶只有 1 個訂單條目和每個客戶 1 個條目關于付款。重新加入主客戶表，并將其全面應用于所有人。
對 O/P 的澄清
為了幫助澄清，讓我們看看你的要求。對于單個客戶，購買了多少東西。將其視為一個查詢。根據訂單詳細資訊和相應的產品價格獲取該客戶的所有訂單，并按客戶分組匯總。現在，客戶的 WHERE 子句 = 7 只是因為您是關于 ONE 客戶的。但是如果沒有 where 將向所有客戶展示他們各自購買的內容。無論他們有 1 個訂單還是 274 個訂單，您都將擁有最多（因為按 customerid 分組）每個客戶 1 條記錄。它是那個客戶的總數。
現在，付款的確切背景關系相同。我不在乎他們買了什么或什么時候買的。我只關心特定客戶的付款。與購買說明中一樣，無論有無客戶 ID = 7 的 WHERE。同樣，無論是 1 次還是 75 次付款，每個客戶只有 1 條記錄，每個客戶只有 1 條記錄。
所以現在，您有一個 1:1:1 的可能比率，即 1 個客戶對 1 個購買與可能 1 個付款總額。
LEFT JOIN 意味著，我想要左邊的東西（客戶），但可以選擇在右邊找到東西（購買和/或付款）。
所以 COALESCE() 防止空值在計算中出錯。如果給定的購買或付款預查詢中有一個值，則獲取它，否則，假設為零。因此，您可以只獲得一個有問題的客戶，或者通過洗掉 WHERE 子句，您可以獲得所有客戶的所有總購買量和所有總付款，以獲得所有客戶的全部未結余額。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/466822.html