{"id":649,"date":"2024-06-14T15:24:40","date_gmt":"2024-06-14T07:24:40","guid":{"rendered":"https:\/\/www.muxuetianyin.cn\/?p=649"},"modified":"2024-06-14T15:24:41","modified_gmt":"2024-06-14T07:24:41","slug":"%e9%ab%98%e9%a2%91-sql-50-%e9%a2%98%ef%bc%88%e5%9f%ba%e7%a1%80%e7%89%88%ef%bc%89","status":"publish","type":"post","link":"https:\/\/www.muxuetianyin.cn\/?p=649","title":{"rendered":"\u9ad8\u9891 SQL 50 \u9898\uff08\u57fa\u7840\u7248\uff09"},"content":{"rendered":"\n

1.\u53ef\u56de\u6536\u4e14\u4f4e\u8102\u7684\u4ea7\u54c1<\/h2>\n\n\n\n

\u8868\uff1aProducts<\/code><\/p>\n\n\n\n

 +-------------+---------+
 | Column Name | Type    |
 +-------------+---------+
 | product_id  | int     |
 | low_fats    | enum    |
 | recyclable  | enum    |
 +-------------+---------+
 product_id \u662f\u8be5\u8868\u7684\u4e3b\u952e\uff08\u5177\u6709\u552f\u4e00\u503c\u7684\u5217\uff09\u3002
 low_fats \u662f\u679a\u4e3e\u7c7b\u578b\uff0c\u53d6\u503c\u4e3a\u4ee5\u4e0b\u4e24\u79cd ('Y', 'N')\uff0c\u5176\u4e2d 'Y' \u8868\u793a\u8be5\u4ea7\u54c1\u662f\u4f4e\u8102\u4ea7\u54c1\uff0c'N' \u8868\u793a\u4e0d\u662f\u4f4e\u8102\u4ea7\u54c1\u3002
 recyclable \u662f\u679a\u4e3e\u7c7b\u578b\uff0c\u53d6\u503c\u4e3a\u4ee5\u4e0b\u4e24\u79cd ('Y', 'N')\uff0c\u5176\u4e2d 'Y' \u8868\u793a\u8be5\u4ea7\u54c1\u53ef\u56de\u6536\uff0c\u800c 'N' \u8868\u793a\u4e0d\u53ef\u56de\u6536\u3002<\/pre>\n\n\n\n
\u7f16\u5199\u89e3\u51b3\u65b9\u6848\u627e\u51fa\u65e2\u662f\u4f4e\u8102\u53c8\u662f\u53ef\u56de\u6536\u7684\u4ea7\u54c1\u7f16\u53f7\u3002<\/p>\n\n\n\n
\u8fd4\u56de\u7ed3\u679c \u65e0\u987a\u5e8f\u8981\u6c42<\/strong> \u3002<\/p>\n\n\n\n
\u8fd4\u56de\u7ed3\u679c\u683c\u5f0f\u5982\u4e0b\u4f8b\u6240\u793a\uff1a<\/p>\n\n\n\n
\u793a\u4f8b 1\uff1a<\/strong><\/p>\n\n\n\n
 \u8f93\u5165\uff1a
 Products \u8868\uff1a
 +-------------+----------+------------+
 | product_id  | low_fats | recyclable |
 +-------------+----------+------------+
 | 0           | Y        | N          |
 | 1           | Y        | Y          |
 | 2           | N        | Y          |
 | 3           | Y        | Y          |
 | 4           | N        | N          |
 +-------------+----------+------------+
 \u8f93\u51fa\uff1a
 +-------------+
 | product_id  |
 +-------------+
 | 1           |
 | 3           |
 +-------------+
 \u89e3\u91ca\uff1a
 \u53ea\u6709\u4ea7\u54c1 id \u4e3a 1 \u548c 3 \u7684\u4ea7\u54c1\uff0c\u65e2\u662f\u4f4e\u8102\u53c8\u662f\u53ef\u56de\u6536\u7684\u4ea7\u54c1\u3002<\/pre>\n\n\n\n
 select product_id from Products where  low_fats = 'Y' and recyclable  = 'Y'<\/pre>\n\n\n\n
2.\u5bfb\u627e\u7528\u6237\u63a8\u8350\u4eba<\/h2>\n\n\n\n
\u8868: Customer<\/code><\/p>\n\n\n\n
 +-------------+---------+
 | Column Name | Type    |
 +-------------+---------+
 | id          | int     |
 | name        | varchar |
 | referee_id  | int     |
 +-------------+---------+
 \u5728 SQL \u4e2d\uff0cid \u662f\u8be5\u8868\u7684\u4e3b\u952e\u5217\u3002
 \u8be5\u8868\u7684\u6bcf\u4e00\u884c\u8868\u793a\u4e00\u4e2a\u5ba2\u6237\u7684 id\u3001\u59d3\u540d\u4ee5\u53ca\u63a8\u8350\u4ed6\u4eec\u7684\u5ba2\u6237\u7684 id\u3002<\/pre>\n\n\n\n
\u627e\u51fa\u90a3\u4e9b \u6ca1\u6709\u88ab<\/strong> id = 2<\/code> \u7684\u5ba2\u6237 \u63a8\u8350<\/strong> \u7684\u5ba2\u6237\u7684\u59d3\u540d\u3002<\/p>\n\n\n\n
\u4ee5 \u4efb\u610f\u987a\u5e8f<\/strong> \u8fd4\u56de\u7ed3\u679c\u8868\u3002<\/p>\n\n\n\n
\u7ed3\u679c\u683c\u5f0f\u5982\u4e0b\u6240\u793a\u3002<\/p>\n\n\n\n
\u793a\u4f8b 1\uff1a<\/strong><\/p>\n\n\n\n
 \u8f93\u5165\uff1a 
 Customer \u8868:
 +----+------+------------+
 | id | name | referee_id |
 +----+------+------------+
 | 1  | Will | null       |
 | 2  | Jane | null       |
 | 3  | Alex | 2          |
 | 4  | Bill | null       |
 | 5  | Zack | 1          |
 | 6  | Mark | 2          |
 +----+------+------------+
 \u8f93\u51fa\uff1a
 +------+
 | name |
 +------+
 | Will |
 | Jane |
 | Bill |
 | Zack |
 +------+<\/pre>\n\n\n\n
\u6267\u884c\u8bed\u53e5<\/p>\n\n\n\n
 select NAME from
 Customer
 WHERE  referee_id !=2 or referee_id is null;
 \u200b
 SELECT name FROM Customer WHERE referee_id <> 2 OR referee_id IS NULL;
 \u200b
 select name from Customer where  id  not in  (select id from Customer where referee_id  = 2 )<\/pre>\n\n\n\n
3.\u5927\u7684\u56fd\u5bb6<\/h2>\n\n\n\n
World<\/code> \u8868\uff1a<\/p>\n\n\n\n
 +-------------+---------+
 | Column Name | Type    |
 +-------------+---------+
 | name        | varchar |
 | continent   | varchar |
 | area        | int     |
 | population  | int     |
 | gdp         | bigint  |
 +-------------+---------+
 name \u662f\u8be5\u8868\u7684\u4e3b\u952e\uff08\u5177\u6709\u552f\u4e00\u503c\u7684\u5217\uff09\u3002
 \u8fd9\u5f20\u8868\u7684\u6bcf\u4e00\u884c\u63d0\u4f9b\uff1a\u56fd\u5bb6\u540d\u79f0\u3001\u6240\u5c5e\u5927\u9646\u3001\u9762\u79ef\u3001\u4eba\u53e3\u548c GDP \u503c\u3002<\/pre>\n\n\n\n
\u5982\u679c\u4e00\u4e2a\u56fd\u5bb6\u6ee1\u8db3\u4e0b\u8ff0\u4e24\u4e2a\u6761\u4ef6\u4e4b\u4e00\uff0c\u5219\u8ba4\u4e3a\u8be5\u56fd\u662f \u5927\u56fd<\/strong> \uff1a<\/p>\n\n\n\n
  • \u9762\u79ef\u81f3\u5c11\u4e3a 300 \u4e07\u5e73\u65b9\u516c\u91cc\uff08\u5373\uff0c3000000 km2<\/code>\uff09\uff0c\u6216\u8005<\/li>
  • \u4eba\u53e3\u81f3\u5c11\u4e3a 2500 \u4e07\uff08\u5373 25000000<\/code>\uff09<\/li><\/ul>\n\n\n\n
    \u7f16\u5199\u89e3\u51b3\u65b9\u6848\u627e\u51fa \u5927\u56fd<\/strong> \u7684\u56fd\u5bb6\u540d\u79f0\u3001\u4eba\u53e3\u548c\u9762\u79ef\u3002<\/p>\n\n\n\n
    \u6309 \u4efb\u610f\u987a\u5e8f<\/strong> \u8fd4\u56de\u7ed3\u679c\u8868\u3002<\/p>\n\n\n\n
    \u8fd4\u56de\u7ed3\u679c\u683c\u5f0f\u5982\u4e0b\u4f8b\u6240\u793a\u3002<\/p>\n\n\n\n
    \u793a\u4f8b\uff1a<\/strong><\/p>\n\n\n\n
     \u8f93\u5165\uff1a
     World \u8868\uff1a
     +-------------+-----------+---------+------------+--------------+
     | name        | continent | area    | population | gdp          |
     +-------------+-----------+---------+------------+--------------+
     | Afghanistan | Asia      | 652230  | 25500100   | 20343000000  |
     | Albania     | Europe    | 28748   | 2831741    | 12960000000  |
     | Algeria     | Africa    | 2381741 | 37100000   | 188681000000 |
     | Andorra     | Europe    | 468     | 78115      | 3712000000   |
     | Angola      | Africa    | 1246700 | 20609294   | 100990000000 |
     +-------------+-----------+---------+------------+--------------+
     \u8f93\u51fa\uff1a
     +-------------+------------+---------+
     | name        | population | area    |
     +-------------+------------+---------+
     | Afghanistan | 25500100   | 652230  |
     | Algeria     | 37100000   | 2381741 |
     +-------------+------------+---------+<\/pre>\n\n\n\n
     select name,population,area from World where area >= 3000000 or population >= 25000000<\/pre>\n\n\n\n
    4.\u6587\u7ae0\u6d4f\u89c8 I<\/h2>\n\n\n\n
    Views<\/code> \u8868\uff1a<\/p>\n\n\n\n
     +---------------+---------+
     | Column Name   | Type    |
     +---------------+---------+
     | article_id    | int     |
     | author_id     | int     |
     | viewer_id     | int     |
     | view_date     | date    |
     +---------------+---------+
     \u6b64\u8868\u53ef\u80fd\u4f1a\u5b58\u5728\u91cd\u590d\u884c\u3002\uff08\u6362\u53e5\u8bdd\u8bf4\uff0c\u5728 SQL \u4e2d\u8fd9\u4e2a\u8868\u6ca1\u6709\u4e3b\u952e\uff09
     \u6b64\u8868\u7684\u6bcf\u4e00\u884c\u90fd\u8868\u793a\u67d0\u4eba\u5728\u67d0\u5929\u6d4f\u89c8\u4e86\u67d0\u4f4d\u4f5c\u8005\u7684\u67d0\u7bc7\u6587\u7ae0\u3002
     \u8bf7\u6ce8\u610f\uff0c\u540c\u4e00\u4eba\u7684 author_id \u548c viewer_id \u662f\u76f8\u540c\u7684\u3002<\/pre>\n\n\n\n
    \u8bf7\u67e5\u8be2\u51fa\u6240\u6709\u6d4f\u89c8\u8fc7\u81ea\u5df1\u6587\u7ae0\u7684\u4f5c\u8005<\/p>\n\n\n\n
    \u7ed3\u679c\u6309\u7167 id<\/code> \u5347\u5e8f\u6392\u5217\u3002<\/p>\n\n\n\n
    \u67e5\u8be2\u7ed3\u679c\u7684\u683c\u5f0f\u5982\u4e0b\u6240\u793a\uff1a<\/p>\n\n\n\n
    \u793a\u4f8b 1\uff1a<\/strong><\/p>\n\n\n\n
     \u8f93\u5165\uff1a
     Views \u8868\uff1a
     +------------+-----------+-----------+------------+
     | article_id | author_id | viewer_id | view_date  |
     +------------+-----------+-----------+------------+
     | 1          | 3         | 5         | 2019-08-01 |
     | 1          | 3         | 6         | 2019-08-02 |
     | 2          | 7         | 7         | 2019-08-01 |
     | 2          | 7         | 6         | 2019-08-02 |
     | 4          | 7         | 1         | 2019-07-22 |
     | 3          | 4         | 4         | 2019-07-21 |
     | 3          | 4         | 4         | 2019-07-21 |
     +------------+-----------+-----------+------------+
     \u200b
     \u8f93\u51fa\uff1a
     +------+
     | id   |
     +------+
     | 4    |
     | 7    |
     +------+<\/pre>\n\n\n\n
     select distinct author_id as id  from  Views  where author_id = viewer_id order by author_id asc
     \u200b
     select distinct author_id as id  from  Views  where author_id = viewer_id order by author_id
     \u200b<\/pre>\n\n\n\n
    5.\u65e0\u6548\u63a8\u6587<\/h2>\n\n\n\n
    \u8868\uff1aTweets<\/code><\/p>\n\n\n\n
     +----------------+---------+
     | Column Name    | Type    |
     +----------------+---------+
     | tweet_id       | int     |
     | content        | varchar |
     +----------------+---------+
     \u5728 SQL \u4e2d\uff0ctweet_id \u662f\u8fd9\u4e2a\u8868\u7684\u4e3b\u952e\u3002
     \u8fd9\u4e2a\u8868\u5305\u542b\u67d0\u793e\u4ea4\u5a92\u4f53 App \u4e2d\u6240\u6709\u7684\u63a8\u6587\u3002<\/pre>\n\n\n\n
    \u67e5\u8be2\u6240\u6709\u65e0\u6548\u63a8\u6587\u7684\u7f16\u53f7\uff08ID\uff09\u3002\u5f53\u63a8\u6587\u5185\u5bb9\u4e2d\u7684\u5b57\u7b26\u6570\u4e25\u683c\u5927\u4e8e<\/strong> 15<\/code> \u65f6\uff0c\u8be5\u63a8\u6587\u662f\u65e0\u6548\u7684\u3002<\/p>\n\n\n\n
    \u4ee5\u4efb\u610f\u987a\u5e8f<\/strong>\u8fd4\u56de\u7ed3\u679c\u8868\u3002<\/p>\n\n\n\n
    \u67e5\u8be2\u7ed3\u679c\u683c\u5f0f\u5982\u4e0b\u6240\u793a\uff1a<\/p>\n\n\n\n
    \u793a\u4f8b 1\uff1a<\/strong><\/p>\n\n\n\n
     \u8f93\u5165\uff1a
     Tweets \u8868\uff1a
     +----------+----------------------------------+
     | tweet_id | content                          |
     +----------+----------------------------------+
     | 1        | Vote for Biden                   |
     | 2        | Let us make America great again! |
     +----------+----------------------------------+
     \u200b
     \u8f93\u51fa\uff1a
     +----------+
     | tweet_id |
     +----------+
     | 2        |
     +----------+
     \u89e3\u91ca\uff1a
     \u63a8\u6587 1 \u7684\u957f\u5ea6 length = 14\u3002\u8be5\u63a8\u6587\u662f\u6709\u6548\u7684\u3002
     \u63a8\u6587 2 \u7684\u957f\u5ea6 length = 32\u3002\u8be5\u63a8\u6587\u662f\u65e0\u6548\u7684\u3002<\/pre>\n\n\n\n
     select tweet_id from Tweets where  length(content) > 15;
     \u200b
     SELECT tweet_id
     FROM Tweets
     WHERE char_length(content) > 15<\/pre>\n\n\n\n
    6.\u4f7f\u7528\u552f\u4e00\u8868\u793a\u4ee3\u66ff\u5458\u5de5id<\/h2>\n\n\n\n
    Employees<\/code> \u8868\uff1a<\/p>\n\n\n\n
     +---------------+---------+
     | Column Name   | Type    |
     +---------------+---------+
     | id            | int     |
     | name          | varchar |
     +---------------+---------+
     \u5728 SQL \u4e2d\uff0cid \u662f\u8fd9\u5f20\u8868\u7684\u4e3b\u952e\u3002
     \u8fd9\u5f20\u8868\u7684\u6bcf\u4e00\u884c\u5206\u522b\u4ee3\u8868\u4e86\u67d0\u516c\u53f8\u5176\u4e2d\u4e00\u4f4d\u5458\u5de5\u7684\u540d\u5b57\u548c ID \u3002<\/pre>\n\n\n\n
    EmployeeUNI<\/code> \u8868\uff1a<\/p>\n\n\n\n
     +---------------+---------+
     | Column Name   | Type    |
     +---------------+---------+
     | id            | int     |
     | unique_id     | int     |
     +---------------+---------+
     \u5728 SQL \u4e2d\uff0c(id, unique_id) \u662f\u8fd9\u5f20\u8868\u7684\u4e3b\u952e\u3002
     \u8fd9\u5f20\u8868\u7684\u6bcf\u4e00\u884c\u5305\u542b\u4e86\u8be5\u516c\u53f8\u67d0\u4f4d\u5458\u5de5\u7684 ID \u548c\u4ed6\u7684\u552f\u4e00\u6807\u8bc6\u7801\uff08unique ID\uff09\u3002<\/pre>\n\n\n\n
    \u5c55\u793a\u6bcf\u4f4d\u7528\u6237\u7684 \u552f\u4e00\u6807\u8bc6\u7801\uff08unique ID \uff09<\/strong>\uff1b\u5982\u679c\u67d0\u4f4d\u5458\u5de5\u6ca1\u6709\u552f\u4e00\u6807\u8bc6\u7801\uff0c\u4f7f\u7528 null \u586b\u5145\u5373\u53ef\u3002<\/p>\n\n\n\n
    \u4f60\u53ef\u4ee5\u4ee5 \u4efb\u610f<\/strong> \u987a\u5e8f\u8fd4\u56de\u7ed3\u679c\u8868\u3002<\/p>\n\n\n\n
    \u8fd4\u56de\u7ed3\u679c\u7684\u683c\u5f0f\u5982\u4e0b\u4f8b\u6240\u793a\u3002<\/p>\n\n\n\n
    \u793a\u4f8b 1\uff1a<\/strong><\/p>\n\n\n\n
     \u8f93\u5165\uff1a
     Employees \u8868:
     +----+----------+
     | id | name     |
     +----+----------+
     | 1  | Alice    |
     | 7  | Bob      |
     | 11 | Meir     |
     | 90 | Winston  |
     | 3  | Jonathan |
     +----+----------+
     EmployeeUNI \u8868:
     +----+-----------+
     | id | unique_id |
     +----+-----------+
     | 3  | 1         |
     | 11 | 2         |
     | 90 | 3         |
     +----+-----------+
     \u8f93\u51fa\uff1a
     +-----------+----------+
     | unique_id | name     |
     +-----------+----------+
     | null      | Alice    |
     | null      | Bob      |
     | 2         | Meir     |
     | 3         | Winston  |
     | 1         | Jonathan |
     +-----------+----------+
     \u89e3\u91ca\uff1a
     Alice and Bob \u6ca1\u6709\u552f\u4e00\u6807\u8bc6\u7801, \u56e0\u6b64\u6211\u4eec\u4f7f\u7528 null \u66ff\u4ee3\u3002
     Meir \u7684\u552f\u4e00\u6807\u8bc6\u7801\u662f 2 \u3002
     Winston \u7684\u552f\u4e00\u6807\u8bc6\u7801\u662f 3 \u3002
     Jonathan \u552f\u4e00\u6807\u8bc6\u7801\u662f 1 \u3002<\/pre>\n\n\n\n
     select unique_id,name  from Employees e left join EmployeeUNI eu on e.id = eu.id     <\/pre>\n\n\n\n
    7.\u4ea7\u54c1\u9500\u552e\u5206\u6790\u4e00<\/h2>\n\n\n\n
    \u9500\u552e\u8868 Sales<\/code>\uff1a<\/p>\n\n\n\n
     +-------------+-------+
     | Column Name | Type  |
     +-------------+-------+
     | sale_id     | int   |
     | product_id  | int   |
     | year        | int   |
     | quantity    | int   |
     | price       | int   |
     +-------------+-------+
     (sale_id, year) \u662f\u9500\u552e\u8868 Sales \u7684\u4e3b\u952e\uff08\u5177\u6709\u552f\u4e00\u503c\u7684\u5217\u7684\u7ec4\u5408\uff09\u3002
     product_id \u662f\u5173\u8054\u5230\u4ea7\u54c1\u8868 Product \u7684\u5916\u952e\uff08reference \u5217\uff09\u3002
     \u8be5\u8868\u7684\u6bcf\u4e00\u884c\u663e\u793a product_id \u5728\u67d0\u4e00\u5e74\u7684\u9500\u552e\u60c5\u51b5\u3002
     \u6ce8\u610f: price \u8868\u793a\u6bcf\u5355\u4f4d\u4ef7\u683c\u3002<\/pre>\n\n\n\n
    \u4ea7\u54c1\u8868 Product<\/code>\uff1a<\/p>\n\n\n\n
     +--------------+---------+
     | Column Name  | Type    |
     +--------------+---------+
     | product_id   | int     |
     | product_name | varchar |
     +--------------+---------+
     product_id \u662f\u8868\u7684\u4e3b\u952e\uff08\u5177\u6709\u552f\u4e00\u503c\u7684\u5217\uff09\u3002
     \u8be5\u8868\u7684\u6bcf\u4e00\u884c\u8868\u793a\u6bcf\u79cd\u4ea7\u54c1\u7684\u4ea7\u54c1\u540d\u79f0\u3002<\/pre>\n\n\n\n
    \u7f16\u5199\u89e3\u51b3\u65b9\u6848\uff0c\u4ee5\u83b7\u53d6 Sales<\/code> \u8868\u4e2d\u6240\u6709 sale_id<\/code> \u5bf9\u5e94\u7684 product_name<\/code> \u4ee5\u53ca\u8be5\u4ea7\u54c1\u7684\u6240\u6709 year<\/code> \u548c price<\/code> \u3002<\/p>\n\n\n\n
    \u8fd4\u56de\u7ed3\u679c\u8868 \u65e0\u987a\u5e8f\u8981\u6c42<\/strong> \u3002<\/p>\n\n\n\n
    \u7ed3\u679c\u683c\u5f0f\u793a\u4f8b\u5982\u4e0b\u3002<\/p>\n\n\n\n
    \u793a\u4f8b 1\uff1a<\/strong><\/p>\n\n\n\n
     \u8f93\u5165\uff1a
     Sales \u8868\uff1a
     +---------+------------+------+----------+-------+
     | sale_id | product_id | year | quantity | price |
     +---------+------------+------+----------+-------+ 
     | 1       | 100        | 2008 | 10       | 5000  |
     | 2       | 100        | 2009 | 12       | 5000  |
     | 7       | 200        | 2011 | 15       | 9000  |
     +---------+------------+------+----------+-------+
     Product \u8868\uff1a
     +------------+--------------+
     | product_id | product_name |
     +------------+--------------+
     | 100        | Nokia        |
     | 200        | Apple        |
     | 300        | Samsung      |
     +------------+--------------+
     \u8f93\u51fa\uff1a
     +--------------+-------+-------+
     | product_name | year  | price |
     +--------------+-------+-------+
     | Nokia        | 2008  | 5000  |
     | Nokia        | 2009  | 5000  |
     | Apple        | 2011  | 9000  |
     +--------------+-------+-------+<\/pre>\n\n\n\n
     select product_name , year , price from Sales 
      s left join Product p on p.product_id = s.product_id ;<\/pre>\n\n\n\n
    8.\u8fdb\u5e97\u5374\u672a\u4ea4\u6613\u8fc7\u7684\u5ba2\u6237<\/h2>\n\n\n\n
    \u8868\uff1aVisits<\/code><\/p>\n\n\n\n
     +-------------+---------+
     | Column Name | Type    |
     +-------------+---------+
     | visit_id    | int     |
     | customer_id | int     |
     +-------------+---------+
     visit_id \u662f\u8be5\u8868\u4e2d\u5177\u6709\u552f\u4e00\u503c\u7684\u5217\u3002
     \u8be5\u8868\u5305\u542b\u6709\u5173\u5149\u4e34\u8fc7\u8d2d\u7269\u4e2d\u5fc3\u7684\u987e\u5ba2\u7684\u4fe1\u606f\u3002<\/pre>\n\n\n\n
    \u8868\uff1aTransactions<\/code><\/p>\n\n\n\n
     +----------------+---------+
     | Column Name    | Type    |
     +----------------+---------+
     | transaction_id | int     |
     | visit_id       | int     |
     | amount         | int     |
     +----------------+---------+
     transaction_id \u662f\u8be5\u8868\u4e2d\u5177\u6709\u552f\u4e00\u503c\u7684\u5217\u3002
     \u6b64\u8868\u5305\u542b visit_id \u671f\u95f4\u8fdb\u884c\u7684\u4ea4\u6613\u7684\u4fe1\u606f\u3002<\/pre>\n\n\n\n
    \u6709\u4e00\u4e9b\u987e\u5ba2\u53ef\u80fd\u5149\u987e\u4e86\u8d2d\u7269\u4e2d\u5fc3\u4f46\u6ca1\u6709\u8fdb\u884c\u4ea4\u6613\u3002\u8bf7\u4f60\u7f16\u5199\u4e00\u4e2a\u89e3\u51b3\u65b9\u6848\uff0c\u6765\u67e5\u627e\u8fd9\u4e9b\u987e\u5ba2\u7684 ID \uff0c\u4ee5\u53ca\u4ed6\u4eec\u53ea\u5149\u987e\u4e0d\u4ea4\u6613\u7684\u6b21\u6570\u3002<\/p>\n\n\n\n
    \u8fd4\u56de\u4ee5 \u4efb\u4f55\u987a\u5e8f<\/strong> \u6392\u5e8f\u7684\u7ed3\u679c\u8868\u3002<\/p>\n\n\n\n
    \u8fd4\u56de\u7ed3\u679c\u683c\u5f0f\u5982\u4e0b\u4f8b\u6240\u793a\u3002<\/p>\n\n\n\n
    \u793a\u4f8b 1\uff1a<\/strong><\/p>\n\n\n\n
     \u8f93\u5165:
     Visits
     +----------+-------------+
     | visit_id | customer_id |
     +----------+-------------+
     | 1        | 23          |
     | 2        | 9           |
     | 4        | 30          |
     | 5        | 54          |
     | 6        | 96          |
     | 7        | 54          |
     | 8        | 54          |
     +----------+-------------+
     Transactions
     +----------------+----------+--------+
     | transaction_id | visit_id | amount |
     +----------------+----------+--------+
     | 2              | 5        | 310    |
     | 3              | 5        | 300    |
     | 9              | 5        | 200    |
     | 12             | 1        | 910    |
     | 13             | 2        | 970    |
     +----------------+----------+--------+
     \u8f93\u51fa:
     +-------------+----------------+
     | customer_id | count_no_trans |
     +-------------+----------------+
     | 54          | 2              |
     | 30          | 1              |
     | 96          | 1              |
     +-------------+----------------+
     \u89e3\u91ca:
     ID = 23 \u7684\u987e\u5ba2\u66fe\u7ecf\u901b\u8fc7\u4e00\u6b21\u8d2d\u7269\u4e2d\u5fc3\uff0c\u5e76\u5728 ID = 12 \u7684\u8bbf\u95ee\u671f\u95f4\u8fdb\u884c\u4e86\u4e00\u7b14\u4ea4\u6613\u3002
     ID = 9 \u7684\u987e\u5ba2\u66fe\u7ecf\u901b\u8fc7\u4e00\u6b21\u8d2d\u7269\u4e2d\u5fc3\uff0c\u5e76\u5728 ID = 13 \u7684\u8bbf\u95ee\u671f\u95f4\u8fdb\u884c\u4e86\u4e00\u7b14\u4ea4\u6613\u3002
     ID = 30 \u7684\u987e\u5ba2\u66fe\u7ecf\u53bb\u8fc7\u8d2d\u7269\u4e2d\u5fc3\uff0c\u5e76\u4e14\u6ca1\u6709\u8fdb\u884c\u4efb\u4f55\u4ea4\u6613\u3002
     ID = 54 \u7684\u987e\u5ba2\u4e09\u5ea6\u9020\u8bbf\u4e86\u8d2d\u7269\u4e2d\u5fc3\u3002\u5728 2 \u6b21\u8bbf\u95ee\u4e2d\uff0c\u4ed6\u4eec\u6ca1\u6709\u8fdb\u884c\u4efb\u4f55\u4ea4\u6613\uff0c\u5728 1 \u6b21\u8bbf\u95ee\u4e2d\uff0c\u4ed6\u4eec\u8fdb\u884c\u4e86 3 \u6b21\u4ea4\u6613\u3002
     ID = 96 \u7684\u987e\u5ba2\u66fe\u7ecf\u53bb\u8fc7\u8d2d\u7269\u4e2d\u5fc3\uff0c\u5e76\u4e14\u6ca1\u6709\u8fdb\u884c\u4efb\u4f55\u4ea4\u6613\u3002
     \u5982\u6211\u4eec\u6240\u89c1\uff0cID \u4e3a 30 \u548c 96 \u7684\u987e\u5ba2\u4e00\u6b21\u6ca1\u6709\u8fdb\u884c\u4efb\u4f55\u4ea4\u6613\u5c31\u53bb\u4e86\u8d2d\u7269\u4e2d\u5fc3\u3002\u987e\u5ba2 54 \u4e5f\u4e24\u6b21\u8bbf\u95ee\u4e86\u8d2d\u7269\u4e2d\u5fc3\u5e76\u4e14\u6ca1\u6709\u8fdb\u884c\u4efb\u4f55\u4ea4\u6613\u3002<\/pre>\n\n\n\n
     select v.customer_id,count(v.customer_id) as count_no_trans from Visits v left join Transactions t on v.visit_id  = t.visit_id where t.transaction_id  is null group by 
         v.customer_id;<\/pre>\n\n\n\n
    9\u4e0a\u5347\u7684\u6e29\u5ea6*<\/h2>\n\n\n\n
    \u8868\uff1a Weather<\/code><\/p>\n\n\n\n
     +---------------+---------+
     | Column Name   | Type    |
     +---------------+---------+
     | id            | int     |
     | recordDate    | date    |
     | temperature   | int     |
     +---------------+---------+
     id \u662f\u8be5\u8868\u5177\u6709\u552f\u4e00\u503c\u7684\u5217\u3002
     \u6ca1\u6709\u5177\u6709\u76f8\u540c recordDate \u7684\u4e0d\u540c\u884c\u3002
     \u8be5\u8868\u5305\u542b\u7279\u5b9a\u65e5\u671f\u7684\u6e29\u5ea6\u4fe1\u606f<\/pre>\n\n\n\n
    \u7f16\u5199\u89e3\u51b3\u65b9\u6848\uff0c\u627e\u51fa\u4e0e\u4e4b\u524d\uff08\u6628\u5929\u7684\uff09\u65e5\u671f\u76f8\u6bd4\u6e29\u5ea6\u66f4\u9ad8\u7684\u6240\u6709\u65e5\u671f\u7684 id<\/code> \u3002<\/p>\n\n\n\n
    \u8fd4\u56de\u7ed3\u679c \u65e0\u987a\u5e8f\u8981\u6c42<\/strong> \u3002<\/p>\n\n\n\n
    \u7ed3\u679c\u683c\u5f0f\u5982\u4e0b\u4f8b\u5b50\u6240\u793a\u3002<\/p>\n\n\n\n
    \u793a\u4f8b 1\uff1a<\/strong><\/p>\n\n\n\n
     \u8f93\u5165\uff1a
     Weather \u8868\uff1a
     +----+------------+-------------+
     | id | recordDate | Temperature |
     +----+------------+-------------+
     | 1  | 2015-01-01 | 10          |
     | 2  | 2015-01-02 | 25          |
     | 3  | 2015-01-03 | 20          |
     | 4  | 2015-01-04 | 30          |
     +----+------------+-------------+
     \u8f93\u51fa\uff1a
     +----+
     | id |
     +----+
     | 2  |
     | 4  |
     +----+
     \u89e3\u91ca\uff1a
     2015-01-02 \u7684\u6e29\u5ea6\u6bd4\u524d\u4e00\u5929\u9ad8\uff0810 -> 25\uff09
     2015-01-04 \u7684\u6e29\u5ea6\u6bd4\u524d\u4e00\u5929\u9ad8\uff0820 -> 30\uff09<\/pre>\n\n\n\n
     date_add\u662f\u5bf9\u65e5\u671f\u7684\u589e\u52a0\uff0c\u5982\u679c\u5929\u6570\u4e3a\u8d1f\u6570\u65f6\uff0c\u5219\u8868\u793a\u5bf9\u65e5\u671f\u51cf\u5c11\uff0c
     date_sub\u662f\u5bf9\u65e5\u671f\u7684\u51cf\u5c11\uff0c\u5982\u679c\u5929\u6570\u4e3a\u8d1f\u6570\u65f6\uff0c\u5219\u8868\u793a\u5bf9\u65e5\u671f\u589e\u52a0
     \u200b
     \u200b
     SELECT 
         t1.id
     FROM 
         Weather t1
     JOIN 
     # \u8fde\u63a5\uff0ct1\u8868\u548ct2\u52a0\u4e00\u5929\u65f6\u95f4\u76f8\u540c\u7684\u65e5\u671f\uff0c\u7136\u540e\u5f97\u5230t1\u662f\u7b2c\u4e8c\u5929\u65f6\u95f4\uff0c\u7136\u540et1\u518d\u52a0\u6761\u4ef6\u662ft1\u300bt2\u5c31\u53ef\u4ee5\u63a8\u7b97\u51fa\u6765\u6bd4\u524d\u4e00\u5929\u9ad8\u7684id
         Weather t2 ON t1.recordDate = DATE_ADD(t2.recordDate, INTERVAL 1 DAY)
     WHERE 
         t1.temperature > t2.temperature;<\/pre>\n\n\n\n
    10\u6bcf\u53f0\u673a\u5668\u8fdb\u7a0b\u7684\u5e73\u5747\u65f6\u95f4*<\/h2>\n\n\n\n
    \u8868: Activity<\/code><\/p>\n\n\n\n
     +----------------+---------+
     | Column Name    | Type    |
     +----------------+---------+
     | machine_id     | int     |
     | process_id     | int     |
     | activity_type  | enum    |
     | timestamp      | float   |
     +----------------+---------+
     \u8be5\u8868\u5c55\u793a\u4e86\u4e00\u5bb6\u5de5\u5382\u7f51\u7ad9\u7684\u7528\u6237\u6d3b\u52a8\u3002
     (machine_id, process_id, activity_type) \u662f\u5f53\u524d\u8868\u7684\u4e3b\u952e\uff08\u5177\u6709\u552f\u4e00\u503c\u7684\u5217\u7684\u7ec4\u5408\uff09\u3002
     machine_id \u662f\u4e00\u53f0\u673a\u5668\u7684ID\u53f7\u3002
     process_id \u662f\u8fd0\u884c\u5728\u5404\u673a\u5668\u4e0a\u7684\u8fdb\u7a0bID\u53f7\u3002
     activity_type \u662f\u679a\u4e3e\u7c7b\u578b ('start', 'end')\u3002
     timestamp \u662f\u6d6e\u70b9\u7c7b\u578b,\u4ee3\u8868\u5f53\u524d\u65f6\u95f4(\u4ee5\u79d2\u4e3a\u5355\u4f4d)\u3002
     'start' \u4ee3\u8868\u8be5\u8fdb\u7a0b\u5728\u8fd9\u53f0\u673a\u5668\u4e0a\u7684\u5f00\u59cb\u8fd0\u884c\u65f6\u95f4\u6233 , 'end' \u4ee3\u8868\u8be5\u8fdb\u7a0b\u5728\u8fd9\u53f0\u673a\u5668\u4e0a\u7684\u7ec8\u6b62\u8fd0\u884c\u65f6\u95f4\u6233\u3002
     \u540c\u4e00\u53f0\u673a\u5668\uff0c\u540c\u4e00\u4e2a\u8fdb\u7a0b\u90fd\u6709\u4e00\u5bf9\u5f00\u59cb\u65f6\u95f4\u6233\u548c\u7ed3\u675f\u65f6\u95f4\u6233\uff0c\u800c\u4e14\u5f00\u59cb\u65f6\u95f4\u6233\u6c38\u8fdc\u5728\u7ed3\u675f\u65f6\u95f4\u6233\u524d\u9762\u3002<\/pre>\n\n\n\n
    \u73b0\u5728\u6709\u4e00\u4e2a\u5de5\u5382\u7f51\u7ad9\u7531\u51e0\u53f0\u673a\u5668\u8fd0\u884c\uff0c\u6bcf\u53f0\u673a\u5668\u4e0a\u8fd0\u884c\u7740 \u76f8\u540c\u6570\u91cf\u7684\u8fdb\u7a0b<\/strong> \u3002\u7f16\u5199\u89e3\u51b3\u65b9\u6848\uff0c\u8ba1\u7b97\u6bcf\u53f0\u673a\u5668\u5404\u81ea\u5b8c\u6210\u4e00\u4e2a\u8fdb\u7a0b\u4efb\u52a1\u7684\u5e73\u5747\u8017\u65f6\u3002<\/p>\n\n\n\n
    \u5b8c\u6210\u4e00\u4e2a\u8fdb\u7a0b\u4efb\u52a1\u7684\u65f6\u95f4\u6307\u8fdb\u7a0b\u7684'end' \u65f6\u95f4\u6233<\/code> \u51cf\u53bb 'start' \u65f6\u95f4\u6233<\/code>\u3002\u5e73\u5747\u8017\u65f6\u901a\u8fc7\u8ba1\u7b97\u6bcf\u53f0\u673a\u5668\u4e0a\u6240\u6709\u8fdb\u7a0b\u4efb\u52a1\u7684\u603b\u8017\u8d39\u65f6\u95f4\u9664\u4ee5\u673a\u5668\u4e0a\u7684\u603b\u8fdb\u7a0b\u6570\u91cf\u83b7\u5f97\u3002<\/p>\n\n\n\n
    \u7ed3\u679c\u8868\u5fc5\u987b\u5305\u542bmachine_id\uff08\u673a\u5668ID\uff09<\/code> \u548c\u5bf9\u5e94\u7684 average time\uff08\u5e73\u5747\u8017\u65f6\uff09<\/strong> \u522b\u540d processing_time<\/code>\uff0c\u4e14\u56db\u820d\u4e94\u5165\u4fdd\u75593\u4f4d\u5c0f\u6570\u3002<\/strong><\/p>\n\n\n\n
    \u4ee5 \u4efb\u610f\u987a\u5e8f<\/strong> \u8fd4\u56de\u8868\u3002<\/p>\n\n\n\n
    \u5177\u4f53\u53c2\u8003\u4f8b\u5b50\u5982\u4e0b\u3002<\/p>\n\n\n\n
    \u793a\u4f8b 1:<\/strong><\/p>\n\n\n\n
     \u8f93\u5165\uff1a
     Activity table:
     +------------+------------+---------------+-----------+
     | machine_id | process_id | activity_type | timestamp |
     +------------+------------+---------------+-----------+
     | 0          | 0          | start         | 0.712     |
     | 0          | 0          | end           | 1.520     |
     | 0          | 1          | start         | 3.140     |
     | 0          | 1          | end           | 4.120     |
     | 1          | 0          | start         | 0.550     |
     | 1          | 0          | end           | 1.550     |
     | 1          | 1          | start         | 0.430     |
     | 1          | 1          | end           | 1.420     |
     | 2          | 0          | start         | 4.100     |
     | 2          | 0          | end           | 4.512     |
     | 2          | 1          | start         | 2.500     |
     | 2          | 1          | end           | 5.000     |
     +------------+------------+---------------+-----------+
     \u8f93\u51fa\uff1a
     +------------+-----------------+
     | machine_id | processing_time |
     +------------+-----------------+
     | 0          | 0.894           |
     | 1          | 0.995           |
     | 2          | 1.456           |
     +------------+-----------------+
     \u89e3\u91ca\uff1a
     \u4e00\u5171\u67093\u53f0\u673a\u5668,\u6bcf\u53f0\u673a\u5668\u8fd0\u884c\u7740\u4e24\u4e2a\u8fdb\u7a0b.
     \u673a\u5668 0 \u7684\u5e73\u5747\u8017\u65f6: ((1.520 - 0.712) + (4.120 - 3.140)) \/ 2 = 0.894
     \u673a\u5668 1 \u7684\u5e73\u5747\u8017\u65f6: ((1.550 - 0.550) + (1.420 - 0.430)) \/ 2 = 0.995
     \u673a\u5668 2 \u7684\u5e73\u5747\u8017\u65f6: ((4.512 - 4.100) + (5.000 - 2.500)) \/ 2 = 1.456<\/pre>\n\n\n\n
     \u200b
     SELECT 
         machine_id, 
         ROUND(AVG(end_time - start_time), 3) AS processing_time
     FROM (
         SELECT 
             start.machine_id,
             start.process_id,
             start.timestamp AS start_time,
             end.timestamp AS end_time
         FROM 
             Activity AS start
         JOIN 
             Activity AS end 
         ON 
             start.machine_id = end.machine_id 
             AND start.process_id = end.process_id 
             AND start.activity_type = 'start' 
             AND end.activity_type = 'end'
     ) AS process_times
     GROUP BY 
         machine_id;<\/pre>\n\n\n\n
    11.\u5458\u5de5\u5956\u91d1<\/h2>\n\n\n\n
    \u8868\uff1aEmployee<\/code><\/p>\n\n\n\n
     +-------------+---------+
     | Column Name | Type    |
     +-------------+---------+
     | empId       | int     |
     | name        | varchar |
     | supervisor  | int     |
     | salary      | int     |
     +-------------+---------+
     empId \u662f\u8be5\u8868\u4e2d\u5177\u6709\u552f\u4e00\u503c\u7684\u5217\u3002
     \u8be5\u8868\u7684\u6bcf\u4e00\u884c\u90fd\u8868\u793a\u5458\u5de5\u7684\u59d3\u540d\u548c id\uff0c\u4ee5\u53ca\u4ed6\u4eec\u7684\u5de5\u8d44\u548c\u7ecf\u7406\u7684 id\u3002<\/pre>\n\n\n\n
    \u8868\uff1aBonus<\/code><\/p>\n\n\n\n
     +-------------+------+
     | Column Name | Type |
     +-------------+------+
     | empId       | int  |
     | bonus       | int  |
     +-------------+------+
     empId \u662f\u8be5\u8868\u5177\u6709\u552f\u4e00\u503c\u7684\u5217\u3002
     empId \u662f Employee \u8868\u4e2d empId \u7684\u5916\u952e(reference \u5217)\u3002
     \u8be5\u8868\u7684\u6bcf\u4e00\u884c\u90fd\u5305\u542b\u4e00\u4e2a\u5458\u5de5\u7684 id \u548c\u4ed6\u4eec\u5404\u81ea\u7684\u5956\u91d1\u3002<\/pre>\n\n\n\n
    \u7f16\u5199\u89e3\u51b3\u65b9\u6848\uff0c\u62a5\u544a\u6bcf\u4e2a\u5956\u91d1 \u5c11\u4e8e<\/strong> 1000<\/code> \u7684\u5458\u5de5\u7684\u59d3\u540d\u548c\u5956\u91d1\u6570\u989d\u3002<\/p>\n\n\n\n
    \u4ee5 \u4efb\u610f\u987a\u5e8f<\/strong> \u8fd4\u56de\u7ed3\u679c\u8868\u3002<\/p>\n\n\n\n
    \u7ed3\u679c\u683c\u5f0f\u5982\u4e0b\u6240\u793a\u3002<\/p>\n\n\n\n
    \u793a\u4f8b 1\uff1a<\/strong><\/p>\n\n\n\n
     \u8f93\u5165\uff1a
     Employee table:
     +-------+--------+------------+--------+
     | empId | name   | supervisor | salary |
     +-------+--------+------------+--------+
     | 3     | Brad   | null       | 4000   |
     | 1     | John   | 3          | 1000   |
     | 2     | Dan    | 3          | 2000   |
     | 4     | Thomas | 3          | 4000   |
     +-------+--------+------------+--------+
     Bonus table:
     +-------+-------+
     | empId | bonus |
     +-------+-------+
     | 2     | 500   |
     | 4     | 2000  |
     +-------+-------+
     \u8f93\u51fa\uff1a
     +------+-------+
     | name | bonus |
     +------+-------+
     | Brad | null  |
     | John | null  |
     | Dan  | 500   |
     +------+-------+<\/pre>\n\n\n\n
     select name,bonus  from Employee e left join Bonus b on  e.empId = b.empId where b.bonus < 1000 or b.bonus is null<\/pre>\n\n\n\n
    12.\u5b66\u751f\u4eec\u53c2\u52a0\u5404\u79d1\u6d4b\u8bd5\u7684\u6b21\u6570*<\/h2>\n\n\n\n
    \u5b66\u751f\u8868: Students<\/code><\/p>\n\n\n\n
     +---------------+---------+
     | Column Name   | Type    |
     +---------------+---------+
     | student_id    | int     |
     | student_name  | varchar |
     +---------------+---------+
     \u5728 SQL \u4e2d\uff0c\u4e3b\u952e\u4e3a student_id\uff08\u5b66\u751fID\uff09\u3002
     \u8be5\u8868\u5185\u7684\u6bcf\u4e00\u884c\u90fd\u8bb0\u5f55\u6709\u5b66\u6821\u4e00\u540d\u5b66\u751f\u7684\u4fe1\u606f\u3002<\/pre>\n\n\n\n
    \u79d1\u76ee\u8868: Subjects<\/code><\/p>\n\n\n\n
     +--------------+---------+
     | Column Name  | Type    |
     +--------------+---------+
     | subject_name | varchar |
     +--------------+---------+
     \u5728 SQL \u4e2d\uff0c\u4e3b\u952e\u4e3a subject_name\uff08\u79d1\u76ee\u540d\u79f0\uff09\u3002
     \u6bcf\u4e00\u884c\u8bb0\u5f55\u5b66\u6821\u7684\u4e00\u95e8\u79d1\u76ee\u540d\u79f0\u3002<\/pre>\n\n\n\n
    \u8003\u8bd5\u8868: Examinations<\/code><\/p>\n\n\n\n
     +--------------+---------+
     | Column Name  | Type    |
     +--------------+---------+
     | student_id   | int     |
     | subject_name | varchar |
     +--------------+---------+
     \u8fd9\u4e2a\u8868\u53ef\u80fd\u5305\u542b\u91cd\u590d\u6570\u636e\uff08\u6362\u53e5\u8bdd\u8bf4\uff0c\u5728 SQL \u4e2d\uff0c\u8fd9\u4e2a\u8868\u6ca1\u6709\u4e3b\u952e\uff09\u3002
     \u5b66\u751f\u8868\u91cc\u7684\u4e00\u4e2a\u5b66\u751f\u4fee\u8bfb\u79d1\u76ee\u8868\u91cc\u7684\u6bcf\u4e00\u95e8\u79d1\u76ee\u3002
     \u8fd9\u5f20\u8003\u8bd5\u8868\u7684\u6bcf\u4e00\u884c\u8bb0\u5f55\u5c31\u8868\u793a\u5b66\u751f\u8868\u91cc\u7684\u67d0\u4e2a\u5b66\u751f\u53c2\u52a0\u4e86\u4e00\u6b21\u79d1\u76ee\u8868\u91cc\u67d0\u95e8\u79d1\u76ee\u7684\u6d4b\u8bd5\u3002<\/pre>\n\n\n\n
    \u67e5\u8be2\u51fa\u6bcf\u4e2a\u5b66\u751f\u53c2\u52a0\u6bcf\u4e00\u95e8\u79d1\u76ee\u6d4b\u8bd5\u7684\u6b21\u6570\uff0c\u7ed3\u679c\u6309 student_id<\/code> \u548c subject_name<\/code> \u6392\u5e8f\u3002<\/p>\n\n\n\n
    \u67e5\u8be2\u7ed3\u6784\u683c\u5f0f\u5982\u4e0b\u6240\u793a\u3002<\/p>\n\n\n\n
    \u793a\u4f8b 1\uff1a<\/strong><\/p>\n\n\n\n
     \u8f93\u5165\uff1a
     Students table:
     +------------+--------------+
     | student_id | student_name |
     +------------+--------------+
     | 1          | Alice        |
     | 2          | Bob          |
     | 13         | John         |
     | 6          | Alex         |
     +------------+--------------+
     Subjects table:
     +--------------+
     | subject_name |
     +--------------+
     | Math         |
     | Physics      |
     | Programming  |
     +--------------+
     Examinations table:
     +------------+--------------+
     | student_id | subject_name |
     +------------+--------------+
     | 1          | Math         |
     | 1          | Physics      |
     | 1          | Programming  |
     | 2          | Programming  |
     | 1          | Physics      |
     | 1          | Math         |
     | 13         | Math         |
     | 13         | Programming  |
     | 13         | Physics      |
     | 2          | Math         |
     | 1          | Math         |
     +------------+--------------+
     \u8f93\u51fa\uff1a
     +------------+--------------+--------------+----------------+
     | student_id | student_name | subject_name | attended_exams |
     +------------+--------------+--------------+----------------+
     | 1          | Alice        | Math         | 3              |
     | 1          | Alice        | Physics      | 2              |
     | 1          | Alice        | Programming  | 1              |
     | 2          | Bob          | Math         | 1              |
     | 2          | Bob          | Physics      | 0              |
     | 2          | Bob          | Programming  | 1              |
     | 6          | Alex         | Math         | 0              |
     | 6          | Alex         | Physics      | 0              |
     | 6          | Alex         | Programming  | 0              |
     | 13         | John         | Math         | 1              |
     | 13         | John         | Physics      | 1              |
     | 13         | John         | Programming  | 1              |
     +------------+--------------+--------------+----------------+
     \u89e3\u91ca\uff1a
     \u7ed3\u679c\u8868\u9700\u5305\u542b\u6240\u6709\u5b66\u751f\u548c\u6240\u6709\u79d1\u76ee\uff08\u5373\u4fbf\u6d4b\u8bd5\u6b21\u6570\u4e3a0\uff09\uff1a
     Alice \u53c2\u52a0\u4e86 3 \u6b21\u6570\u5b66\u6d4b\u8bd5, 2 \u6b21\u7269\u7406\u6d4b\u8bd5\uff0c\u4ee5\u53ca 1 \u6b21\u7f16\u7a0b\u6d4b\u8bd5\uff1b
     Bob \u53c2\u52a0\u4e86 1 \u6b21\u6570\u5b66\u6d4b\u8bd5, 1 \u6b21\u7f16\u7a0b\u6d4b\u8bd5\uff0c\u6ca1\u6709\u53c2\u52a0\u7269\u7406\u6d4b\u8bd5\uff1b
     Alex \u5565\u6d4b\u8bd5\u90fd\u6ca1\u53c2\u52a0\uff1b
     John  \u53c2\u52a0\u4e86\u6570\u5b66\u3001\u7269\u7406\u3001\u7f16\u7a0b\u6d4b\u8bd5\u5404 1 \u6b21\u3002<\/pre>\n\n\n\n
     # Write your MySQL query statement below
     SELECT 
         s.student_id,
         s.student_name,
         sub.subject_name,
         COALESCE(COUNT(e.subject_name), 0) AS attended_exams
     from 
         Students s
     JOIN 
         Subjects sub
     LEFT JOIN 
         Examinations e ON s.student_id = e.student_id AND sub.subject_name = e.subject_name
     group by s.student_id,s.student_name,sub.subject_name 
     \u200b
     order by s.student_id,sub.subject_name <\/pre>\n\n\n\n
    13\u81f3\u5c11\u53c8\u4e94\u540d\u76f4\u63a5\u4e0b\u5c5e\u7684\u7ecf\u7406<\/h2>\n\n\n\n
    \u8868: Employee<\/code><\/p>\n\n\n\n
     +-------------+---------+
     | Column Name | Type    |
     +-------------+---------+
     | id          | int     |
     | name        | varchar |
     | department  | varchar |
     | managerId   | int     |
     +-------------+---------+
     id \u662f\u6b64\u8868\u7684\u4e3b\u952e\uff08\u5177\u6709\u552f\u4e00\u503c\u7684\u5217\uff09\u3002
     \u8be5\u8868\u7684\u6bcf\u4e00\u884c\u8868\u793a\u96c7\u5458\u7684\u540d\u5b57\u3001\u4ed6\u4eec\u7684\u90e8\u95e8\u548c\u4ed6\u4eec\u7684\u7ecf\u7406\u7684id\u3002
     \u5982\u679cmanagerId\u4e3a\u7a7a\uff0c\u5219\u8be5\u5458\u5de5\u6ca1\u6709\u7ecf\u7406\u3002
     \u6ca1\u6709\u5458\u5de5\u4f1a\u6210\u4e3a\u81ea\u5df1\u7684\u7ba1\u7406\u8005\u3002<\/pre>\n\n\n\n
    \u7f16\u5199\u4e00\u4e2a\u89e3\u51b3\u65b9\u6848\uff0c\u627e\u51fa\u81f3\u5c11\u6709\u4e94\u4e2a\u76f4\u63a5\u4e0b\u5c5e<\/strong>\u7684\u7ecf\u7406\u3002<\/p>\n\n\n\n
    \u4ee5 \u4efb\u610f\u987a\u5e8f<\/strong> \u8fd4\u56de\u7ed3\u679c\u8868\u3002<\/p>\n\n\n\n
    \u67e5\u8be2\u7ed3\u679c\u683c\u5f0f\u5982\u4e0b\u6240\u793a\u3002<\/p>\n\n\n\n
    \u793a\u4f8b 1:<\/strong><\/p>\n\n\n\n
     \u8f93\u5165: 
     Employee \u8868:
     +-----+-------+------------+-----------+
     | id  | name  | department | managerId |
     +-----+-------+------------+-----------+
     | 101 | John  | A          | Null      |
     | 102 | Dan   | A          | 101       |
     | 103 | James | A          | 101       |
     | 104 | Amy   | A          | 101       |
     | 105 | Anne  | A          | 101       |
     | 106 | Ron   | B          | 101       |
     +-----+-------+------------+-----------+
     \u8f93\u51fa: 
     +------+
     | name |
     +------+
     | John |
     +------+<\/pre>\n\n\n\n
     SELECT 
         e.name
     FROM 
         Employee e
     JOIN 
         Employee sub ON e.id = sub.managerId
     GROUP BY 
         e.id, e.name
     HAVING 
         COUNT(sub.id) >= 5;
     \u200b
     # Write your MySQL query statement below
     SELECT name 
     FROM Employee
     WHERE id IN (
         SELECT DISTINCT ManagerId
         FROM Employee
         GROUP BY ManagerID
         HAVING COUNT(ManagerID) >= 5
     )
     \u200b
     \u200b
     # Write your MySQL query statement below
     select name from 
     (select coalesce(count(e2.managerId),0) as num,e1.name from Employee e1 join Employee e2 on e1.id = e2.managerId group by e1.id) as Employee where num>=5<\/pre>\n\n\n\n
    14.\u786e\u8ba4\u7387<\/h2>\n\n\n\n
    \u8868: Signups<\/code><\/p>\n\n\n\n
     +----------------+----------+
     | Column Name    | Type     |
     +----------------+----------+
     | user_id        | int      |
     | time_stamp     | datetime |
     +----------------+----------+
     User_id\u662f\u8be5\u8868\u7684\u4e3b\u952e\u3002
     \u6bcf\u4e00\u884c\u90fd\u5305\u542bID\u4e3auser_id\u7684\u7528\u6237\u7684\u6ce8\u518c\u65f6\u95f4\u4fe1\u606f\u3002<\/pre>\n\n\n\n
    \u8868: Confirmations<\/code><\/p>\n\n\n\n
     +----------------+----------+
     | Column Name    | Type     |
     +----------------+----------+
     | user_id        | int      |
     | time_stamp     | datetime |
     | action         | ENUM     |
     +----------------+----------+
     (user_id, time_stamp)\u662f\u8be5\u8868\u7684\u4e3b\u952e\u3002
     user_id\u662f\u4e00\u4e2a\u5f15\u7528\u5230\u6ce8\u518c\u8868\u7684\u5916\u952e\u3002
     action\u662f\u7c7b\u578b\u4e3a('confirmed'\uff0c 'timeout')\u7684ENUM
     \u8be5\u8868\u7684\u6bcf\u4e00\u884c\u90fd\u8868\u793aID\u4e3auser_id\u7684\u7528\u6237\u5728time_stamp\u8bf7\u6c42\u4e86\u4e00\u6761\u786e\u8ba4\u6d88\u606f\uff0c\u8be5\u786e\u8ba4\u6d88\u606f\u8981\u4e48\u88ab\u786e\u8ba4('confirmed')\uff0c\u8981\u4e48\u88ab\u8fc7\u671f('timeout')\u3002<\/pre>\n\n\n\n
    \u7528\u6237\u7684 \u786e\u8ba4\u7387<\/strong> \u662f 'confirmed'<\/code> \u6d88\u606f\u7684\u6570\u91cf\u9664\u4ee5\u8bf7\u6c42\u7684\u786e\u8ba4\u6d88\u606f\u7684\u603b\u6570\u3002\u6ca1\u6709\u8bf7\u6c42\u4efb\u4f55\u786e\u8ba4\u6d88\u606f\u7684\u7528\u6237\u7684\u786e\u8ba4\u7387\u4e3a 0<\/code> \u3002\u786e\u8ba4\u7387\u56db\u820d\u4e94\u5165\u5230 \u5c0f\u6570\u70b9\u540e\u4e24\u4f4d<\/strong> \u3002<\/p>\n\n\n\n
    \u7f16\u5199\u4e00\u4e2aSQL\u67e5\u8be2\u6765\u67e5\u627e\u6bcf\u4e2a\u7528\u6237\u7684 \u786e\u8ba4\u7387 \u3002<\/p>\n\n\n\n
    \u4ee5 \u4efb\u610f\u987a\u5e8f \u8fd4\u56de\u7ed3\u679c\u8868\u3002<\/p>\n\n\n\n
    \u67e5\u8be2\u7ed3\u679c\u683c\u5f0f\u5982\u4e0b\u6240\u793a\u3002<\/p>\n\n\n\n
    \u793a\u4f8b1:<\/strong><\/p>\n\n\n\n
     \u8f93\u5165\uff1a
     Signups \u8868:
     +---------+---------------------+
     | user_id | time_stamp          |
     +---------+---------------------+
     | 3       | 2020-03-21 10:16:13 |
     | 7       | 2020-01-04 13:57:59 |
     | 2       | 2020-07-29 23:09:44 |
     | 6       | 2020-12-09 10:39:37 |
     +---------+---------------------+
     Confirmations \u8868:
     +---------+---------------------+-----------+
     | user_id | time_stamp          | action    |
     +---------+---------------------+-----------+
     | 3       | 2021-01-06 03:30:46 | timeout   |
     | 3       | 2021-07-14 14:00:00 | timeout   |
     | 7       | 2021-06-12 11:57:29 | confirmed |
     | 7       | 2021-06-13 12:58:28 | confirmed |
     | 7       | 2021-06-14 13:59:27 | confirmed |
     | 2       | 2021-01-22 00:00:00 | confirmed |
     | 2       | 2021-02-28 23:59:59 | timeout   |
     +---------+---------------------+-----------+
     \u8f93\u51fa: 
     +---------+-------------------+
     | user_id | confirmation_rate |
     +---------+-------------------+
     | 6       | 0.00              |
     | 3       | 0.00              |
     | 7       | 1.00              |
     | 2       | 0.50              |
     +---------+-------------------+
     \u89e3\u91ca:
     \u7528\u6237 6 \u6ca1\u6709\u8bf7\u6c42\u4efb\u4f55\u786e\u8ba4\u6d88\u606f\u3002\u786e\u8ba4\u7387\u4e3a 0\u3002
     \u7528\u6237 3 \u8fdb\u884c\u4e86 2 \u6b21\u8bf7\u6c42\uff0c\u90fd\u8d85\u65f6\u4e86\u3002\u786e\u8ba4\u7387\u4e3a 0\u3002
     \u7528\u6237 7 \u63d0\u51fa\u4e86 3 \u4e2a\u8bf7\u6c42\uff0c\u6240\u6709\u8bf7\u6c42\u90fd\u5f97\u5230\u4e86\u786e\u8ba4\u3002\u786e\u8ba4\u7387\u4e3a 1\u3002
     \u7528\u6237 2 \u505a\u4e86 2 \u4e2a\u8bf7\u6c42\uff0c\u5176\u4e2d\u4e00\u4e2a\u88ab\u786e\u8ba4\uff0c\u53e6\u4e00\u4e2a\u8d85\u65f6\u3002\u786e\u8ba4\u7387\u4e3a 1 \/ 2 = 0.5\u3002<\/pre>\n","protected":false},"excerpt":{"rendered":"
    1.\u53ef\u56de\u6536\u4e14\u4f4e\u8102\u7684\u4ea7\u54c1 \u8868\uff1aProducts  +————-+———+& […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[13],"tags":[],"_links":{"self":[{"href":"https:\/\/www.muxuetianyin.cn\/index.php?rest_route=\/wp\/v2\/posts\/649"}],"collection":[{"href":"https:\/\/www.muxuetianyin.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.muxuetianyin.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.muxuetianyin.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.muxuetianyin.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=649"}],"version-history":[{"count":1,"href":"https:\/\/www.muxuetianyin.cn\/index.php?rest_route=\/wp\/v2\/posts\/649\/revisions"}],"predecessor-version":[{"id":650,"href":"https:\/\/www.muxuetianyin.cn\/index.php?rest_route=\/wp\/v2\/posts\/649\/revisions\/650"}],"wp:attachment":[{"href":"https:\/\/www.muxuetianyin.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=649"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.muxuetianyin.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=649"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.muxuetianyin.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=649"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}