SQL study

最近有機會去做了幾個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...