1.subquery特性(必须有括号包围)
(1) ORDER BY 不能在subquery中使用
(2) 返回多行的SELECT或WHERE子句中的子查询必须与显式设计用于处理多个值的运算符结合使用，例如IN运算符。 否则，SELECT或WHERE语句中的子查询可以输出不超过1行。
Example 1:
SELECT * FROM exam_answers WHERE TIMESTAMPDIFF(minute,start_time,end_time) > (SELECT AVG(TIMESTAMPDIFF(minute,start_time,end_time)) AS AvgDuration FROM exam_answers WHERE TIMESTAMPDIFF(minute,start_time,end_time)>0);2.IN 运算符号
SELECT * FROM users WHERE state IN ('NC','NY'); /********same as folllows********/ SELECT * FROM users WHERE state ='NC' OR state ='NY';3. NOT IN运算符号
SELECT * FROM users WHERE state NOT IN ('NC','NY');4. EXISTS 和 NOT EXISTS 运算符号
/*If we wanted to retrieve a list of all the users in the users table who were also in the dogs table, we could write: */ SELECT DISTINCT u.user_guid AS uUserID FROM users u WHERE EXISTS (SELECT d.user_guid FROM dogs d WHERE u.user_guid =d.user_guid); /**********************************/ %%sql SELECT COUNT(DISTINCT user_guid) FROM users u WHERE NOT EXISTS (SELECT * FROM dogs d WHERE u.user_guid = d.user_guid)5.子查询
/* Queries that include subqueries always run the innermost subquery first, and then run subsequent queries sequentially in order from the innermost query to the outermost query. */ SELECT DistinctUUsersID.user_guid AS uUserID, d.user_guid AS dUserID, count(*) AS numrows FROM (SELECT DISTINCT u.user_guid FROM users u) AS DistinctUUsersID LEFT JOIN dogs d ON DistinctUUsersID.user_guid=d.user_guid GROUP BY DistinctUUsersID.user_guid ORDER BY numrows DESC子查询和JOIN结合：
%%sql SELECT DistinctUUsersID.user_guid AS uUserId, DistinctDogID.user_guid AS dUserID, COUNT(*) AS numrows FROM (SELECT DISTINCT u.user_guid FROM users u WHERE u.user_guid='ce7b75bc-7144-11e5-ba71-058fbc01cf0b') AS DistinctUUsersID LEFT JOIN (SELECT DISTINCT d.user_guid FROM dogs d) AS DistinctDogID ON DistinctUUsersID.user_guid=DistinctDogID.user_guid GROUP BY DistinctUUsersID.user_guid ORDER BY numrows DESC
转载于:https://www.cnblogs.com/Shinered/p/9649114.html
相关资源：MySQL DBA运维笔记 超详细