数据库练习丽丽

题型总结

1. 查询购买了S8但是没有购买IPhone的顾客id

select distinct buyer_id
  from  Sales 
  where buyer_id in (
      select distinct buyer_id 
        from Product as t1 inner join Sales as t2 on t1.product_id = t2.product_id
       where product_name = 'S8') 
       and 
       buyer_id not in (
      select distinct buyer_id 
        from Product as t1 inner join Sales as t2 on t1.product_id = t2.product_id
       where product_name = 'iPhone');


select distinct t2.buyer_id as buyer_id
  from Product as t1 inner join Sales as t2 on t1.product_id = t2.product_id 
  group by t2.buyer_id
  having count(if(t1.product_name = 'S8',1,NULL))>=1 AND count(if(t1.product_name='iPhone',1,NULL))=0;

select distinct t2.buyer_id as buyer_id
  from Product as t1 inner join Sales as t2 on t1.product_id = t2.product_id 
  where t1.product_name = 'S8' 
     and 
         t2.buyer_id not in (
            select distinct buyer_id 
                from Product as t1 inner join Sales as t2 on t1.product_id = t2.product_id
                where t1.product_name = 'iPhone');

2. 此表没有主键，所以可能会有重复的行。
   action 字段是 ENUM 类型的，包含:(‘view’, ‘like’, ‘reaction’, ‘comment’, ‘report’, ‘share’)
   extra 字段是可选的信息（可能为 null），其中的信息例如有：1.报告理由(a reason for report) 2.反应类型(a type of reaction)

   查询每种 报告理由（report reason）在昨天的不同报告数量（post_id）。假设今天是 2019-07-05。

   ## 方法1
   select  extra as report_reason,
          count(distinct post_id) as report_count
       from Actions
       where datediff('2019-07-05',action_date) = 1 and extra is not null and action = 'report'
       group by extra 
       order by report_reason asc , report_count asc;
   
   ## 方法2
   
   select extra report_reason,count(distinct post_id) report_count
     from Actions
     where action='report' and action_date='2019-07-04'
     and extra is not null
     group by extra;
   

3. 编写 SQL 查询以查找截至 2019-07-27（含）的 30 天内每个用户的平均会话数，四舍五入到小数点后两位。只统计那些会话期间用户至少进行一项活动的有效会话

   select  round(coalesce(count(distinct session_id)/count(distinct user_id),0),2) as average_sessions_per_user
     from Activity
     where datediff('2019-07-27',activity_date) <30;
     # 注意，如果分组，就会显示的是每个用户的平均会话次数
   

4. 查询询问名称，质量，贫困的查询问题

   select query_name,
          round(avg(rating/position),2) as quality,
          round(sum(case when rating < 3 then 1 else 0 end)/count(rating)*100,2) as poor_query_percentage
     from Queries
     group by query_name; 
   

5. 编写 SQL 语句以查找每个帖子的评论数。

   结果表应包含帖子的 post_id 和对应的评论数 number_of_comments 并且按 post_id 升序排列。

   Submissions 可能包含重复的评论。您应该计算每个帖子的唯一评论数。

   Submissions 可能包含重复的帖子。您应该将它们视为一个帖子。

   结果表应该按 post_id 升序排序。

   select t2.sub_name as post_id,
          count(distinct sub_id) as number_of_comments
       from Submissions as t1 right join (select distinct sub_id as sub_name from Submissions where parent_id is null) as t2 on t1.parent_id = t2.sub_name
       group by t2.sub_name,t1.parent_id
       order by sub_name asc, number_of_comments desc ;
   

6. 查每一个学生每门课程的考试次数

   select t1.student_id as student_id,
          t1.student_name as student_name,
          t3.subject_name as subject_name,
          count( t2.subject_name ) as attended_exams
     from Students  as t1 cross join Subjects t3  left  join Examinations as t2 on t1.student_id = t2.student_id and t2.subject_name = t3.subject_name
     group by t1.student_id ,t3.subject_name
     order by t1.student_id asc ,t3.subject_name asc;
   

7. SQL 来找到表中每个国家在 2019 年 11 月的天气类型。

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

select country_name,
       case when avg(weather_state) <= 15 then 'Cold' 
            when avg(weather_state) >= 25 then 'Hot'
            else 'Warm' end as weather_type
  from Countries as t1 inner join  Weather as t2  on t1.country_id = t2.country_id 
  where  `day` between '2019-11-01' and '2019-11-30'
  group by  t1.country_id;

