61-80 - 《SQL》 - 极客文档

61. 查询结果的质量和占比
62. 查询球队积分
63. 报告系统状态的连续日期
64. 每个帖子的评论数
65. 平均售价
66. 页面推荐
67. 汇报工作
68. 学生们参加各科测试的次数
69. 找到连续区间的开始和结束数字
70. 不同国家的天气类型
71. 求团队人数
72. 不同性别每日分数总计
73. 餐馆营业额变化增长
74. 广告效果
75. 列出指定时间段内所有的下单产品
76. 每次访问的交易次数
77. 电影评分
78. 院系无效的学生
79. 活动参与者
80. 顾客的可信联系人数量

61. 查询结果的质量和占比

需求：编写一组 SQL 来查找每次查询的名称(query_name)、质量(quality) 和 劣质查询百分比(poor_query_percentage)。

质量(quality) 和劣质查询百分比(poor_query_percentage) 都应四舍五入到小数点后两位。

展示效果：

query_name	quality	poor_query_percentage
Dog	2.50	33.33
Cat	0.66	33.33

Create table If Not Exists 61_Queries (query_name varchar(30), result varchar(50), position int, rating int);
Truncate table 61_Queries;
insert into 61_Queries (query_name, result, position, rating) values ('Dog', 'Golden Retriever', 1, 5);
insert into 61_Queries (query_name, result, position, rating) values ('Dog', 'German Shepherd', 2, 5);
insert into 61_Queries (query_name, result, position, rating) values ('Dog', 'Mule', '200', 1);
insert into 61_Queries (query_name, result, position, rating) values ('Cat', 'Shirazi', 5, 2);
insert into 61_Queries (query_name, result, position, rating) values ('Cat', 'Siamese', 3, 3);
insert into 61_Queries (query_name, result, position, rating) values ('Cat', 'Sphynx', 7, 4);

最终SQL:

select
      query_name,
      round(avg(rating/position), 2) as quality ,
      round((count(if(rating<3, True, null)) / count(query_name)) *100 , 2) as poor_query_percentage
from
      61_Queries
group by 
      query_name
order by query_name desc;

62. 查询球队积分

需求一：写出一条SQL语句以查询每个队的 team_id，team_name 和 num_points。结果根据 num_points 降序排序，如果有两队积分相同，那么这两队按 team_id 升序排序。

展示效果：

team_id	team_name	num_points
10	Leetcode FC	7
20	NewYork FC	3
50	Toronto FC	3
30	Atlanta FC	1
40	Chicago FC	0

Create table If Not Exists 62_Teams (team_id int, team_name varchar(30));
Create table If Not Exists 62_Matches (match_id int, host_team int, guest_team int, host_goals int, guest_goals int);
Truncate table 62_Teams;
insert into 62_Teams (team_id, team_name) values (10, 'Leetcode FC');
insert into 62_Teams (team_id, team_name) values (20, 'NewYork FC');
insert into 62_Teams (team_id, team_name) values (30, 'Atlanta FC');
insert into 62_Teams (team_id, team_name) values (40, 'Chicago FC');
insert into 62_Teams (team_id, team_name) values (50, 'Toronto FC');
Truncate table 62_Matches;
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (1, 10, 20, 30, 0);
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (2, 30, 10, 2, 2);
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (3, 10, 50, 5, 1);
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (4, 20, 30, 1, 0);
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (5, 50, 30, 1, 0);

最终SQL:

SELECT 
     *
FROM
    (SELECT 
           a.team_id,
           MAX(team_name) AS team_name,
           SUM(
                CASE 
                    WHEN a.team_id = b.host_team THEN 
                    CASE 
                        WHEN b.host_goals > b.guest_goals THEN 3
                        WHEN b.host_goals = b.guest_goals THEN 1
                        ELSE 0
                    END
                    WHEN a.team_id = b.guest_team THEN 
                    CASE 
                        WHEN b.host_goals < b.guest_goals THEN 3
                        WHEN b.host_goals = b.guest_goals THEN 1
                        ELSE 0
                    END
                    ELSE 0
               END
           ) AS num_points
    FROM 
         62_Teams a
    LEFT JOIN
         62_Matches b
    ON 
         a.team_id = b.host_team OR 
         a.team_id = b.guest_team
    GROUP BY a.team_id
    ) a 
ORDER BY
    a.num_points DESC,
    a.team_id;

63. 报告系统状态的连续日期

需求：系统每天运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。编写一个 SQL 查询 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期（start_date 和 end_date）。即如果任务失败了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。最后结果按照起始日期 start_date 排序。

展示效果：

period_state	start date	end date
present	2019-01-01	2019-01-03
missing	2019-01-04	2019-01-05
present	2019-01-06	2019-01-06

Create table If Not Exists 63_Failed (fail_date date);
Create table If Not Exists 63_Succeeded (success_date date);
Truncate table 63_Failed;
insert into 63_Failed (fail_date) values ('2018-12-28');
insert into 63_Failed (fail_date) values ('2018-12-29');
insert into 63_Failed (fail_date) values ('2019-01-04');
insert into 63_Failed (fail_date) values ('2019-01-05');
Truncate table 63_Succeeded;
insert into 63_Succeeded (success_date) values ('2018-12-30');
insert into 63_Succeeded (success_date) values ('2018-12-31');
insert into 63_Succeeded (success_date) values ('2019-01-01');
insert into 63_Succeeded (success_date) values ('2019-01-02');
insert into 63_Succeeded (success_date) values ('2019-01-03');
insert into 63_Succeeded (success_date) values ('2019-01-06');

最终SQL:

-- 方法一
select 
      if(str=1,'succeeded','failed') as period_state ,
      min(date) as start_date,
      max(date) as end_date
from 
     (select 
            @diff := @diff+ if(num = 1 , 1,0) as diff,
            date,
            str
      from
           (select 
                  case 
                      when @str = str and  date_add(@pre,interval 1 day) = date  then @num := @num +1
                      when @str:=str then  @num := 1
                      else @num := 1
                  end as num,
                  @pre := date,
                  date,
                  str
            from 
                 (select 
                        fail_date as date ,
                        0 as 'str'
                  from 
                        63_Failed 
                  union  
                  select
                        success_date,
                        1
                  from 
                        63_Succeeded 
                 ) s,
                 (select @pre:=null,@num:=0,@str := null) s1
            where 
                  date between '2019-01-01' and '2019-12-31'
            order by
                  date 
           ) s,
           (select @diff:=0)  s1
     ) ys
group by diff, str;
-- 方法二
select 
      'fail' as period_state,
      min(f1.fail_date) start_date,
      min(f2.fail_date) end_date
