最近有機會去做了幾個SQL的題目，才發現自己的SQL knowledge有點弱，太多層的巢狀表格就不知道讓怎麼寫，之前大部份是小型資料庫，所以都靠Programming language 去幫忙計算。
Q: Update the field in table if condition is success
A:
UPDATE OpenOrder SET Status=1 WHERE OpenOrderId In (SELECT OpenOrder.OpenOrderId FROM OpenOrder, SettleOrder WHERE OpenOrder.OpenOrderId = SettleOrder.OpenOrderId GROUP BY SettleOrder.OpenOrderId HAVING SUM(SettleOrder.SettleQty) < OpenOrder.Qty)
Q: Calculate the profit per each customer
SELECT Client.ClientName, SUM((SettlePrice-OrderPrice)*SettleQty) AS TotalPL FROM Client, OpenOrder, SettleOrder WHERE Client.ClientId=OpenOrder.ClientId AND OpenOrder.OpenOrderId=SettleOrder.OpenOrderId GROUP BY Client.ClientId
--
後來花了一點時間再研究後，才知道我忘了HAVING的用途還有3 個 table時我就卡住了... :)
Solving problem and deep study always...