8. 编写一个 SQL 查询，以求得每个员工所在团队的总人数

   select employee_id,
          team_size
     from Employee as t1 inner join ( select team_id,
                                           count(case when employee_id then employee_id else 0 end) as team_size
                                       from Employee
                                       group by team_id  ) as t2 
                         on t1.team_id = t2.team_id
     group by t1.employee_id;
   

9. 计算广告转化率，计算公式：广告点击数除以广告点击数和广告观看数之和，ignored忽略就行

   select distinct ad_id,
         round(coalesce(sum(case action when 'Ignored'  then 0 when 'Clicked' then 1 else 0  end ) /
         sum(case action when 'Ignored' then 0 else 1 end )*100,0),2) as ctr
       from Ads
       group by ad_id
       order by ctr desc, ad_id asc;
   

10. 你想知道用户在你的 app 上的访问时长情况。因此决定统计访问时长区间分别为 “[0-5>”, “[5-10>”, “[10-15>” 和 “15 or more” （单位：分钟）的会话数量，并以此绘制柱状图。

    写一个SQL查询来报告（访问时长区间，会话总数）。结果可用任何顺序呈现

    使用方法：原表多加一列，进行分类，把所有的类联合成一个新的表，然后两个表做有链接

     select  t2.`bin` ,
           count(t1.session_id ) as total
      from (select session_id,
                    case when duration/60 >= 0 and duration/60 <5  then '[0-5>'
                        when duration/60 >= 5 and duration/60 <10  then '[5-10>'
                        when duration/60 >= 10 and duration/60 <15  then '[10-15>'
                        when duration/60 >= 15 then '15 or more'
                        end as session_seg
              from Sessions ) as t1
         right join
             (select '[0-5>' as `bin` union select '[5-10>'  as `bin` union select '[10-15>'  as `bin` union select '15 or more' as `bin`) as t2 
         on t1.session_seg = t2.bin
      group by t2.`bin`
      order by t2.`bin` desc;
    

11. 报告在 2020 年 6 月和 7 月 每个月至少花费 $100 的客户的 customer_id 和 customer_name 。

    select customer_id,
           name
      from Customers
      where customer_id = any(
          select  customer_id
       from(
                select customer_id,
                     case when order_date between '2020-06-01' and '2020-06-30' then 6 
                          when order_date between '2020-07-01' and '2020-07-31' then 7
                        else 0 end as month_,
                     sum(quantity * price) as money
                from Product as t1 inner join Orders as t2 on t1.product_id = t2.product_id 
            where order_date between '2020-06-01' and '2020-07-31'
            group by t2.customer_id,case when order_date between '2020-06-01' and '2020-06-30' then 6 
                          when order_date between '2020-07-01' and '2020-07-31' then 7
                        else 0 end 
            having money >= 100 ) as temp
      group by customer_id
      having sum(month_) = 13);
      
    # 方法2
    select t2.customer_id,
           name
      from Product as t1 inner join Orders as t2 on t1.product_id = t2.product_id inner join Customers as t3 on t3.customer_id = t2.customer_id
      where t2.order_date between '2020-06-01' and '2020-07-31'
      group by t2.customer_id
      having sum(if(month(order_date)=6 ,price * quantity ,0) )>= 100 and sum(if(month(order_date)=7 ,price * quantity ,0)) >= 100;
    

12. 写一条 SQL 语句，查询拥有有效邮箱的用户。

    有效的邮箱包含符合下列条件的前缀名和域名：

    前缀名是包含字母（大写或小写）、数字、下划线 ‘_’、句点 ‘.’ 和/或横杠 ‘-’ 的字符串。前缀名必须以字母开头。
    域名是 ‘@leetcode.com’ 。
    按任意顺序返回结果表。

    select user_id,
           name,
           mail
      from Users
      where mail regexp '^[a-zA-Z][0-9a-zA-Z\.\_/\-]*@leetcode[.]com$';
    

13. 因为在 2000 年该表是手工填写的，product_name 可能包含前后空格，而且包含大小写。

    写一个 SQL 语句报告每个月的销售情况：

    product_name 是小写字母且不包含前后空格
    sale_date 格式为 (‘YYYY-MM’)
    total 是产品在本月销售的次数
    返回结果以 product_name 升序 排列，如果有排名相同，再以 sale_date 升序 排列

     select 
           lower(trim(product_name)) as product_name,
           left(sale_date,7) as sale_date,
           count(sale_id) as total
      from Sales
      group by  lower(trim(product_name)),month(sale_date)
      order by product_name asc,sale_date asc;
    

14. 写一个查询语句来 按月 统计金额（invoice）大于 $20 的唯一 订单数 和唯一 顾客数 。

    select left(order_date,7) as month,
           count(distinct order_id) as order_count,
           count(distinct customer_id) as customer_count
      from Orders
      where  invoice > 20
      group by left(order_date,7);
    

15. 有一个国家只有三所学校，这个国家的每一个学生只会注册一所学校。

    这个国家正在参加一个竞赛，他们希望从这三所学校中各选出一个学生来组建一支三人的代表队。

    例如：

    member_A是从 SchoolA中选出的
    member_B是从 SchoolB中选出的
    member_C是从 SchoolC中选出的
    被选中的学生具有不同的名字和ID（没有任何两个学生拥有相同的名字、没有任何两个学生拥有相同的ID）
    使用上述条件，编写SQL查询语句来找到所有可能的三人国家代表队组合。

    查询结果接受任何顺序。

    select t1.student_name as member_A,
           t2.student_name as member_B,
           t3.student_name as member_C
      from SchoolA as t1,SchoolB as t2, SchoolC as t3
      where t1.student_name != t2.student_name and t1.student_name != t3.student_name and 
            t2.student_name != t3.student_name and t1.student_id != t2.student_id and t1.student_id != t3.student_id and t2.student_id!= t3.student_id
      group by t1.student_name ,t2.student_name, t3.student_name;
    

16. 写出一个 SQL 查询语句，查找每种产品在各个商店中的价格。

    可以以 任何顺序 输出结果。

    select product_id ,
           min(case store when 'store1' then price else null end) as store1,
           min(case store when 'store2' then price else null end) as store2,
           min(case store when 'store3' then price else null end) as store3
      from Products
      group by product_id;
    

17. 一个员工可以属于多个部门。

    当一个员工加入超过一个部门的时候，他需要决定哪个部门是他的直属部门。

    请注意，当员工只加入一个部门的时候，那这个部门将默认为他的直属部门，虽然表记录的值为’N’.

    请编写一段SQL，查出员工所属的直属部门。

    select employee_id,
           department_id
      from Employee
      group by employee_id
      having count(department_id) =1
     union 
    select employee_id,
            department_id 
      from Employee
      where primary_flag = 'Y'
      order by employee_id;
    

18. 编写 SQL 查询以查找在 24 小时窗口内两次请求确认消息的用户的 ID。 两个正好相隔 24 小时的消息被认为是在窗口内。 该操作不会影响答案，只会影响请求时间。

    以任意顺序返回结果表。

    select distinct t1.user_id as user_id
      from Confirmations as t1 ,Confirmations as t2 
      where t1.user_id = t2.user_id and t1.time_stamp < t2.time_stamp and timestampdiff(second,t1.time_stamp,t2.time_stamp) <= 24*60*60
      order by t1.user_id asc;
    

    中等

    1. 找第二高的薪水，如果只有一条记录，就显示null。

       select max(salary) as  SecondHighestSalary
         from Employee
         where salary < (select max(salary) from Employee);
       

    2. 六种方法查询MYsql查询策略

       排名是数据库中的一个经典题目，实际上又根据排名的具体细节可分为3种场景：

       连续排名，例如薪水3000、2000、2000、1000排名结果为1-2-3-4，体现同薪不同名，排名类似于编号
       同薪同名但总排名不连续，例如同样的薪水分布，排名结果为1-2-2-4
       同薪同名且总排名连续，同样的薪水排名结果为1-2-2-3
       不同的应用场景可能需要不同的排名结果，也意味着不同的查询策略。本题的目标是实现第三种排名方式下的第N个结果，且是全局排名，不存在分组的问题，实际上还要相对简单一些。

       值得一提的是：在Oracle等数据库中有窗口函数，可非常容易实现这些需求，而MySQL直到8.0版本也引入相关函数。最新OJ环境已更新至8.0版本，可直接使用窗口函数。

思路1：单表查询
由于本题不存在分组排序，只需返回全局第N高的一个，所以自然想到的想法是用order by排序加limit限制得到。需要注意两个细节：
同薪同名且不跳级的问题，解决办法是用group by按薪水分组后再order by
排名第N高意味着要跳过N-1个薪水，由于无法直接用limit N-1，所以需先在函数开头处理N为N=N-1。
注：这里不能直接用limit N-1是因为limit和offset字段后面只接受正整数（意味着0、负数、小数都不行）或者单一变量（意味着不能用表达式），也就是说想取一条，limit 2-1、limit 1.1这类的写法都是报错的。
注：这种解法形式最为简洁直观，但仅适用于查询全局排名问题，如果要求各分组的每个第N名，则该方法不适用；而且也不能处理存在重复值的情况。

 ##### 代码1
   CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
   BEGIN
       SET N := N-1;
     RETURN (
         # Write your MySQL query statement below.
         SELECT 
               salary
         FROM 
               employee
         GROUP BY 
               salary
         ORDER BY 
               salary DESC
         LIMIT N, 1
     );
   END

• 运行时间：175s

思路2：子查询
排名第N的薪水意味着该表中存在N-1个比其更高的薪水
注意这里的N-1个更高的薪水是指去重后的N-1个，实际对应人数可能不止N-1个
最后返回的薪水也应该去重，因为可能不止一个薪水排名第N
由于对于每个薪水的where条件都要执行一遍子查询，注定其效率低下

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
          DISTINCT e.salary
      FROM 
          employee e
      WHERE 
          (SELECT count(DISTINCT salary) FROM employee WHERE salary>e.salary) = N-1
  );
END

• 查询效率 479 ms

思路3：自连接

一般来说，能用子查询解决的问题也能用连接解决。具体到本题：

两表自连接，连接条件设定为表1的salary小于表2的salary
以表1的salary分组，统计表1中每个salary分组后对应表2中salary唯一值个数，即去重
限定步骤2中having 计数个数为N-1，即实现了该分组中表1salary排名为第N个
考虑N=1的特殊情形(特殊是因为N-1=0，计数要求为0)，此时不存在满足条件的记录数，但仍需返回结果，所以连接用left join
如果仅查询薪水这一项值，那么不用left join当然也是可以的，只需把连接条件放宽至小于等于、同时查询个数设置为N即可。因为连接条件含等号，所以一定不为空，用join即可。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
          e1.salary
      FROM 
          employee e1 JOIN employee e2 ON e1.salary <= e2.salary
      GROUP BY 
          e1.salary
      HAVING 
          count(DISTINCT e2.salary) = N
  );
END

• 查询效率: 435ms

思路4：笛卡尔积

当然，可以很容易将思路2中的代码改为笛卡尔积连接形式，其执行过程实际上一致的，甚至MySQL执行时可能会优化成相同的查询语句。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
          e1.salary
      FROM 
          employee e1, employee e2 
      WHERE 
          e1.salary <= e2.salary
      GROUP BY 
          e1.salary
      HAVING 
          count(DISTINCT e2.salary) = N
  );