from
     (select 
            fail_date 
      from 
            63_failed
      where
            fail_date between '2019-01-01' and '2019-12-31'
            and
            date_add(fail_date, interval -1 day) not in(select * from 63_failed )
     ) f1
 join 
     (select 
            fail_date 
      from 
            63_failed
      where 
            fail_date between '2019-01-01' and '2019-12-31'
            and
            date_add(fail_date, interval 1 day) not in(select * from 63_failed )
      ) f2
on 
      f1.fail_date <= f2.fail_date
group by 
      f1.fail_date
union
select
      'success' as period_state,
      min(s1.success_date) start_date,
      min(s2.success_date) end_date
from
     (select 
            success_date 
      from
            63_succeeded
      where 
            success_date between '2019-01-01' and '2019-12-31'
            and date_add(success_date, interval -1 day) not in(select * from 63_succeeded where success_date between '2019-01-01' and '2019-12-31')) s1
join
     (select
            success_date
      from 
            63_succeeded 
      where
            success_date between '2019-01-01' and '2019-12-31' 
            and
            date_add(success_date, interval 1 day) not in(select * from 63_succeeded where success_date between '2019-01-01' and '2019-12-31')) s2
on 
      s1.success_date <= s2.success_date
group by 
      s1.success_date
order by 
      start_date;
-- 方法三
select
     type as period_state,
     min(date) as start_date,
     max(date) as end_date
from
    (select 
           type, 
           `date`,
           subdate(`date`,row_number()over(partition by type order by `date`)) as diff
     from
          (select 'failed' as type, fail_date as `date` from 63_Failed
           union all
           select 'succeeded' as type, success_date as `date` from 63_Succeeded
          ) a1
      )a2
where 
    `date` between '2019-01-01' and '2019-12-31'
group by
     type,diff
order by
     start_date;

64. 每个帖子的评论数

需求一：编写 SQL 语句以查找每个帖子的评论数。结果表应包含帖子的 post_id 和对应的评论数 number_of_comments 并且按 post_id 升序排列。Submissions 可能包含重复的评论。您应该计算每个帖子的唯一评论数。Submissions 可能包含重复的帖子。您应该将它们视为一个帖子。

展示效果：

post_id	number_of_comments
1	3
2	2
12	0

Create table If Not Exists 64_Submissions (sub_id int, parent_id int);
Truncate table 64_Submissions;
insert into 64_Submissions (sub_id, parent_id) values (1, null);
insert into 64_Submissions (sub_id, parent_id) values (2, null);
insert into 64_Submissions (sub_id, parent_id) values (1, null);
insert into 64_Submissions (sub_id, parent_id) values (12, null);
insert into 64_Submissions (sub_id, parent_id) values (3, 1);
insert into 64_Submissions (sub_id, parent_id) values (5, 2);
insert into 64_Submissions (sub_id, parent_id) values (3, 1);
insert into 64_Submissions (sub_id, parent_id) values (4, 1);
insert into 64_Submissions (sub_id, parent_id) values (9, 1);
insert into 64_Submissions (sub_id, parent_id) values (10, 2);
insert into 64_Submissions (sub_id, parent_id) values (6, 7);

最终SQL:

SELECT
      post_id,
      COUNT( DISTINCT S2.sub_id ) AS number_of_comments 
FROM
    (SELECT
           DISTINCT sub_id AS post_id 
     FROM 
           64_Submissions
     WHERE 
           parent_id IS NULL
    ) S1
LEFT JOIN
     64_Submissions S2
ON
     S1.post_id = S2.parent_id 
GROUP BY
     S1.post_id;

65. 平均售价

需求一：编写SQL查询以查找每种产品的平均售价。
average_price 应该四舍五入到小数点后两位。

展示效果：

product_id	average_price
1	6.96
2	16.96

Create table If Not Exists 65_Prices (product_id int, start_date date, end_date date, price int);
Create table If Not Exists 65_UnitsSold (product_id int, purchase_date date, units int);
Truncate table 65_Prices;
insert into 65_Prices (product_id, start_date, end_date, price) values (1, '2019-02-17', '2019-02-28', 5);
insert into 65_Prices (product_id, start_date, end_date, price) values (1, '2019-03-01', '2019-03-22', 20);
insert into 65_Prices (product_id, start_date, end_date, price) values (2, '2019-02-01', '2019-02-20', 15);
insert into 65_Prices (product_id, start_date, end_date, price) values (2, '2019-02-21', '2019-03-31', 30);
Truncate table 65_UnitsSold;
insert into 65_UnitsSold (product_id, purchase_date, units) values (1, '2019-02-25', 100);
insert into 65_UnitsSold (product_id, purchase_date, units) values (1, '2019-03-01', 15);
insert into 65_UnitsSold (product_id, purchase_date, units) values (2, '2019-02-10', 200);
insert into 65_UnitsSold (product_id, purchase_date, units) values (2, '2019-03-22', 30);

最终SQL:

select
      product_id,
      round(sum(a)/sum(units),2) as average_price
from
   (select 
          p.product_id as product_id,
          price,units,
          price * units as a
    from 
          65_Prices p 
    left join
          65_UnitsSold u
    on 
          p.product_id=u.product_id and 
          purchase_date<=end_date and 
          purchase_date>=start_date
   )t
group by 
    product_id;

66. 页面推荐

需求一：写一段 SQL 向user_id = 1 的用户，推荐其朋友们喜欢的页面。不要推荐该用户已经喜欢的页面。

展示效果：

recommended_page
23
24
56
33
77

Create table If Not Exists 66_Friendship (user1_id int, user2_id int);
Create table If Not Exists 66_Likes (user_id int, page_id int);
Truncate table 66_Friendship;
insert into 66_Friendship (user1_id, user2_id) values (1, 2);
insert into 66_Friendship (user1_id, user2_id) values (1, 3);
insert into 66_Friendship (user1_id, user2_id) values (1, 4);
insert into 66_Friendship (user1_id, user2_id) values (2, 3);
insert into 66_Friendship (user1_id, user2_id) values (2, 4);
insert into 66_Friendship (user1_id, user2_id) values (2, 5);
insert into 66_Friendship (user1_id, user2_id) values (6, 1);
Truncate table 66_Likes;
insert into 66_Likes (user_id, page_id) values (1, 88);
insert into 66_Likes (user_id, page_id) values (2, 23);
insert into 66_Likes (user_id, page_id) values (3, 24);
insert into 66_Likes (user_id, page_id) values (4, 56);
insert into 66_Likes (user_id, page_id) values (5, 11);
insert into 66_Likes (user_id, page_id) values (6, 33);
insert into 66_Likes (user_id, page_id) values (2, 77);
insert into 66_Likes (user_id, page_id) values (3, 77);
insert into 66_Likes (user_id, page_id) values (6, 88);

