题型总结

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

select distinct buyer_idfrom  Sales where buyer_id in (select distinct buyer_id from Product as t1 inner join Sales as t2 on t1.product_id = t2.product_idwhere 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_idwhere product_name = 'iPhone');select distinct t2.buyer_id as buyer_idfrom Product as t1 inner join Sales as t2 on t1.product_id = t2.product_id group by t2.buyer_idhaving 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_idfrom 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_idwhere 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_countfrom Actionswhere 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;## 方法2select extra report_reason,count(distinct post_id) report_countfrom Actionswhere action='report' and action_date='2019-07-04'and extra is not nullgroup 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_userfrom Activitywhere 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_percentagefrom Queriesgroup 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_commentsfrom 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_namegroup by t2.sub_name,t1.parent_idorder 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_examsfrom 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_namegroup by t1.student_id ,t3.subject_nameorder 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_typefrom 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_sizefrom Employee as t1 inner join ( select team_id,count(case when employee_id then employee_id else 0 end) as team_sizefrom Employeegroup by team_id  ) as t2 on t1.team_id = t2.team_idgroup 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 ctrfrom Adsgroup by ad_idorder 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 totalfrom (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_segfrom Sessions ) as t1right 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.bingroup by t2.`bin`order by t2.`bin` desc;
    

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

    select customer_id,namefrom Customerswhere customer_id = any(select  customer_idfrom(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 7else 0 end as month_,sum(quantity * price) as moneyfrom 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 7else 0 end having money >= 100 ) as tempgroup by customer_idhaving sum(month_) = 13);# 方法2
    select t2.customer_id,namefrom 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_idwhere t2.order_date between '2020-06-01' and '2020-07-31'group by t2.customer_idhaving 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,mailfrom Userswhere 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 totalfrom Salesgroup 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_countfrom Orderswhere  invoice > 20group 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_Cfrom SchoolA as t1,SchoolB as t2, SchoolC as t3where 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_idgroup 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 store3from Productsgroup by product_id;
    

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

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

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

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

    select employee_id,department_idfrom Employeegroup by employee_idhaving count(department_id) =1union 
    select employee_id,department_id from Employeewhere primary_flag = 'Y'order by employee_id;
    

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

    以任意顺序返回结果表。

    select distinct t1.user_id as user_idfrom 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*60order by t1.user_id asc;
    

    中等

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

       select max(salary) as  SecondHighestSalaryfrom Employeewhere 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名，则该方法不适用；而且也不能处理存在重复值的情况。

 ##### 代码1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGINSET N := N-1;RETURN (# Write your MySQL query statement below.SELECT salaryFROM employeeGROUP BY salaryORDER BY salary DESCLIMIT N, 1);END

• 运行时间：175s

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

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGINRETURN (# Write your MySQL query statement below.SELECT DISTINCT e.salaryFROM employee eWHERE (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
BEGINRETURN (# Write your MySQL query statement below.SELECT e1.salaryFROM employee e1 JOIN employee e2 ON e1.salary <= e2.salaryGROUP BY e1.salaryHAVING count(DISTINCT e2.salary) = N);
END

• 查询效率: 435ms

思路4：笛卡尔积

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

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGINRETURN (# Write your MySQL query statement below.SELECT e1.salaryFROM employee e1, employee e2 WHERE e1.salary <= e2.salaryGROUP BY e1.salaryHAVING 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
BEGINRETURN (# 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) tmpWHERE 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
BEGINRETURN (# Write your MySQL query statement below.SELECT DISTINCT salaryFROM (SELECT salary, dense_rank() over(ORDER BY salary DESC) AS rnkFROM employee) tmpWHERE 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 ansFROM (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) tmpWHERE rnk = N;# iii 返回查询结果，注意函数名中是 returns，而函数体中是 returnRETURN ans;
END

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

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

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

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

## 方法2 
select distinct user_id as buyer_id,join_date,count( order_id ) as orders_in_2019from 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_2019from Users as t1 left join (select buyer_id,count(order_date) as orders_in_2019from Orderswhere year(order_date) = '2019'group by buyer_id) as t2 on t1.user_id = t2.buyer_idgroup 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 , studentfrom Seatorder 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_farfrom Activity as t1, Activity as t2where t1.player_id = t2.player_id and t1.event_date >= t2.event_dategroup by t1.player_id,t1.event_dateorder 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 fractionfrom Activitywhere (player_id,event_date) in  (select player_id,date(min(event_date)+1) from Activitygroup by player_id  );
   

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

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

     select id,sum(num) as numfrom ((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 tempgroup by idorder by num desclimit 1;
   

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

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

   # 方法1
   select  t2.product_id ,t2.first_year,quantity,pricefrom  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,pricefrom Saleswhere (product_id,year) in (select product_id,min(year) from Sales group by product_id);# 方法3
   select product_id,year as first_year,quantity,pricefrom (select product_id,year,quantity,price,rank()  over( partition by product_id order by year asc) as year_rnfrom Sales) as tempwhere temp.year_rn = 1;
   

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

    # 方法1 两个表的内连接，然后做了一个集合查询，子查询先找出每个项目的最大的工作年限的人
    select project_id,t1.employee_idfrom Project as t1 inner join Employee as t2 on t1.employee_id = t2.employee_idwhere (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_idgroup by project_id);
    # 方法2 使用窗口函数，对每个项目的人都按照工作年限进行排名，由大到小进行排名，然后取第一名，就可以把工作年限最多的人拿到。
    select project_id,employee_idfrom (select project_id,t1.employee_id as employee_id,experience_years,rank() over(partition by project_id order by experience_years desc ) as year_rankfrom Project as t1 inner join Employee as t2 on t1.employee_id = t2.employee_id) as tempwhere 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 valuefrom 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 Accountswhere id in (select distinct t1.idfrom 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_datehaving 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 1union select n+1from Cwhere n<(select max(customer_id) from Customers))select n as ids from Cwhere n not in (select distinct customer_id from Customers);
    

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

14. 找中位数

    # 使用窗口函数
    select id,t1.company,t1.salaryfrom (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.companywhere t1.ran >= t2.num/2 and t1.ran <= t2.num/2 +1order 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:='') temporder 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`,Salaryfrom Employeewhere (Id,month) not in (select Id,max(month) as max_monthfrom Employeegroup 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`,Salaryfrom Employeewhere (Id,month) not in (select Id,max(month) as max_monthfrom Employeegroup 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 <= 2group by id, month order by Id, month desc,Salary desc;