END

• 查询效率:570ms

思路5 ：自定义变量

以上方法2-4中均存在两表关联的问题，表中记录数少时尚可接受，当记录数量较大且无法建立合适索引时，实测速度会比较慢，用算法复杂度来形容大概是O(n^2)量级（实际还与索引有关）。那么，用下面的自定义变量的方法可实现O(2*n)量级，速度会快得多，且与索引无关。

自定义变量实现按薪水降序后的数据排名，同薪同名不跳级，即3000、2000、2000、1000排名后为1、2、2、3；
对带有排名信息的临时表二次筛选，得到排名为N的薪水；
因为薪水排名为N的记录可能不止1个，用distinct去重

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
          DISTINCT salary 
      FROM 
          (SELECT 
                salary, @r:=IF(@p=salary, @r, @r+1) AS rnk,  @p:= salary 
            FROM  
                employee, (SELECT @r:=0, @p:=NULL)init 
            ORDER BY 
                salary DESC) tmp
      WHERE rnk = N
  );
END

查询效率178ms

思路6：窗口函数

实际上，在mysql8.0中有相关的内置函数，而且考虑了各种排名问题：

row_number(): 同薪不同名，相当于行号，例如3000、2000、2000、1000排名后为1、2、3、4
rank(): 同薪同名，有跳级，例如3000、2000、2000、1000排名后为1、2、2、4
dense_rank(): 同薪同名，无跳级，例如3000、2000、2000、1000排名后为1、2、2、3
ntile(): 分桶排名，即首先按桶的个数分出第一二三桶，然后各桶内从1排名，实际不是很常用
显然，本题是要用第三个函数。
另外这三个函数必须要要与其搭档over()配套使用，over()中的参数常见的有两个，分别是