解释：

用户1同用户2, 3, 4, 6 是朋友关系。

推荐页面为：页面23 来自于用户2, 页面24 来自于用户3, 页面56 来自于用户3 以及页面33 来自于用户6。
页面77 同时被用户2 和用户3 推荐。
页面88 没有被推荐，因为用户1 已经喜欢了它。

最终SQL:

select 
      distinct page_id as recommended_page
from 
      66_Likes,
      66_friendship
where 
      page_id not in(select page_id from 66_likes where user_id=1)
      and
      user_id in (select user1_id from 66_friendship where user2_id=1) 
      or
      user_id in (select user2_id from 66_friendship where user1_id=1);

67. 汇报工作

需求：用 SQL 查询出所有直接或间接向公司 CEO 汇报工作的职工的 employee_id 。由于公司规模较小，经理之间的间接关系不超过 3 个经理。可以以任何顺序返回的结果，不需要去重。

展示效果：

employee_id
2
4
7
77

Create table If Not Exists 67_Employees (employee_id int, employee_name varchar(30), manager_id int);
Truncate table 67_Employees;
insert into 67_Employees (employee_id, employee_name, manager_id) values (1, 'Boss', 1);
insert into 67_Employees (employee_id, employee_name, manager_id) values (3, 'Alice', 3);
insert into 67_Employees (employee_id, employee_name, manager_id) values (2, 'Bob', 1);
insert into 67_Employees (employee_id, employee_name, manager_id) values (4, 'Daniel', 2);
insert into 67_Employees (employee_id, employee_name, manager_id) values (7, 'Luis', 4);
insert into 67_Employees (employee_id, employee_name, manager_id) values (8, 'John', 3);
insert into 67_Employees (employee_id, employee_name, manager_id) values (9, 'Angela', 8);
insert into 67_Employees (employee_id, employee_name, manager_id) values (77, 'Robert', 1);

提示：

公司 CEO 的 employee_id 是 1.

employee_id 是 2 和 77 的职员直接汇报给公司 CEO。

employee_id 是 4 的职员间接汇报给公司 CEO 4 —> 2 —> 1 。

employee_id 是 7 的职员间接汇报给公司 CEO 7 —> 4 —> 2 —> 1 。

employee_id 是 3, 8 ，9 的职员不会直接或间接的汇报给公司 CEO。

最终SQL:

select
      employee_id EMPLOYEE_ID
from 
      67_Employees
where 
      manager_id=1 and 
      employee_id!=1
union
select
      a1.employee_id
from 
      67_Employees a1,
     (select 
            employee_id
      from
            67_Employees
      where
            manager_id=1 and
            employee_id!=1
     ) a
where
     manager_id=a.employee_id
union
select 
     a2.employee_id
from 
     67_Employees a2,
    (select
           a1.employee_id employee_id
    from 
           67_Employees a1,
           (select 
                  employee_id
            from
                  67_Employees
            where
                  manager_id=1 and
                  employee_id!=1
           ) a
    where 
           manager_id=a.employee_id
    ) a3
where 
    manager_id=a3.employee_id
order by 
    employee_id;

68. 学生们参加各科测试的次数

需求：写一段 SQL 语句，查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。

展示效果：

+------------+--------------+--------------+----------------+
| 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              |
+------------+--------------+--------------+----------------+

建表语句：

Create table If Not Exists 68_Students (student_id int, student_name varchar(20));
Create table If Not Exists 68_Subjects (subject_name varchar(20));
Create table If Not Exists 68_Examinations (student_id int, subject_name varchar(20));
Truncate table 68_Students;
insert into 68_Students (student_id, student_name) values ('1', 'Alice');
insert into 68_Students (student_id, student_name) values ('2', 'Bob');
insert into 68_Students (student_id, student_name) values ('13', 'John');
insert into 68_Students (student_id, student_name) values ('6', 'Alex');
Truncate table 68_Subjects;
insert into 68_Subjects (subject_name) values ('Math');
insert into 68_Subjects (subject_name) values ('Physics');
insert into 68_Subjects (subject_name) values ('Programming');
Truncate table 68_Examinations;
insert into 68_Examinations (student_id, subject_name) values ('1', 'Math');
insert into 68_Examinations (student_id, subject_name) values ('1', 'Physics');
insert into 68_Examinations (student_id, subject_name) values ('1', 'Programming');
insert into 68_Examinations (student_id, subject_name) values ('2', 'Programming');
insert into 68_Examinations (student_id, subject_name) values ('1', 'Physics');
insert into 68_Examinations (student_id, subject_name) values ('1', 'Math');
insert into 68_Examinations (student_id, subject_name) values ('13', 'Math');
insert into 68_Examinations (student_id, subject_name) values ('13', 'Programming');
insert into 68_Examinations (student_id, subject_name) values ('13', 'Physics');
insert into 68_Examinations (student_id, subject_name) values ('2', 'Math');
insert into 68_Examinations (student_id, subject_name) values ('1', 'Math');

最终sql：

SELECT 
     a.student_id,
     a.student_name,
     b.subject_name,
     COUNT(e.subject_name) AS attended_exams
FROM 
     68_Students a 
CROSS JOIN
     68_Subjects b
LEFT JOIN
     68_Examinations e 
ON 
     a.student_id = e.student_id 
     AND
     b.subject_name = e.subject_name
GROUP BY 
     a.student_id, b.subject_name
ORDER BY
     a.student_id, b.subject_name;

69. 找到连续区间的开始和结束数字

需求：编写一个 SQL 查询得到 Logs 表中的连续区间的开始数字和结束数字。

展示效果：

+------------+--------------+
| start_id   | end_id       |
+------------+--------------+
| 1          | 3            |
| 7          | 8            |
| 10         | 10           |
+------------+--------------+

建表语句：

Create table If Not Exists 69_Logs (log_id int);
Truncate table 69_Logs;
insert into 69_Logs (log_id) values ('1');
insert into 69_Logs (log_id) values ('2');
insert into 69_Logs (log_id) values ('3');
insert into 69_Logs (log_id) values ('7');
insert into 69_Logs (log_id) values ('8');
insert into 69_Logs (log_id) values ('10');

最终sql：

-- 方法一
SELECT
    min(log_id) start_id,
    max(log_id) end_id
FROM
    (SELECT
        log_id,
        CASE WHEN @id = log_id - 1 THEN @num := @num
        ELSE @num := @num + 1
        END num, @id := log_id
     FROM 
         69_Logs,
         (SELECT @num := 0, @id := NULL) a
    ) x
