MySQL笔记10

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