partition by，按某字段切分
order by，与常规order by用法一致，也区分ASC(默认)和DESC，因为排名总得有个依据
注：下面代码仅在mysql8.0以上版本可用，最新OJ已支持。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
        SELECT 
            DISTINCT salary
        FROM 
            (SELECT 
                salary, dense_rank() over(ORDER BY salary DESC) AS rnk
             FROM 
                employee) tmp
        WHERE rnk = N
  );
END

查询效率：324ms

总结：MYSQL查询的一般思路是：

• 能用单表优先用单表，即便是需要用group by、order by、limit等，效率一般也比多表高

• 不能用单表时优先用连接，连接是SQL中非常强大的用法，小表驱动大表+建立合适索引+合理运用连接条件，基本上连接可以解决绝大部分问题。但join级数不宜过多，毕竟是一个接近指数级增长的关联效果

• 能不用子查询、笛卡尔积尽量不用，虽然很多情况下MySQL优化器会将其优化成连接方式的执行过程，但效率仍然难以保证

• 自定义变量在复杂SQL实现中会很有用，例如LeetCode中困难级别的数据库题目很多都需要借助自定义变量实现

• 如果MySQL版本允许，某些带聚合功能的查询需求应用窗口函数是一个最优选择。除了经典的获取3种排名信息，还有聚合函数、向前向后取值、百分位等，具体可参考官方指南。以下是官方给出的几个窗口函数的介绍：