GROUP BY num;
-- 方法二
select
     a.log_id as start_id,
     min(b.log_id) as end_id
from 
    (select log_id from 69_logs where log_id-1 not in (select * from 69_logs)) a,
    (select log_id from 69_logs where log_id+1 not in (select * from 69_logs)) b
where 
     b.log_id>=a.log_id
group by
     a.log_id;
-- 方法三
SELECT
    MIN(log_id) start_id,
    MAX(log_id) end_id
FROM
    (SELECT
        log_id, 
        log_id - row_number() OVER(ORDER BY log_id) as diff
     FROM 
        69_Logs ) t
GROUP BY diff;

70. 不同国家的天气类型

需求：写一段 SQL 来找到表中每个国家在 2019 年 11 月的天气类型。

天气类型的定义如下：当 weather_state 的平均值小于或等于15返回 Cold，当 weather_state 的平均值大于或等于 25 返回 Hot，否则返回 Warm。

展示效果：

+--------------+--------------+
| country_name | weather_type |
+--------------+--------------+
| USA          | Cold         |
| Austraila    | Cold         |
| Peru         | Hot          |
| China        | Warm         |
| Morocco      | Hot          |
+--------------+--------------+

建表语句：

Create table If Not Exists 70_Countries (country_id int, country_name varchar(20));
Create table If Not Exists 70_Weather (country_id int, weather_state int, day date);
Truncate table 70_Countries;
insert into 70_Countries (country_id, country_name) values ('2', 'USA');
insert into 70_Countries (country_id, country_name) values ('3', 'Australia');
insert into 70_Countries (country_id, country_name) values ('7', 'Peru');
insert into 70_Countries (country_id, country_name) values ('5', 'China');
insert into 70_Countries (country_id, country_name) values ('8', 'Morocco');
insert into 70_Countries (country_id, country_name) values ('9', 'Spain');
Truncate table 70_Weather;
insert into 70_Weather (country_id, weather_state, day) values ('2', '15', '2019-11-01');
insert into 70_Weather (country_id, weather_state, day) values ('2', '12', '2019-10-28');
insert into 70_Weather (country_id, weather_state, day) values ('2', '12', '2019-10-27');
insert into 70_Weather (country_id, weather_state, day) values ('3', '-2', '2019-11-10');
insert into 70_Weather (country_id, weather_state, day) values ('3', '0', '2019-11-11');
insert into 70_Weather (country_id, weather_state, day) values ('3', '3', '2019-11-12');
insert into 70_Weather (country_id, weather_state, day) values ('5', '16', '2019-11-07');
insert into 70_Weather (country_id, weather_state, day) values ('5', '18', '2019-11-09');
insert into 70_Weather (country_id, weather_state, day) values ('5', '21', '2019-11-23');
insert into 70_Weather (country_id, weather_state, day) values ('7', '25', '2019-11-28');
insert into 70_Weather (country_id, weather_state, day) values ('7', '22', '2019-12-01');
insert into 70_Weather (country_id, weather_state, day) values ('8', '25', '2019-11-05');
insert into 70_Weather (country_id, weather_state, day) values ('8', '27', '2019-11-15');
insert into 70_Weather (country_id, weather_state, day) values ('8', '31', '2019-11-25');
insert into 70_Weather (country_id, weather_state, day) values ('9', '7', '2019-10-23');
insert into 70_Weather (country_id, weather_state, day) values ('9', '3', '2019-12-23');

最终sql：

select 
    country_name,
    (case 
         when avg(weather_state)<=15 then 'Cold'
         when avg(weather_state)>=25 then 'Hot'
         else 'Warm'
     end ) weather_type
from 
    70_Countries c 
left join 
    70_Weather w
on
    c.country_id = w.country_id
where
    date_format(day,"%Y-%m")='2019-11'
group by country_name;

71. 求团队人数

编写一个 SQL 查询，以求得每个员工所在团队的总人数。查询结果中的顺序无特定要求。

展示效果：

+-------------+------------+
| employee_id | team_size  |
+-------------+------------+
|     1       |     3      |
|     2       |     3      |
|     3       |     3      |
|     4       |     1      |
|     5       |     2      |
|     6       |     2      |
+-------------+------------+

建表语句：

Create table If Not Exists 71_Employee (employee_id int, team_id int);
Truncate table 71_Employee;
insert into 71_Employee (employee_id, team_id) values ('1', '8');
insert into 71_Employee (employee_id, team_id) values ('2', '8');
insert into 71_Employee (employee_id, team_id) values ('3', '8');
insert into 71_Employee (employee_id, team_id) values ('4', '7');
insert into 71_Employee (employee_id, team_id) values ('5', '9');
insert into 71_Employee (employee_id, team_id) values ('6', '9');

最终sql：

-- 方法一
SELECT 
     employee_id,
    (SELECT COUNT(*) FROM 71_employee e2 WHERE e1.team_id = e2.team_id) AS team_size
FROM 
     71_Employee e1
ORDER BY
     e1.employee_id;

-- 方法二
SELECT
     e1.employee_id, 
     COUNT(*) AS team_size
FROM
     71_Employee e1
JOIN
     71_Employee e2 
USING(team_id)
GROUP BY
     e1.employee_id
ORDER BY 
     e1.employee_id;

-- 方法三
SELECT
    employee_id,
    COUNT(employee_id) OVER(PARTITION BY team_id) AS team_size
FROM
    71_Employee
ORDER BY
    employee_id;

72. 不同性别每日分数总计

写一条SQL语句查询每种性别在每一天的总分，并按性别和日期对查询结果排序

展示效果：

+--------+------------+-------+
| gender | day        | total |
+--------+------------+-------+
| F      | 2019-12-30 | 17    |
| F      | 2019-12-31 | 40    |
| F      | 2020-01-01 | 57    |
| F      | 2020-01-07 | 80    |
| M      | 2019-12-18 | 2     |
| M      | 2019-12-25 | 13    |
| M      | 2019-12-30 | 26    |
| M      | 2019-12-31 | 29    |
| M      | 2020-01-07 | 36    |
+--------+------------+-------+

建表语句：

Create table If Not Exists 72_Scores (player_name varchar(20), gender varchar(1), day date, score_points int);
Truncate table 72_Scores;
insert into 72_Scores (player_name, gender, day, score_points) values ('Aron', 'F', '2020-01-01', '17');
insert into 72_Scores (player_name, gender, day, score_points) values ('Alice', 'F', '2020-01-07', '23');
insert into 72_Scores (player_name, gender, day, score_points) values ('Bajrang', 'M', '2020-01-07', '7');
insert into 72_Scores (player_name, gender, day, score_points) values ('Khali', 'M', '2019-12-25', '11');
insert into 72_Scores (player_name, gender, day, score_points) values ('Slaman', 'M', '2019-12-30', '13');
insert into 72_Scores (player_name, gender, day, score_points) values ('Joe', 'M', '2019-12-31', '3');
insert into 72_Scores (player_name, gender, day, score_points) values ('Jose', 'M', '2019-12-18', '2');
insert into 72_Scores (player_name, gender, day, score_points) values ('Priya', 'F', '2019-12-31', '23');
insert into 72_Scores (player_name, gender, day, score_points) values ('Priyanka', 'F', '2019-12-30', '17');

