????7??SQL???????????????
??????????????????SQL???????????????????????б?????????????ЩSQL????п???????????????д?????????????Щ???????????????????
???????????????????????????????????
????-- A derived table
????FROM (SELECT * FROM author)
???????????????ЩSQL???????????????????????????????????????????
????-- A derived table with an alias
????FROM (SELECT * FROM author) a
??????????????SQL????????????????????????????????????????????????????磬?????????SELECT??WHERE?????????????б??????д??Oracle???????
????-- Get authors' first and last names?? and their age in days
????SELECT first_name?? last_name?? age
????FROM (
????SELECT first_name?? last_name?? current_date - date_of_birth age
????FROM author
????)
????-- If the age is greater than 10000 days
????WHERE age > 10000
????????Щ??????SQL:1999?????????????????????????????????????????????????????SQL SELECT????????????????????????????????????????????
????WITH a AS (
????SELECT first_name?? last_name?? current_date - date_of_birth age
????FROM author
????)
????SELECT *
????FROM a
????WHERE age > 10000
??????????????????????SQL???????????????????"a"?????????????С??????????????
??????????????????????
???????????飬SQL?????????????????????С??ú???????Щ?????á????????д????????????????????á?
????8??SQL GROUP BY????????????
?????????????????????????FROM???
????FROM a?? b
???????????????????????GROUP BY???????????????
????GROUP BY A.x?? A.y?? B.z
??????????????????????????(!)???μ?????á???????????????顣?????????GROUP BY????????????к??????????-????????м???????е??????????????????????SELECT????е?GROUP BY????????????????
???????????????????????????????????????
????SELECT A.x?? A.y?? SUM(A.z)
????FROM A
????GROUP BY A.x?? A.y
???????????????????MySQL???????????????????????????????MySQL??????GROUP BY??????????????????????????????GROUPBY???
??????????????????????
????GROUP BY???????????????????????????????±??
????9??SQL SELECT?????????б??????
????????????????????????????????????"??"????С???????????????????????????????????????????????????????С?SELECT?????????????????????????????????????????????????????????????
??????SELECT????У???????????????????????????????б?????????/?е???????
?????к???????????????????????????????????????????ü????Щ??
????1???????????“output”?????ò???????????
????2?????????GROUP BY???????????????????????????
????3?????????GROUP BY????????????????????????????
????4??????????GROUP BY?????????????????????????????
????5???????Щ?????????????????溯?????????????
????6??????…
??????????к?????????????????????????????磬??????????????????????????????????????GROUP BY??SELECT????????????
????1????????????κ????塣
????2???????SQL??????????????????????????????????????SQL:1999??????????鼯??SQL??2003?????????鼯GROUP BY()???????????????GROUP BY???????????????????????鼯??????2?????????????????????????????????????SELECT?????????????????????????????GROUP BY????????
??????????е???????????????????????Щ????ɡ?
??????????????????????
??????SQL????У?SELECT????????????????????????????????????????????????????????????????????????????????????????????????????????????????????Щ????SELECT??????????????????Щ???????????
??????????SQL?????????????????????У??????????SELECT???????????SELECT?????????????????????y????????
????10.????????SQL DISTINCT??UNION??ORDER BY????OFFSET
???????????SELECT???????????Щ?????????
????????????DISTINCT??UNION??
?????????????ORDER BY??OFFSET..FETCH??
????????????
??????????????????????????????????“??”????????????????????????????????????????????????
????DISTINCT???????????
????UNION?????????????
????UNION ALL??????????????
????EXCEPT??????????????????????????????????????е?????????????????
????INTERSECT???????????????????????е??????????????
??????????Щ??????????????????????????????????????????????????????UNION ALL??
???????????
??????????????????????????SQL???е????????????SQL????У????????????????????????????????????????????????????????????ORDER BY a??OFFSET..FETCH??????????????????????????????????????????????????????
????OFFSET..FETCH????????塣???????????MySQL'??PostgreSQL??LIMIT..OFFSET??????SQL Server??Sybase??TOP..START AT????????
??????????????e?
???????????????????????????SQL??????????????????????10????????軔????????дSQL????????塣??????棬???????????????????????????????????????г????Java????????????????дSQL??????????
????· 10 Common Mistakes Java Developers Make when Writing SQL
????· 10 More Common Mistakes Java Developers Make when Writing SQL