訂單表
--------- ------------ ---------------- ---------- ------------
| 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 |
--------- ------------ ---------------- ---------- ------------
客戶表
------------ ---------- ------- ------- ------------- ----------------------------- ---------- --------------
| 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 |
------------ ---------- ------- ------- ------------- ----------------------------- ---------- --------------
付款表
----------- ------------ --------- ------------ ----------
| 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 |
----------- ------------ --------- ------------ ----------
我想知道哪些是已付款和未付款的客戶 Kurdapya (OrderID=91) 的 OrderID
這是我迄今為止嘗試過的查詢
嘗試1：
select if(py.OrderID=r.OrderID, 'paid','unpaid') as remarks, r.OrderID, r.CustomerID
from orders r,
payment py
where py.OrderID = r.OrderID and r.CustomerID = 91
GROUP by r.OrderID;
嘗試 1 的結果：
--------- --------- ------------
| remarks | OrderID | CustomerID |
--------- --------- ------------
| paid | 83 | 91 |
| paid | 85 | 91 |
--------- --------- ------------
嘗試2：
select if(py.OrderID=r.OrderID and py.OrderID=py.Amount!='null', 'paid','unpaid') as remarks, r.OrderID, r.CustomerID
from orders r,
payment py
where r.CustomerID = 91
GROUP by r.OrderID;
嘗試 2 的結果：
--------- --------- ------------
| remarks | OrderID | CustomerID |
--------- --------- ------------
| unpaid | 83 | 91 |
| unpaid | 85 | 91 |
| unpaid | 87 | 91 |
--------- --------- ------------
我想要的結果是這樣的：
--------- --------- ------------
| remarks | OrderID | CustomerID |
--------- --------- ------------
| unpaid | 83 | 91 |
| unpaid | 85 | 91 |
| paid | 87 | 91 |
--------- --------- ------------
uj5u.com熱心網友回復：
SELECT
IF(py.OrderID IS NULL, 'unpaid', 'paid') AS remarks,
r.OrderID,
r.CustomerID
FROM orders AS r
LEFT OUTER JOIN payment AS py USING (OrderID)
WHERE r.CustomerID = 91
但是，由于訂單表沒有金額，我不知道支付的金額是否足以支付全部訂單。
uj5u.com熱心網友回復：
你可以試試
select CASE
WHEN P.Amount > 0 then 'paid' else 'unpaid' end as remarks,
O.orderID, O.CustomerID
from Customer C inner join OrderTable O on C.CustomerID = O.CustomerID
inner join Payment P on P.OrderID = O.OrderID
where O.CustomerID = 91
uj5u.com熱心網友回復：
您的第二個查詢甚至沒有編譯。試試這個
select if(py.Amount is not null, 'paid','unpaid') as remarks, r.OrderID, r.CustomerID
from orders r
left outer join payment py on r.OrderID=py.OrderID
where r.CustomerID = 91;
注意事項：
- 我使用了現代的、顯式的 JOIN 語法 - 請參閱@jarlh 的評論
- 我使用了 LEFT OUTER 連接
- 我已經從 'null' 中洗掉了引號 - NULL 是一個特定值，而 'null' 是一個字串
- GROUP BY 是不必要的
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/466912.html