最终SQL：

-- 方法一
SELECT 
     s1.gender,
     s1.day,
     SUM(s2.score_points) AS total
FROM 
     Scores AS s1 
JOIN 
     Scores AS s2
ON 
     s1.gender = s2.gender 
     AND
     s1.day >= s2.day
GROUP BY 
     s1.gender, s1.day
ORDER BY 
     s1.gender, s1.day;

-- 方法二
SELECT 
     gender,
     day,
     SUM(score_points) OVER (PARTITION BY gender ORDER BY day) AS total
FROM Scores;

73. 餐馆营业额变化增长

写一条 SQL 查询计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值

查询结果格式的例子如下：

查询结果按 visited_on 排序
average_amount 要 保留两位小数，日期数据的格式为 (‘YYYY-MM-DD’)

展示效果：

+---------------+--------+----------------+
| visited_on    | amount | average_amount |
+---------------+--------+----------------+
| 2019-01-07    |    860 |         122.86 |
| 2019-01-08    |    840 |         120.00 |
| 2019-01-09    |    840 |         120.00 |
| 2019-01-10    |   1000 |         142.86 |
+---------------+--------+----------------+

Create table If Not Exists 73_Customer (customer_id int, name varchar(20), visited_on date, amount int);
Truncate table 73_Customer;
insert into 73_Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-01', '100');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('2', 'Daniel', '2019-01-02', '110');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', '2019-01-03', '120');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('4', 'Khaled', '2019-01-04', '130');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('5', 'Winston', '2019-01-05', '110');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('6', 'Elvis', '2019-01-06', '140');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('7', 'Anna', '2019-01-07', '150');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('8', 'Maria', '2019-01-08', '80');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('9', 'Jaze', '2019-01-09', '110');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-10', '130');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', '2019-01-10', '150');

最终sql：

-- 方法一
select
    s.visited_on,
    sum(c.amount) as "amount",
    round(sum(c.amount) / 7, 2) as "average_amount"
from 
    73_Customer c 
inner join 
   (select
         distinct visited_on
    from
         73_Customer c 
    where 
         visited_on >= (select
                              distinct visited_on
                        from
                              73_customer 
                        order by
                              visited_on asc
                        limit 1 offset 6)
   ) s 
on 
   datediff(s.visited_on, c.visited_on) >= 0 
   and
   datediff(s.visited_on, c.visited_on) < 7
group by
   s.visited_on;

-- 方法二
select 
     visited_on,
     amount,
     round(amount/7,2) average_amount
from
    (select
          visited_on,
          ant,
          lag(visited_on,6,null) over(order by visited_on) lg,
          sum(ant) over(order by visited_on rows between 6 PRECEDING and current row) amount
     from
         (select 
                visited_on,
                sum(amount) ant
          from 
                73_Customer
          group by 
                visited_on)t1
          )t2
where lg is not null

扩展：从起始日期查询七天内，每天的平均收入，不满足七天的按实际天数求平均值。

select
     c2_visited_on as visited_on,
     sum(amount) amount,
     round(sum(amount)/count(distinct c1_visited_on),2) as average_amount
from
    (select
          c1.amount,
          c1.visited_on as c1_visited_on,
          c2.visited_on as c2_visited_on
     from
          73_Customer c1
     join
         (select distinct visited_on from 73_Customer) c2
     on
          datediff(c2.visited_on, c1.visited_on) <= 6
          and
          datediff(c2.visited_on, c1.visited_on) >= 0
     ) tmp
group by
     tmp.c2_visited_on;

74. 广告效果

写一条SQL语句来查询每一条广告的 ctr ， ctr 要保留两位小数。结果需要按 ctr 降序、按 ad_id 升序进行排序。

广告效果用点击通过率（Click-Through Rate：CTR）来衡量，公式如下:

61-80 - 图1

展示效果：

+-------+-------+
| ad_id | ctr   |
+-------+-------+
| 1     | 66.67 |
| 3     | 50.00 |
| 2     | 33.33 |
| 5     | 0.00  |
+-------+-------+

Create table If Not Exists 74_Ads (ad_id int, user_id int, action ENUM('Clicked', 'Viewed', 'Ignored'));
Truncate table 74_Ads;
insert into 74_Ads (ad_id, user_id, action) values ('1', '1', 'Clicked');
insert into 74_Ads (ad_id, user_id, action) values ('2', '2', 'Clicked');
insert into 74_Ads (ad_id, user_id, action) values ('3', '3', 'Viewed');
insert into 74_Ads (ad_id, user_id, action) values ('5', '5', 'Ignored');
insert into 74_Ads (ad_id, user_id, action) values ('1', '7', 'Ignored');
insert into 74_Ads (ad_id, user_id, action) values ('2', '7', 'Viewed');
insert into 74_Ads (ad_id, user_id, action) values ('3', '5', 'Clicked');
insert into 74_Ads (ad_id, user_id, action) values ('1', '4', 'Viewed');
insert into 74_Ads (ad_id, user_id, action) values ('2', '11', 'Viewed');
insert into 74_Ads (ad_id, user_id, action) values ('1', '2', 'Clicked');

最终SQL：

-- 方法一
SELECT
     ad_id,
     ROUND(IFNULL(SUM(action = 'Clicked')
                    /
             (SUM(action = 'Clicked') + SUM(action = 'Viewed')) * 100, 0), 2) AS ctr
FROM 
    74_Ads
GROUP BY
    ad_id
ORDER BY
    ctr DESC,
    ad_id ASC;

-- 方法二
select 
     ad_id,
     ifnull(round((Clicked/(Viewed + Clicked))*100,2),0) as ctr
from
    (select
           ad_id,
           count(case when action = 'Clicked' then 1 end) as Clicked,
           count(case when action = 'Viewed' then 1 end) as Viewed
     from 74_Ads 
     group by ad_id ) a 
order by 
     ctr desc,
     ad_id asc;

-- 方法三
select 
     a.ad_id,
     ifnull(ctr,0) ctr
from 
     74_Ads a
left join 
     (select
           ad_id,
           round(sum(if(action='Clicked',1,0))/count(*)*100,2) ctr 
      from 74_Ads 
      where action !='Ignored'
      group by ad_id )t1 