• 最后的最后再补充一点，本题将查询语句封装成一个自定义函数并给出了模板，实际上是降低了对函数语法的书写要求和难度，而且提供的函数写法也较为精简。然而，自定义函数更一般化和常用的写法应该是分三步：

定义变量接收返回值
执行查询条件，并赋值给相应变量
返回结果
例如以解法5为例，如下写法可能更适合函数初学者理解和掌握：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    # i 定义变量接收返回值
    DECLARE ans INT DEFAULT NULL;  
    # ii 执行查询语句，并赋值给相应变量
    SELECT 
        DISTINCT salary INTO ans
    FROM 
        (SELECT 
            salary, @r:=IF(@p=salary, @r, @r+1) AS rnk,  @p:= salary 
        FROM  
            employee, (SELECT @r:=0, @p:=NULL)init 
        ORDER BY 
            salary DESC) tmp
    WHERE rnk = N;
    # iii 返回查询结果，注意函数名中是 returns，而函数体中是 return
    RETURN ans;
END

###
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      select distinct t1.salary as getNthHighestSalary
        from Employee as t1
        where (select count(distinct t2.salary)
                 from Employee as t2
                 where t1.salary < t2.salary) = n-1
      
  );
END

3. 找每个部门的最高的人（部门名称，姓名，薪水）

   ```sql
   select t2.name as Department,
          t1.name as  Employee,
          t1.salary as Salary
     from Employee as t1 inner join  Department as t2 on t1.departmentId = t2.id
     where (t1.departmentId ,t1.salary) in (select t2.id,max(salary)
                                     from Employee as t1 inner join  Department as t2 on t1.departmentId = t2.id
                                     group by t2.id,t2.name );
   ```
   

4. 查询用户编号，注册时间，2019年交易记录

## 方法2 
select distinct user_id as buyer_id,
       join_date,
      count( order_id ) as orders_in_2019
  from Users as t1 left  join Orders as t2 on t1.user_id = t2.buyer_id and year(order_date) = '2019'
  group by user_id ;
## 方法2
select distinct user_id as buyer_id,
       join_date,
       coalesce(orders_in_2019,0) as orders_in_2019
  from Users as t1 left join (
                select buyer_id,
                count(order_date) as orders_in_2019
            from Orders
            where year(order_date) = '2019'
            group by buyer_id) as t2 on t1.user_id = t2.buyer_id
    group by user_id;

