????1??????
???????????????????????????????????????????б??????????????????????????????????????????????????????????????????WHERE????????????????DAL????????????——??????SELECT????б?д??????SQL??
????SELECT SO.order_no??(SELECT SUM(SOD.good_num) FROM [StockOrderDetails] AS SOD WHERE SOD.order_id=SO.id) AS order_num
????FROM [StockOrder] AS SO
??????????д????????￡?
????1>order_num??????SELECT?м??????
????2>order_num??????WHERE????????(SELECT?е???????????WHERE?????)
????3>????????
???????????????????????order_num??????WHERE????????
????2?????order_num??????WHERE?????????
???????SELECT?????е???????
????1>. FROM
????2>. WHERE
????3>. GROUP BY
????4>. HAVING
????5>. SELECT
????6>. ORDER BY
????WHERE?????SELECT???????У???????SELECT?е??б?????WHERE?е?????????????
????3?????
??????????????????б???????????????????????????????????????????????????
?????????????
????????????????SQL:
????SELECT SO.order_no??(SELECT SUM(SOD.good_num) FROM [StockOrderDetails] AS SOD WHERE SOD.order_id=SO.id) AS order_num
????FROM [StockOrder] AS SO
??????????????SQL:
SELECT SO.order_no??ISNULL(SOD.order_num??0) AS order_num
FROM [StockOrder] AS SO
LEFT JOIN
(
SELECT order_id??SUM(good_num) AS order_num FROM [StockOrderDetails]
GROUP BY order_id
) AS SOD ON SO.id=SOD.order_id
WHERE SOD.order_num>=8
?????????????????????????WITH AS??????????????滻 LEFT JOIN?????
;WITH StockOrderD AS(
SELECT order_id??SUM(good_num) AS order_num FROM [StockOrderDetails]
GROUP BY order_id
)
SELECT SO.order_no??ISNULL(SOD.order_num??0) AS order_num
FROM [StockOrder] AS SO
LEFT JOIN StockOrderD AS SOD ON SO.id=SOD.order_id
WHERE SOD.order_num>=8
????4?????
???????????????????????????????????
??????????????д?????????дSQL?????????????????ЩSQL??д??????????????????????????????ε????????