on 
      a.ad_id= t1.ad_id
group by 
      ad_id,ctr
order by
      ctr desc,ad_id;

75. 列出指定时间段内所有的下单产品

写一个 SQL 语句，要求获取在 2020 年 2 月份下单的数量不少于 100 的产品的名字和数目。返回结果表单的顺序无要求。

展示效果：

+--------------------+---------+
| product_name       | unit    |
+--------------------+---------+
| Leetcode Solutions | 130     |
| Leetcode Kit       | 100     |
+--------------------+---------+

Create table If Not Exists 75_Products (product_id int, product_name varchar(40), product_category varchar(40));
Create table If Not Exists 75_Orders (product_id int, order_date date, unit int);
Truncate table 75_Products;
insert into 75_Products (product_id, product_name, product_category) values ('1', 'Leetcode Solutions', 'Book');
insert into 75_Products (product_id, product_name, product_category) values ('2', 'Jewels of Stringology', 'Book');
insert into 75_Products (product_id, product_name, product_category) values ('3', 'HP', 'Laptop');
insert into 75_Products (product_id, product_name, product_category) values ('4', 'Lenovo', 'Laptop');
insert into 75_Products (product_id, product_name, product_category) values ('5', 'Leetcode Kit', 'T-shirt');
Truncate table 75_Orders;
insert into 75_Orders (product_id, order_date, unit) values ('1', '2020-02-05', '60');
insert into 75_Orders (product_id, order_date, unit) values ('1', '2020-02-10', '70');
insert into 75_Orders (product_id, order_date, unit) values ('2', '2020-01-18', '30');
insert into 75_Orders (product_id, order_date, unit) values ('2', '2020-02-11', '80');
insert into 75_Orders (product_id, order_date, unit) values ('3', '2020-02-17', '2');
insert into 75_Orders (product_id, order_date, unit) values ('3', '2020-02-24', '3');
insert into 75_Orders (product_id, order_date, unit) values ('4', '2020-03-01', '20');
insert into 75_Orders (product_id, order_date, unit) values ('4', '2020-03-04', '30');
insert into 75_Orders (product_id, order_date, unit) values ('4', '2020-03-04', '60');
insert into 75_Orders (product_id, order_date, unit) values ('5', '2020-02-25', '50');
insert into 75_Orders (product_id, order_date, unit) values ('5', '2020-02-27', '50');
insert into 75_Orders (product_id, order_date, unit) values ('5', '2020-03-01', '50');

-- 方法一
select 
    p.product_name,
    sum(o.unit) as unit
from
    75_Products p
left join
    75_Orders o 
on
    p.product_id=o.product_id
where
    order_date between '2020-02-01' and '2020-02-29'
group by
    p.product_name
having
    sum(o.unit)>=100
order by
    sum(o.unit);

-- 方法二 
SELECT 
     product_name, 
     SUM(unit) AS unit
FROM 
     75_Products 
JOIN 
     75_Orders USING (product_id)
WHERE 
     order_date LIKE "2020-02%"
GROUP BY
     product_name
HAVING
     unit >= 100;

-- 方法三
select 
     T.product_name,
     T.unit
from
    (select
          p.product_name,
          sum(unit) as unit
     from
          75_Orders o
     join
          75_Products p
     on 
          o.product_id = p.product_id
     where
         order_date like "2020-02%"
    group by
         p.product_id
    ) as T
where
    T.unit>= 100;

76. 每次访问的交易次数

写一条 SQL 查询多少客户访问了银行但没有进行任何交易，多少客户访问了银行进行了一次交易等等

结果包含两列：

transactions_count： 客户在一次访问中的交易次数
visits_count： 在 transactions_count 交易次数下相应的一次访问时的客户数量

展示效果：

+--------------------+--------------+
| transactions_count | visits_count |
+--------------------+--------------+
| 0                  | 4            |
| 1                  | 5            |
| 2                  | 0            |
| 3                  | 1            |
+--------------------+--------------+

提示：

对于 transactions_count = 0, visits 中 (1, “2020-01-01”), (2, “2020-01-02”), (12, “2020-01-01”) 和 (19, “2020-01-03”) 没有进行交易，所以 visits_count = 4 。
对于 transactions_count = 1, visits 中 (2, “2020-01-03”), (7, “2020-01-11”), (8, “2020-01-28”), (1, “2020-01-02”) 和 (1, “2020-01-04”) 进行了一次交易，所以 visits_count = 5 。
对于 transactions_count = 2, 没有客户访问银行进行了两次交易，所以 visits_count = 0 。
对于 transactions_count = 3, visits 中 (9, “2020-01-25”) 进行了三次交易，所以 visits_count = 1 。
对于 transactions_count >= 4, 没有客户访问银行进行了超过3次交易，所以我们停止在 transactions_count = 3 。

Create table If Not Exists 76_Visits (user_id int, visit_date date);
Create table If Not Exists 76_Transactions (user_id int, transaction_date date, amount int);
Truncate table 76_Visits;
insert into 76_Visits (user_id, visit_date) values ('1', '2020-01-01');
insert into 76_Visits (user_id, visit_date) values ('2', '2020-01-02');
insert into 76_Visits (user_id, visit_date) values ('12', '2020-01-01');
insert into 76_Visits (user_id, visit_date) values ('19', '2020-01-03');
insert into 76_Visits (user_id, visit_date) values ('1', '2020-01-02');
insert into 76_Visits (user_id, visit_date) values ('2', '2020-01-03');
insert into 76_Visits (user_id, visit_date) values ('1', '2020-01-04');
insert into 76_Visits (user_id, visit_date) values ('7', '2020-01-11');
insert into 76_Visits (user_id, visit_date) values ('9', '2020-01-25');
insert into 76_Visits (user_id, visit_date) values ('8', '2020-01-28');
Truncate table 76_Transactions;
insert into 76_Transactions (user_id, transaction_date, amount) values ('1', '2020-01-02', '120');
insert into 76_Transactions (user_id, transaction_date, amount) values ('2', '2020-01-03', '22');
insert into 76_Transactions (user_id, transaction_date, amount) values ('7', '2020-01-11', '232');
insert into 76_Transactions (user_id, transaction_date, amount) values ('1', '2020-01-04', '7');
insert into 76_Transactions (user_id, transaction_date, amount) values ('9', '2020-01-25', '33');
insert into 76_Transactions (user_id, transaction_date, amount) values ('9', '2020-01-25', '66');
insert into 76_Transactions (user_id, transaction_date, amount) values ('8', '2020-01-28', '1');
insert into 76_Transactions (user_id, transaction_date, amount) values ('9', '2020-01-25', '99');