5. 换座位

select case when  id in (select max(id) from Seat)  and id mod 2 <> 0 then id when id mod 2 != 0 then id+1 else id-1 end as id , 
       student
  from Seat
  order by id ;

6. 编写一个 SQL 查询，同时报告每组玩家和日期，以及玩家到目前为止玩了多少游戏。也就是说，在此日期之前玩家所玩的游戏总数。要看到截止今天某个日期为止玩了多少场游戏。思路，建立两个表的自连接，条件是id值相等，并且一个表的日期大于另一个表的日期，拿出来，计算游戏次数。

   select  t1.player_id as player_id,
           t1.event_date as event_date,
           sum(t2.games_played) as games_played_so_far
      from Activity as t1, Activity as t2
      where t1.player_id = t2.player_id and t1.event_date >= t2.event_date
      group by t1.player_id,t1.event_date
      order by t1.player_id asc , t1.event_date desc;
   

7. 编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的比率，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

   思路：找到每个万家刚开始玩游戏的记录日期，再加1，如果后面的登录日期有在这个集合里面，那么就是连续登录两天的。

   select  round(count(distinct player_id)/(select count(distinct player_id) from Activity  ),2) as fraction
     from Activity
     where (player_id,event_date) in  (select player_id,date(min(event_date)+1) 
                     from Activity
                     group by player_id  );
   

8. 写一个查询语句，找出拥有最多的好友的人和他拥有的好友数目。

   生成的测试用例保证拥有最多好友数目的只有 1 个人。

     select id,
            sum(num) as num
       from ((select requester_id  as id , count(distinct accepter_id) as num from  RequestAccepted group by requester_id)
              union all
              (select accepter_id as id ,count(distinct requester_id) as num from RequestAccepted group by accepter_id))as temp
       group by id
       order by num desc
       limit 1;
   

9. 编写一个 SQL 查询，选出每个销售产品 第一年 销售的 产品 id、年份、数量 和 价格。

   结果表中的条目可以按 任意顺序 排列。

   # 方法1
   select  t2.product_id ,
          t2.first_year,
         quantity,
         price
     from  Sales as t1 right join (select distinct  product_id,min(year) as first_year from Sales group by product_id) as t2 on t1.product_id = t2.product_id and t1.year = t2.first_year;
   
   # 方法2
   select product_id,
          year as first_year,
          quantity,
          price
     from Sales
     where (product_id,year) in (select product_id,min(year) from Sales group by product_id);
   
   # 方法3
   select product_id,
          year as first_year,
          quantity,
          price
     from (select product_id,
                  year,
                  quantity,
                  price,
                  rank()  over( partition by product_id order by year asc) as year_rn
             from Sales) as temp
    where temp.year_rn = 1;
   

10. 写 一个 SQL 查询语句，报告在每一个项目中经验最丰富的雇员是谁。如果出现经验年数相同的情况，请报告所有具有最大经验年数的员工。

    # 方法1 两个表的内连接，然后做了一个集合查询，子查询先找出每个项目的最大的工作年限的人
    select project_id,
           t1.employee_id
     from Project as t1 inner join Employee as t2 on t1.employee_id = t2.employee_id
     where (project_id,experience_years) in (select project_id,
                                                    max(experience_years)
                                                from Project as t1 inner join Employee as t2 on t1.employee_id = t2.employee_id
                                                group by project_id);
    # 方法2 使用窗口函数，对每个项目的人都按照工作年限进行排名，由大到小进行排名，然后取第一名，就可以把工作年限最多的人拿到。
    select project_id,
           employee_id
      from (
          select project_id,
                 t1.employee_id as employee_id,
                 experience_years,
                 rank() over(partition by project_id order by experience_years desc ) as year_rank
            from Project as t1 inner join Employee as t2 on t1.employee_id = t2.employee_id) as temp
      where year_rank = 1;
    

11. 计算布尔值

    select left_operand,
           operator,
           right_operand,
           case when t1.value > t3.value and operator = '>' then 'true' 
                when t1.value = t3.value  and operator = '=' then 'true'
                when t1.value < t3.value and operator = '<' then 'true'
                when t1.value = t3.value and operator = '=' then 'true'
                else 'false' end as value
      from Variables as t1 inner join Expressions as t2 on t1.name = t2.left_operand  inner join Variables as t3 on t3.name = t2.right_operand;
    