最终SQL：

-- 方法一
select
     tmp.user_visits_num transactions_count,
     count(1) visits_count
from
    (select 
          sum(if(amount is null, 0, 1)) user_visits_num
     from 
          76_Visits v1
     left join 
          76_Transactions t1
     on 
          v1.user_id = t1.user_id 
          and
          v1.visit_date = t1.transaction_date
     group by 
          v1.user_id, v1.visit_date
    ) tmp
group by tmp.user_visits_num;

-- 方法二
SELECT
     *
FROM
     (SELECT
            t5.rnb AS transactions_count,
            IFNULL(visits_count, 0) AS visits_count
      FROM
           (SELECT
                 0 AS rnb
            UNION
            SELECT
                 ROW_NUMBER() OVER () AS rnb
            FROM
                 76_Transactions
            ) t5
      LEFT JOIN
           (SELECT
                 cnt AS transactions_count,
                 COUNT(user_id) AS visits_count
            FROM
                (SELECT
                      t1.user_id, 
                      COUNT(t2.amount) AS cnt
                 FROM
                      76_Visits t1
                 LEFT JOIN
                      76_Transactions t2
                 ON 
                      t1.user_id = t2.user_id
                      AND
                      t1.visit_date = t2.transaction_date
                 GROUP BY
                      user_id, 
                      visit_date ) t3
             GROUP BY cnt ) t4
       ON t5.rnb = t4.transactions_count) t6
WHERE transactions_count <= (SELECT
                                   COUNT(t2.amount) AS cnt
                              FROM 
                                   76_Visits t1
                              LEFT JOIN
                                   76_Transactions t2
                              ON
                                   t1.user_id = t2.user_id
                                   AND
                                   t1.visit_date = t2.transaction_date
                              GROUP BY
                                   t1.user_id, visit_date
                              ORDER BY
                                   cnt DESC
                              LIMIT 1);

77. 电影评分

请你编写一组 SQL 查询：查找评论电影数量最多的用户名，如果出现平局，返回字典序较小的用户名。查找在2020 年 2 月平均评分最高的电影名称，如果出现平局，返回字典序较小的电影名称。

展示效果：

+--------------+
| results      |
+--------------+
| Daniel       |
| Frozen 2     |
+--------------+

建表语句：

Create table If Not Exists 77_Movies (movie_id int, title varchar(30));
Create table If Not Exists 77_Users (user_id int, name varchar(30));
Create table If Not Exists 77_Movie_Rating (movie_id int, user_id int, rating int, created_at date);
Truncate table 77_Movies;
insert into 77_Movies (movie_id, title) values ('1', 'Avengers');
insert into 77_Movies (movie_id, title) values ('2', 'Frozen 2');
insert into 77_Movies (movie_id, title) values ('3', 'Joker');
Truncate table 77_Users;
insert into 77_Users (user_id, name) values ('1', 'Daniel');
insert into 77_Users (user_id, name) values ('2', 'Monica');
insert into 77_Users (user_id, name) values ('3', 'Maria');
insert into 77_Users (user_id, name) values ('4', 'James');
Truncate table 77_Movie_Rating;
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('1', '1', '3', '2020-01-12');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('1', '2', '4', '2020-02-11');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('1', '3', '2', '2020-02-12');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('1', '4', '1', '2020-01-01');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('2', '1', '5', '2020-02-17');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('2', '2', '2', '2020-02-01');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('2', '3', '2', '2020-03-01');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('3', '1', '3', '2020-02-22');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('3', '2', '4', '2020-02-25');

最终SQL：

select 
     name results 
from
    (select
          m.user_id,
          u.name 
    from
          77_Movie_Rating m 
    left join
          77_Users u 
    on
          m.user_id = u.user_id
    group by
          user_id
    order by
          count(*) desc,name
    limit 1)t1

union 
    (select
          title results
     from 
          77_Movie_Rating r 
     left join
          77_Movies m
     on
          r.movie_id =m.movie_id 
     where
         date_format(created_at,'%Y-%m')='2020-02'
     group by 
         r.movie_id
     order by 
         avg(rating) desc,title 
     limit 1);

78. 院系无效的学生

写一条 SQL 语句以查询那些所在院系不存在的学生的 id 和姓名，可以以任何顺序返回结果

展示效果：

+------+----------+
| id   | name     |
+------+----------+
| 2    | John     |
| 7    | Daiana   |
| 4    | Jasmine  |
| 3    | Steve    |
+------+----------+

建表语句：

Create table If Not Exists 78_Departments (id int, name varchar(30));
Create table If Not Exists 78_Students (id int, name varchar(30), department_id int);
Truncate table 78_Departments;
insert into 78_Departments (id, name) values ('1', 'Electrical Engineering');
insert into 78_Departments (id, name) values ('7', 'Computer Engineering');
insert into 78_Departments (id, name) values ('13', 'Bussiness Administration');
Truncate table 78_Students;
insert into 78_Students (id, name, department_id) values ('23', 'Alice', '1');
insert into 78_Students (id, name, department_id) values ('1', 'Bob', '7');
insert into 78_Students (id, name, department_id) values ('5', 'Jennifer', '13');
insert into 78_Students (id, name, department_id) values ('2', 'John', '14');
insert into 78_Students (id, name, department_id) values ('4', 'Jasmine', '77');
insert into 78_Students (id, name, department_id) values ('3', 'Steve', '74');
insert into 78_Students (id, name, department_id) values ('6', 'Luis', '1');
insert into 78_Students (id, name, department_id) values ('8', 'Jonathan', '7');
insert into 78_Students (id, name, department_id) values ('7', 'Daiana', '33');
insert into 78_Students (id, name, department_id) values ('11', 'Madelynn', '1');

最终SQL：

-- 方法一
select
     id,
     name  
from 
     78_Students 
where
     department_id not in (select id from 78_Departments)
order by
     id asc;

-- 方法二
select 
     s.id,
     s.name
from 
     78_students s 
left join 
     78_Departments d
on 
     s.department_id=d.id
where
     d.id is null;

79. 活动参与者

写一条 SQL 查询那些既没有最多，也没有最少参与者的活动的名字，可以以任何顺序返回结果，Activities 表的每项活动的参与者都来自 Friends 表

展示效果：

+--------------+
| activity     |
+--------------+
| Singing      |
+--------------+

Create table If Not Exists 79_Friends (id int, name varchar(30), activity varchar(30));
Create table If Not Exists 79_Activities (id int, name varchar(30));
Truncate table 79_Friends;
insert into 79_Friends (id, name, activity) values ('1', 'Jonathan D.', 'Eating');
insert into 79_Friends (id, name, activity) values ('2', 'Jade W.', 'Singing');
insert into 79_Friends (id, name, activity) values ('3', 'Victor J.', 'Singing');
insert into 79_Friends (id, name, activity) values ('4', 'Elvis Q.', 'Eating');
insert into 79_Friends (id, name, activity) values ('5', 'Daniel A.', 'Eating');
insert into 79_Friends (id, name, activity) values ('6', 'Bob B.', 'Horse Riding');
Truncate table 79_Activities;
insert into 79_Activities (id, name) values ('1', 'Eating');
insert into 79_Activities (id, name) values ('2', 'Singing');
insert into 79_Activities (id, name) values ('3', 'Horse Riding');

提示：

Eating 活动有三个人参加, 是最多人参加的活动 (Jonathan D. , Elvis Q. and Daniel A.)

Horse Riding 活动有一个人参加, 是最少人参加的活动 (Bob B.)

Singing 活动有两个人参加 (Victor J. and Jade W.)

最终SQL：

-- 方法一
select 
     activity as ACTIVITY
from 
     79_friends
group by 
     activity
having
     count(*) > any(select count(*) from 79_friends group by activity)
     and
     count(*) < any (select count(*) from 79_friends group by activity);

-- 方法二
select
     activity
from 
     79_Friends
group by 1
having 
     count(distinct id) > some(select count(distinct id) from 79_Friends group by activity)
     and
     count(distinct id) < some(select count(distinct id) from 79_Friends group by activity);


-- 方法三
SELECT 
     tempAct.activity AS 'ACTIVITY'
FROM 
    (SELECT
          activity,
          COUNT(id) AS theTimes
     FROM
          79_Friends 
     GROUP BY
          activity 
     HAVING 
          theTimes NOT IN (
            (SELECT MAX(tMax.countTimes) AS maxCount 
             FROM 
                (SELECT activity, COUNT(id) AS countTimes
                 FROM 79_Friends 
                 GROUP BY activity)AS tMax),
            (SELECT MIN(tMin.countTimes) AS minCount 
             FROM 
                (SELECT activity, COUNT(id) AS countTimes
                 FROM 79_Friends 
                 GROUP BY activity)AS tMin))
    ) AS tempAct


-- 方法四
select 
     activity
from
    (select
          activity,
          rank()over(order by cnt) rk1,
          rank()over(order by cnt desc) rk2
     from
         (select
               activity,
               count(*) cnt
          from 
               79_Friends
          group by
               activity )t1
    )t2
where 
    rk1 != 1 and rk2 != 1;

提示：

     1.any关键词可以理解为“对于子查询返回的列中的任一数值，如果比较结果为true，则返回true。

     2.all的意思是“对于子查询返回的列中的所有值，如果比较结果为true，则返回true”

     3.any 和some 具有同等含义，用法也一致。

80. 顾客的可信联系人数量

为每张发票 invoice_id 编写一个SQL查询以查找以下内容：

customer_name：与发票相关的顾客名称。
price：发票的价格。
contacts_cnt：该顾客的联系人数量。
trusted_contacts_cnt：可信联系人的数量：既是该顾客的联系人又是商店顾客的联系人数量（即：可信联系人的电子邮件存在于客户表中）。

将查询的结果按照 invoice_id 排序。

展示效果：

+------------+---------------+-------+--------------+----------------------+
| invoice_id | customer_name | price | contacts_cnt | trusted_contacts_cnt |
+------------+---------------+-------+--------------+----------------------+
| 44         | Alex          | 60    | 1            | 1                    |
| 55         | John          | 500   | 0            | 0                    |
| 66         | Bob           | 400   | 2            | 0                    |
| 77         | Alice         | 100   | 3            | 2                    |
| 88         | Alice         | 200   | 3            | 2                    |
| 99         | Bob           | 300   | 2            | 0                    |
+------------+---------------+-------+--------------+----------------------+

Create table If Not Exists 80_Customers (customer_id int, customer_name varchar(20), email varchar(30));
Create table If Not Exists 80_Contacts (user_id int, contact_name varchar(20), contact_email varchar(30));
Create table If Not Exists 80_80_Invoices (invoice_id int, price int, user_id int);
Truncate table 80_Customers;
insert into 80_Customers (customer_id, customer_name, email) values ('1', 'Alice', 'alice@leetcode.com');
insert into 80_Customers (customer_id, customer_name, email) values ('2', 'Bob', 'bob@leetcode.com');
insert into 80_Customers (customer_id, customer_name, email) values ('13', 'John', 'john@leetcode.com');
insert into 80_Customers (customer_id, customer_name, email) values ('6', 'Alex', 'alex@leetcode.com');
Truncate table 80_Contacts;
insert into 80_Contacts (user_id, contact_name, contact_email) values ('1', 'Bob', 'bob@leetcode.com');
insert into 80_Contacts (user_id, contact_name, contact_email) values ('1', 'John', 'john@leetcode.com');
insert into 80_Contacts (user_id, contact_name, contact_email) values ('1', 'Jal', 'jal@leetcode.com');
insert into 80_Contacts (user_id, contact_name, contact_email) values ('2', 'Omar', 'omar@leetcode.com');
insert into 80_Contacts (user_id, contact_name, contact_email) values ('2', 'Meir', 'meir@leetcode.com');
insert into 80_Contacts (user_id, contact_name, contact_email) values ('6', 'Alice', 'alice@leetcode.com');
Truncate table 80_Invoices;
insert into 80_Invoices (invoice_id, price, user_id) values ('77', '100', '1');
insert into 80_Invoices (invoice_id, price, user_id) values ('88', '200', '1');
insert into 80_Invoices (invoice_id, price, user_id) values ('99', '300', '2');
insert into 80_Invoices (invoice_id, price, user_id) values ('66', '400', '2');
insert into 80_Invoices (invoice_id, price, user_id) values ('55', '500', '13');
insert into 80_Invoices (invoice_id, price, user_id) values ('44', '60', '6');

最终SQL：

select 
     invoice_id, 
     customer_name,
     price,
     ifnull(cnt,0) contacts_cnt,
     ifnull(bc,0) trusted_contacts_cnt 
from 
     80_Invoices i
left join
     (select
           user_id, 
           count(*) cnt
      from
           80_Contacts
      group by 
           user_id ) t1
on 
      i.user_id=t1.user_id
left join
     (select
            user_id, 
            count(*) bc
      from 
            80_Contacts
      where 
            contact_name in(select customer_name from 80_Customers )
      group by user_id )t2
on 
      i.user_id = t2.user_id
left join 
      80_Customers c
on 
      i.user_id= c.customer_id
order by 
      invoice_id;