12. 写一个 SQL 查询, 找到活跃用户的 id 和 name.

    活跃用户是指那些至少连续 5 天登录账户的用户.

    返回的结果表按照 id 排序.

    select *
      from Accounts
      where id in (select distinct t1.id
                     from Logins as t1 inner join Logins as t2 on t1.id = t2.id and datediff(t2.login_date,t1.login_date) between 0 and 4 
                  group by t1.id,t1.login_date
                  having count(distinct  t2.login_date) >= 5)
    order by id asc;
    

13. 写一个 SQL 语句, 找到所有遗失的顾客id. 遗失的顾客id是指那些不在 Customers 表中, 值却处于 1 和表中最大 customer_id 之间的id.

    注意: 最大的 customer_id 值不会超过 100.

    返回结果按 ids 升序排列

     with recursive C(n) as
        (select 1
        union 
        select n+1
        from C
        where n<(select max(customer_id) from Customers))
     select n as ids 
     from C
     where n not in (select distinct customer_id from Customers);
    

    • 使用递归的思想，例题最大值是5，就会产生一个公差为1的等差数列，即[1,5],然后查询不在这个区间的数字。

14. 找中位数

    # 使用窗口函数
    select id,
           t1.company,
           t1.salary
     from (
            select id,
                company,
                salary,
                row_number() over(partition by company  order by salary asc,id asc) as ran    
            from Employee )  as t1 inner join (select company , count(id) as num from Employee group by company) as t2 on t1.company=t2.company
     where t1.ran >= t2.num/2 and t1.ran <= t2.num/2 +1
     order by t1.company asc,t1.salary asc;
    
    # 不使用窗口函数
    
      select  id,
           t1.company,
           t1.salary
    from (
    
            select id,
                company,
                salary,
                if(@row_company=company,@row_num:=@row_num +1,@row_num := 1) row_numb,
                 @row_company:=company 
              from Employee,(select @row_num:=0,@row_company:='') temp
             order by company asc,salary asc) as t1 , (select company , count(*) as num from Employee group by company) as t2 
             where  t1.company=t2.company  and row_numb >= num/2 and row_numb <= num/2+1;
    

15. 请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）。

    结果请按 Id 升序，然后按 Month 降序显示。

    select t1.Id as id,t1.Month as month,sum(t2.Salary) as Salary
    from (
            select Id,
                    `Month`,
                    Salary
            from Employee
            where (Id,month) not in (select Id,max(month) as max_month
                                        from Employee
                                        group by Id)
        ) as t1 left join  Employee as t2 on t1.id = t2.id and t1.month - t2.month >= 0 and t1.month - t2.month <= 2
     """
     | Id | Month | Salary | id | month | salary |
    | -- | ----- | ------ | -- | ----- | ------ |
    | 1  | 1     | 20     | 1  | 1     | 20     |
    | 2  | 1     | 20     | 2  | 1     | 20     |
    | 1  | 2     | 30     | 1  | 2     | 30     |
    | 1  | 2     | 30     | 1  | 1     | 20     |
    | 3  | 2     | 40     | 3  | 2     | 40     |
    | 1  | 3     | 40     | 1  | 3     | 40     |
    | 1  | 3     | 40     | 1  | 2     | 30     |
    | 1  | 3     | 40     | 1  | 1     | 20     |
    | 3  | 3     | 60     | 3  | 3     | 60     |
    | 3  | 3     | 60     | 3  | 2     | 40     |
    | 1  | 4     | 60     | 1  | 4     | 60     |
    | 1  | 4     | 60     | 1  | 3     | 40     |
    | 1  | 4     | 60     | 1  | 2     | 30     |
    | 1  | 7     | 90     | 1  | 7     | 90     | 
     
     """
    ## 实际代码
    请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）。
    
    结果请按 Id 升序，然后按 Month 降序显示。
    
    select t1.Id as id,t1.Month as month,sum(t2.Salary) as Salary
    from (
            select Id,
                    `Month`,
                    Salary
            from Employee
            where (Id,month) not in (select Id,max(month) as max_month
                                        from Employee
                                        group by Id)
        ) as t1 left join  Employee as t2 on t1.id = t2.id and t1.month - t2.month >= 0 and t1.month - t2.month <= 2
      group by id, month 
      order by Id, month desc,Salary desc;