数据库系统-刷题

智汇君2025-01-17

数据库系统-刷题

mysql

常问问题

常用的存储引擎？InnoDB与MyISAM的区别？

什么是事务,以及事务的四大特性?

事务的隔离级别有哪些,MySQL默认是哪个?

内连接与左外连接的区别是什么?

MySQL默认InnoDB引擎的索引是什么数据结构?

如何查看MySQL的执行计划?

索引失效的情况有哪些?

什么是回表查询?

什么是MVCC?

MySQL主从复制的原理是什么?

主从复制之后的读写分离如何实现?

数据库的分库分表如何实现?

SQL性能优化经验

1.sql优化大多依赖索引进行
2.聚集索引和二级索引对于理解sql优化很关键(能使用聚集使用过滤就不使用二级索引，效率更高)
3.满足业务需求的情况下，尽量降低主键的长度 --因为二级索引叶子节点会存储主键，会导致索引数据大
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。 --避免页分裂
尽量不要使用UUID做主键或者是其他自然主键，如身份证号  --长度长且无序
4.load data批量插入数据
5.sql执行频率
6.慢查询
7.执行计划
8.profile详情

可以从这几个维度回答这个问题：
加索引
避免返回不必要的数据
适当分批量进行
优化sql结构
分库分表
读写分离

1
2
3

数据库优化经验

四五千万条记录时，就可能要考虑分库分表了，这是运维篇的内容

建立索引的优劣势

优缺点
优势
提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势
索引列也是要占用空间的
索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

为什么InnoDB存储引擎选择使用B+tree索引结构?

相对于二叉树，层级更少，搜索效率高;
对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
相对Hash索引，B+tree支持范围匹配及排序操作

估算主键索引树2，3层时，大概能存储多少行数据？

只要给出主键什么类型，多大，然后一行数据多大？就可以估算
1.先算一个页最大能存储多少个主键 2.然后算出指针数，再乘以一个页最大能存储多少行数据就行

MySQL 索引使用有哪些注意事项呢？

讲的10条
+
在索引列上使用mysql的内置函数，索引失效。
对索引列运算（如，+、-、*、/），索引失效。
索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。
左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

索引不适合哪些场景

数据量少的不适合加索引
更新比较频繁的也不适合加索引
区分度低的字段不适合加索引（如性别）

针对于数据量较大(300w条)，且查询比较频繁的表建立索引。
针对于常作为查询条件(where)、排序 (order by)、分组 (group by)操作的字段建立索引。
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。要控制素引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

MySQL 遇到过死锁问题吗，你是如何解决的？
我排查死锁的一般步骤是酱紫的：

查看死锁日志show engine innodb status;
找出死锁Sql
分析sql加锁情况
模拟死锁案发
分析死锁日志
分析死锁结果

1	聚集索引与非聚集索引的区别

LEECODE题目

LeeCode Wiki

177.第N高的薪水函数 dense_rank limit

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      select (
          select distinct salary from (
              select salary, dense_rank() over (order by salary desc) rnk from employee
          )t
          where rnk=N
      )getNthHighestSalary
  );
END

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N=N-1;
  RETURN (
        SELECT distinct salary FROM Employee ORDER BY salary DESC LIMIT N, 1   
  );
END

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
            with t1 as (
          select * ,dense_rank() over (order by Salary desc) as rk
          from Employee
      )
      select distinct ifnull(Salary ,null)
      from t1
      where rk = N
  );
END

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT; 
    SET M = N-1; 
  RETURN (
      SELECT DISTINCT salary
      FROM Employee
      ORDER BY salary DESC
      LIMIT M, 1
  );
END

178.分数排名 dense_rank b.score >= a.score

表: Scores

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| score       | decimal |
+-------------+---------+
id 是该表的主键（有不同值的列）。
该表的每一行都包含了一场比赛的分数。Score 是一个有两位小数点的浮点值。
 

编写一个解决方案来查询分数的排名。排名按以下规则计算:

分数应按从高到低排列。
如果两个分数相等，那么两个分数的排名应该相同。
在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

select
a.score as Score,
count(DISTINCT b.score) AS Rank # 统计b表符合条件的不重复的分数的数量作为排名
FROM scores a join scores b
where b.score >= a.score # 条件是这个分数不小于我，因为a、b表数据相同，所以排名值最小是1
group by a.id # a表中每个数据都进行排名
order by a.score DESC # 最后按分数（跟排名一样）降序排列

1	dense_rank

180.连续出现的数字 lag lead with 变量多同表连接

表：Logs
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
在 SQL 中，id 是该表的主键。
id 是一个自增列。

找出所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
结果格式如下面的例子所示：

示例 1:
输入：
Logs 表：
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
输出：
Result表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
解释：1是唯一连续出现至少三次的数字。

select
distinct p.num as ConsecutiveNums
from(
    select
    id,
    num,
    lag(num,1)over(order by id) num1,
    lag(num,2)over(order by id) num2
    from
    Logs
) p
where
p.num = p.num1 and p.num1  = p.num2

# Write your MySQL query statement below
with p as(
    (
    select
    id,
    num,
    lag(num,1)over(order by id) num1,
    lag(num,2)over(order by id) num2,
    lead(num,1)over(order by id) num3,
    lead(num,2)over(order by id) num4
    from Logs
) 

)
select
distinct p.num as ConsecutiveNums
from p
where
p.num = p.num1 and p.num1  = p.num2 || p.num1 = p.num = p.num3 || p.num = p.num3 = p.num4

select distinct Num as ConsecutiveNums
from (
  select Num, 
    case 
      when @prev = Num then @count := @count + 1
      when (@prev := Num) is not null then @count := 1
    end as CNT
  from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3

1
2
3

select distinct a.Num as ConsecutiveNums
from Logs as a,Logs as b,Logs as c
where a.Num=b.Num and b.Num=c.Num and a.id=b.id-1 and b.id=c.id-1;

184.部门工资最高的员工

SELECT 
    d.name AS Department,
    e.name AS Employee,
    e.salary AS Salary
FROM (
    SELECT 
    name, 
    salary,
    departmentId, 
    RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS `rank`
    FROM Employee
) e 
LEFT JOIN Department d 
    ON e.departmentId = d.id
WHERE e.`rank` = 1

# Write your MySQL query statement below
select 
    d.Name as Department,
    e.Name as Employee,
    e.Salary 
from 
    Employee e,Department d 
where
    e.DepartmentId=d.id 
    and
    (e.Salary,e.DepartmentId) in (select max(Salary),DepartmentId from Employee group by DepartmentId);

185.部门工资前三高的所有员工

表: Employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id 是该表的主键列(具有唯一值的列)。
departmentId 是 Department 表中 ID 的外键（reference 列）。
该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。
 

表: Department

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id 是该表的主键列(具有唯一值的列)。
该表的每一行表示部门ID和部门名。
 

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。
编写解决方案，找出每个部门中 收入高的员工 。
以 任意顺序 返回结果表。
返回结果格式如下所示。

# Write your MySQL query statement below
with t as(
select e1.departmentId,count(distinct e2.Salary) as r,e1.id
from Employee e1 join Employee e2 
on e1.departmentId = e2.departmentId and e1.salary <= e2.salary
group by e1.id
having r<3
)

select d.name as Department,e.name as Employee,e.salary as Salary
from Department as d,t,Employee as e
where t.departmentId = d.id and t.id = e.id

1	dense_rank()

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )
;

196.删除重复的电子邮箱连表delete

表: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id 是该表的主键列(具有唯一值的列)。
该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。
 

编写解决方案 删除 所有重复的电子邮件，只保留一个具有最小 id 的唯一电子邮件。

1 2	delete b from person a join person b on a.email = b.email and b.id > a.id

with t as
(select min(id) id,email
from Person
group by email
having count(*) > 1)

delete Person
from Person,t
where Person.id !=t.id and Person.email = t.email

197.上升的温度 datediff lag date_add

表： Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id 是该表具有唯一值的列。
没有具有相同 recordDate 的不同行。
该表包含特定日期的温度信息
 

编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 。

select a.id
from weather a, weather b
where a.temperature > b.temperature
and datediff(a.recordDate, b.recordDate) = 1;

lag()

262.行程和用户 IF Case

表：Trips
+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| client_id   | int      |
| driver_id   | int      |
| city_id     | int      |
| status      | enum     |
| request_at  | varchar  |     
+-------------+----------+
id 是这张表的主键（具有唯一值的列）。
这张表中存所有出租车的行程信息。每段行程有唯一 id ，其中 client_id 和 driver_id 是 Users 表中 users_id 的外键。
status 是一个表示行程状态的枚举类型，枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。
表：Users

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| users_id    | int      |
| banned      | enum     |
| role        | enum     |
+-------------+----------+
users_id 是这张表的主键（具有唯一值的列）。
这张表中存所有用户，每个用户都有一个唯一的 users_id ，role 是一个表示用户身份的枚举类型，枚举成员为 (‘client’, ‘driver’, ‘partner’) 。
banned 是一个表示用户是否被禁止的枚举类型，枚举成员为 (‘Yes’, ‘No’) 。
取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。

编写解决方案找出 "2013-10-01" 至 "2013-10-03" 期间有 至少 一次行程的非禁止用户（乘客和司机都必须未被禁止）的 取消率。非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。

返回结果表中的数据 无顺序要求 。

SELECT T.request_at AS `Day`, 
	ROUND(
			SUM(
				IF(T.STATUS = 'completed',0,1)
			)
			/ 
			COUNT(T.STATUS),
			2
	) AS `Cancellation Rate`
FROM Trips AS T
JOIN Users AS U1 ON (T.client_id = U1.users_id AND U1.banned ='No')
JOIN Users AS U2 ON (T.driver_id = U2.users_id AND U2.banned ='No')
WHERE T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at

case when

avg精彩用法

# 精彩
# Write your MySQL query statement below
SELECT
    request_at as 'Day', round(avg(Status!='completed'), 2) as 'Cancellation Rate'
FROM 
    trips t JOIN users u1 ON (t.client_id = u1.users_id AND u1.banned = 'No')
    JOIN users u2 ON (t.driver_id = u2.users_id AND u2.banned = 'No')
WHERE	
    request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY 
    request_at

534.游戏玩法分析 III sum() over(partition by

表：Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
（player_id，event_date）是此表的主键（具有唯一值的列）。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0 ）。

编写一个解决方案，同时报告每组玩家和日期，以及玩家到 目前为止 玩了多少游戏。也就是说，玩家在该日期之前所玩的游戏总数。详细情况请查看示例。

以 任意顺序 返回结果表。

结果格式如下所示。

示例 1：

输入：
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-05-02 | 6            |
| 1         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
输出：
+-----------+------------+---------------------+
| player_id | event_date | games_played_so_far |
+-----------+------------+---------------------+
| 1         | 2016-03-01 | 5                   |
| 1         | 2016-05-02 | 11                  |
| 1         | 2017-06-25 | 12                  |
| 3         | 2016-03-02 | 0                   |
| 3         | 2018-07-03 | 5                   |
+-----------+------------+---------------------+

-- 方案一：
select 
    a1.player_id,
    a1.event_date,
    sum(a2.games_played) as games_played_so_far
from Activity a1
join Activity a2
on a1.player_id=a2.player_id and a1.event_date>=a2.event_date  -- 关联当前时间之前的数据
group by a1.player_id,a1.event_date;
 
 
-- 方案二：
select
    player_id,
    event_date,
    sum(games_played) over(partition by player_id order by event_date)  games_played_so_far 
from Activity;

难 569.游戏玩法分析 IV partition by 中位数

SELECT Id, Company, salary
FROM Employee
WHERE Id in
(SELECT E1.Id
FROM Employee AS E1
JOIN
Employee AS E2 
ON E1.company = E2.Company
GROUP BY E1.Id
HAVING SUM(IF(E1.Salary >=E2.Salary,1,0))>= COUNT(*)/ 2
AND SUM(IF(E1.Salary<=E2.salary,1,0))>= COUNT(*)/ 2)
GROUP BY Company,Salary
ORDER BY Company

--方法一：
--注意事项：排序时用row_number,会有排名相同的情况
--中位数的逻辑了解：不论个数是奇数偶数，中位数在总数除以2和总数除以2加1之间
select id ,company,salary
from (
    select *
    ,row_number() over(partition by company order by salary) as rnk
    ,count(*) over(partition by company ) as cnt
    from Employee) a
where  rnk BETWEEN cnt*1.0/2 AND cnt*1.0/2 + 1

难 571.给定数字的频率查询中位数

参考

Numbers 表保存数字的值及其频率。

+----------+-------------+
|  Number  |  Frequency  |
+----------+-------------|
|  0       |  7          |
|  1       |  1          |
|  2       |  3          |
|  3       |  1          |
+----------+-------------+
在此表中，数字为 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3，所以中位数是 (0 + 0) / 2 = 0。

+--------+
| median |
+--------|
| 0.0000 |
+--------+
请编写一个查询来查找所有数字的中位数并将结果命名为 median 。

SELECT *
FROM 
(
	SELECT N1.NUMBER, SUM(IF(N2.frequency IS NULL,0,N2.frequency)) AS `beg`
	FROM numbers AS N1
	LEFT JOIN numbers AS N2 ON (N1.NUMBER > N2.NUMBER)
	GROUP BY N1.number
	ORDER BY N1.NUMBER
) AS A
JOIN 
(
	SELECT N1.NUMBER, SUM(IF(N2.frequency IS NULL,0,N2.frequency)) AS `end`
	FROM numbers AS N1
	LEFT JOIN numbers AS N2 ON (N1.NUMBER >= N2.NUMBER)
	GROUP BY N1.number
	ORDER BY N1.number
) AS B
	ON (A.NUMBER = B.NUMBER)
JOIN 
(
	SELECT FLOOR((SUM(N.frequency)-1)/2) AS `beg`, if(SUM(N.frequency)%2=1,0,1) AS `cnt`
	FROM numbers AS N
) AS C
	ON (
			(
				(C.beg <= A.beg AND A.beg < (C.beg +C.cnt+1)) 
				OR 
				(C.beg < B.END AND B.END <= (C.beg +C.cnt+1))
			)
			OR
			(
				(A.beg <= C.beg AND C.beg < B.end) 
				OR 
				(A.beg < (C.beg+C.cnt+1) AND (C.beg+C.cnt+1) <= B.END)
			)
		)

578.查询回答率最高的问题

从 survey_log 表中获得回答率最高的问题，survey_log 表包含这些列：id, action, question_id, answer_id, q_num, timestamp。

id 表示用户 id；action 有以下几种值：“show”，“answer”，“skip”；当 action 值为 “answer” 时 answer_id 非空，而 action 值为 “show” 或者 “skip” 时 answer_id 为空；q_num 表示当前会话中问题的编号。

请编写 SQL 查询来找到具有最高回答率的问题。

select question_id
from (
    select question_id,
            sum(if(action='answer',1,0)) as ans_num,
        sum(if(action='show',1,0)) as cout_num
    from survey_log
    group by question_id
) as temp
order by ans_num / cout_num desc
limit 1;

579.查询员工的累计薪水

Employee 表保存了一年内的薪水信息。

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

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

SELECT
    temp.Id,
    temp.Month,
    sum( temp.Salary ) over ( PARTITION BY temp.Id ORDER BY temp.Month ) AS salary
FROM
    ( SELECT Id, Month, Salary, DENSE_RANK() over ( PARTITION BY Id ORDER BY Month DESC ) AS rnk FROM employee_579 ) AS temp
WHERE
    temp.rnk > 1
ORDER BY
    temp.Id,
    temp.Month DESC;

with tmp as(
select
    *,
    rank() over(partition by id order by month desc) as rn
from Employee
)
select id,month,
    sum(salary) over(partition by id order by month range between 2 preceding and current row) as salary
 from tmp
 where rn != 1
 order by id ,month desc;

SELECT t.id, t.`month`, t.salary FROM
(
 SELECT id, `month`, 
 SUM(salary) over(PARTITION BY id ORDER BY `month` ROWS 2 PRECEDING) salary, -- 累加的总薪水
 rank() over(PARTITION BY id ORDER BY `month` DESC) ranks   -- 排名好去掉最近一个月
 FROM employee
) t
WHERE t.ranks > 1

580.统计各专业学生人数

题目描述
表: Student

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| student_name | varchar |
| gender       | varchar |
| dept_id      | int     |
+--------------+---------+
student_id 是该表的主键（具有唯一值的列）。
dept_id是Department表中dept_id的外键。
该表的每一行都表示学生的姓名、性别和所属系的id。
 

表: Department

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| dept_id     | int     |
| dept_name   | varchar |
+-------------+---------+
dept_id是该表的主键（具有唯一值的列）。
该表的每一行包含一个部门的id和名称。
 

编写解决方案，为 Department 表中的所有部门(甚至是没有当前学生的部门)报告各自的部门名称和每个部门的学生人数。

按 student_number 降序 返回结果表。如果是平局，则按 dept_name 的  字母顺序 排序。

# Write your MySQL query statement below
SELECT dept_name, COUNT(student_id) AS student_number
FROM
    Department
    LEFT JOIN Student USING (dept_id)
GROUP BY dept_id
ORDER BY 2 DESC, 1;

1	count(null)=0，所以不用判断

585.2016年的投资

Insurance 表：

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| pid         | int   |
| tiv_2015    | float |
| tiv_2016    | float |
| lat         | float |
| lon         | float |
+-------------+-------+
pid 是这张表的主键(具有唯一值的列)。
表中的每一行都包含一条保险信息，其中：
pid 是投保人的投保编号。
tiv_2015 是该投保人在 2015 年的总投保金额，tiv_2016 是该投保人在 2016 年的总投保金额。
lat 是投保人所在城市的纬度。题目数据确保 lat 不为空。
lon 是投保人所在城市的经度。题目数据确保 lon 不为空。
 

编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和：

他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
他所在的城市必须与其他投保人都不同（也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同）。
tiv_2016 四舍五入的 两位小数 。

select round(sum(tiv_2016),2) tiv_2016 
from
(select e1.pid pid,e1.tiv_2016
from Insurance e1,Insurance e2
where e1.tiv_2015=e2.tiv_2015 and e1.pid != e2.pid
group by e1.pid
having count(*)>0
) t1
where pid not in
(select e1.pid
from Insurance e1,Insurance e2
where e1.lat =e2.lat and e1.lon =e2.lon and e1.pid != e2.pid)

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM Insurance
WHERE tiv_2015 IN (
    SELECT tiv_2015
    FROM Insurance
    GROUP BY tiv_2015
    HAVING COUNT(*) > 1
)
AND (lat, lon) NOT IN (
    SELECT lat, lon
    FROM Insurance
    GROUP BY lat, lon
    HAVING COUNT(*) > 1
)

597.好友申请1:总体通过率

表：FriendRequest

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| sender_id      | int     |
| send_to_id     | int     |
| request_date   | date    |
+----------------+---------+
该表可能包含重复项（换句话说，在SQL中，该表没有主键）。
该表包含发送请求的用户的 ID ，接受请求的用户的 ID 以及请求的日期。
 

表：RequestAccepted

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| requester_id   | int     |
| accepter_id    | int     |
| accept_date    | date    |
+----------------+---------+
该表可能包含重复项（换句话说，在SQL中，该表没有主键）。
该表包含发送请求的用户的 ID ，接受请求的用户的 ID 以及请求通过的日期。
 

求出好友申请的通过率，用 2 位小数表示。通过率由接受好友申请的数目除以申请总数。

提示：

通过的好友申请不一定都在表 friend_request 中。你只需要统计总的被通过的申请数（不管它们在不在表 FriendRequest 中），并将它除以申请总数，得到通过率
一个好友申请发送者有可能会给接受者发几条好友申请，也有可能一个好友申请会被通过好几次。这种情况下，重复的好友申请只统计一次。
如果一个好友申请都没有，你应该返回 accept_rate 为 0.00 。

# Write your MySQL query statement below
SELECT
    ROUND(
        IFNULL(
            (
                SELECT COUNT(DISTINCT requester_id, accepter_id)
                FROM RequestAccepted
            ) / (SELECT COUNT(DISTINCT sender_id, send_to_id) FROM FriendRequest),
            0
        ),
        2
    ) AS accept_rate;

601.体育馆的人流量 lag() lead() leaset()

with people as
(
    select id, visit_date, people,
    Lag(people,2) over(order by id) as pprvPeople,
    Lag(people,1) over(order by id) as prvPeople,
    Lead(people,1) over(order by id) as nextPeople,
    Lead(people,2) over(order by id) as nnextPeople
    from stadium
)
select id, visit_date, people from people
where 
(people >= 100 and prvPeople>=100 and pprvPeople>=100) ||
(people >= 100 and nextPeople>=100 and nnextPeople>=100) ||
(people >= 100 and nextPeople>=100 and prvPeople>=100)

select distinct s1.*
from stadium s1, stadium s2, stadium s3
where s1.people >= 100 and s2.people>= 100 and s3.people >= 100
and
(
    (s1.id - s2.id = 1 and s2.id - s3.id =1)
   or
   (s2.id - s1.id = 1 and s1.id - s3.id =1) 
   or
   (s3.id - s2.id = 1 and s2.id - s1.id = 1) 
) order by s1.id;

select id, visit_date, people
from 
(select id, visit_date, people,
lag(people, 1, 0) over(order by id) as previous1_num,
lag(people, 2, 0) over(order by id) as previous2_num,
lead(people, 1, 0) over(order by id) as next1_num,
lead(people, 2, 0) over(order by id) as next2_num
from Stadium) a
where least(a.people, a.previous1_num, a.previous2_num)>=100
or least(a.people, a.next1_num, a.next2_num)>=100
or least(a.people, a.next1_num, a.previous1_num)>=100

602 case when

select case when requester_id is not null then requester_id else accepter_id end  as id, if(c1 is null,0,c1)+if(c2 is null,0,c2) as num
from
(select requester_id,count(*) c1
from RequestAccepted 
group by requester_id) t1 
left join 
(select accepter_id,count(*) c2 
from RequestAccepted 
group by accepter_id) t2
on t1.requester_id=t2.accepter_id
order by num desc limit 1

603.连续空余座位 lag lead ROWS BETWEEN AND

表: Cinema

+-------------+------+
| Column Name | Type |
+-------------+------+
| seat_id     | int  |
| free        | bool |
+-------------+------+
Seat_id 是该表的自动递增主键列。
在 PostgreSQL 中，free 存储为整数。请使用 ::boolean 将其转换为布尔格式。
该表的每一行表示第 i 个座位是否空闲。1 表示空闲，0 表示被占用。
 

查找电影院所有连续可用的座位。

返回按 seat_id 升序排序 的结果表。

测试用例的生成使得两个以上的座位连续可用。

# Write your MySQL query statement below
WITH
    T AS (
        SELECT
            seat_id,
            (free + (LAG(free) OVER (ORDER BY seat_id))) AS a,
            (free + (LEAD(free) OVER (ORDER BY seat_id))) AS b
        FROM Cinema
    )
SELECT seat_id
FROM T
WHERE a = 2 OR b = 2;

# Write your MySQL query statement below
WITH
    T AS (
        SELECT
            *,
            SUM(free = 1) OVER (
                ORDER BY seat_id
                ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
            ) AS cnt
        FROM Cinema
    )
SELECT seat_id
FROM T
WHERE free = 1 AND cnt > 1
ORDER BY 1;

608 union

# Write your MySQL query statement below
select id,'Root' as type
from Tree
where p_id is null
union
select id,'Inner' as type
from Tree
where p_id is not null and id not in(
select t2.id
from Tree t1 
right join Tree t2
on t1.p_id=t2.id
where t1.id is null and t2.id is not null and t2.p_id is not null)
union
select t2.id, 'Leaf' as type
from Tree t1 
right join Tree t2
on t1.p_id=t2.id
where t1.id is null and t2.id is not null and t2.p_id is not null

610.判断三角形

表: Triangle

+-------------+------+
| Column Name | Type |
+-------------+------+
| x           | int  |
| y           | int  |
| z           | int  |
+-------------+------+
在 SQL 中，(x, y, z)是该表的主键列。
该表的每一行包含三个线段的长度。
 

对每三个线段报告它们是否可以形成一个三角形。

# Write your MySQL query statement below

select x,y,z,case when x+y>z and x+z>y and y+z>x then 'Yes' else 'No' end as triangle
from Triangle

614.二级关注者

表：Follow

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| followee    | varchar |
| follower    | varchar |
+-------------+---------+
(followee, follower) 是该表的主键(具有唯一值的列的组合)。
该表的每一行表示关注者关注了社交网络上的被关注者。
不会有用户关注他们自己。
 

二级关注者 是指满足以下条件的用户:

关注至少一个用户，
被至少一个用户关注。
编写一个解决方案来报告 二级用户 及其关注者的数量。

返回按 follower 字典序排序 的结果表。

select followee follower,count(*) num
from Follow f1
where f1.follower in 
(
select distint f2.follower
from Follow f2
)
group by followee

615.平均工资:部门与公司比较

表：Salary

+-------------+------+
| 列名        | 类型 |
+-------------+------+
| id          | int  |
| employee_id | int  |
| amount      | int  |
| pay_date    | date |
+-------------+------+
在 SQL 中，id 是该表的主键列。
该表的每一行表示一个员工一个月的薪资。
employee_id 是来自 Employee 表的外键（reference 列）。
 

表： Employee

+---------------+------+
| 列名          | 类型 |
+---------------+------+
| employee_id   | int  |
| department_id | int  |
+---------------+------+
在 SQL 中，employee_id 是该表的主键列。
该表的每一行表示一个员工所属的部门。
 

找出各个部门员工的平均薪资与公司平均薪资之间的比较结果（更高 / 更低 / 相同）。每月每个部门的平均工资和每月公司平均工资比较

以 任意顺序 返回结果表。

1	好像两个group by 两个select 再连接就可以了吧

618.学生地理信息报告行转列

表： student 

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| name        | varchar |
| continent   | varchar |
+-------------+---------+
该表可能包含重复的行。
该表的每一行表示学生的名字和他们来自的大陆。
 

一所学校有来自亚洲、欧洲和美洲的学生。

编写解决方案实现对大洲（continent）列的 透视表 操作，使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲（America）、亚洲（Asia）和欧洲（Europe）。

测试用例的生成保证来自美国的学生人数不少于亚洲或欧洲的学生人数。

SELECT
    IF(continent = 'America', name, NULL) AS 'America',
    IF(continent = 'Asia', name, NULL) AS 'Asia',
    IF(continent = 'Europe', name, NULL) AS 'Europe'
FROM student

要排序的话提前对student里面的name字段排序就可以了

626.换座位换序号

表: Seat
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| student     | varchar |
+-------------+---------+
id 是该表的主键（唯一值）列。
该表的每一行都表示学生的姓名和 ID。
ID 序列始终从 1 开始并连续增加。

编写解决方案来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。
按 id 升序 返回结果表。
查询结果格式如下所示。

示例 1:
输入: 
Seat 表:
+----+---------+
| id | student |
+----+---------+
| 1  | Abbot   |
| 2  | Doris   |
| 3  | Emerson |
| 4  | Green   |
| 5  | Jeames  |
+----+---------+
输出: 
+----+---------+
| id | student |
+----+---------+
| 1  | Doris   |
| 2  | Abbot   |
| 3  | Green   |
| 4  | Emerson |
| 5  | Jeames  |
+----+---------+
解释:
请注意，如果学生人数为奇数，则不需要更换最后一名学生的座位。

select if(id=count(*)+1,id-1,id) id,student
from 
(select id-1 id,student
from student
where id%2=0)
union
(select id+1 id,student
from student 
where id%2=1) u
order by id

627.变更性别 update replace

Salary 表：

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| name        | varchar  |
| sex         | ENUM     |
| salary      | int      |
+-------------+----------+
id 是这个表的主键（具有唯一值的列）。
sex 这一列的值是 ENUM 类型，只能从 ('m', 'f') 中取。
本表包含公司雇员的信息。
 

请你编写一个解决方案来交换所有的 'f' 和 'm' （即，将所有 'f' 变为 'm' ，反之亦然），仅使用 单个 update 语句 ，且不产生中间临时表。

注意，你必须仅使用一条 update 语句，且 不能 使用 select 语句。

# 1.
update salary
set sex = (
    case sex when 'm' then 'f' else 'm' end
);

# 2.
update salary set sex = if(sex='m','f','m');

# 3.
update salary set sex = char(ascii('m') + ascii('f') - ascii(sex));

update Salary
set sex = replace("mf", sex, "");

相当于把"mf"中与sex相同的换为空字符串，也就是删除了，剩下的是对立的性别

1070.产品销售分析川

select product_id,year as first_year,quantity,price
from(
select product_id,year,quantity,price,ROW_NUMBER() over(partition by product_id order by year) as r
from Sales
) t
where t.r=1

1083.销售分析 | sum(表达式)

表：Product
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
| unit_price   | int     |
+--------------+---------+
Product_id 是该表的主键(具有唯一值的列)。
该表的每一行表示每种产品的名称和价格。
表：Sales

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+------ ------+---------+
这个表可能有重复的行。
product_id 是 Product 表的外键(reference 列)。
buyer_id 永远不会是 NULL。
sale_date 永远不会是 NULL。
该表的每一行都包含一次销售的一些信息。
 

编写一个解决方案，报告那些买了 S8 而没有买 iPhone 的 买家。注意，S8 和 iPhone 是 Product 表中显示的产品。

# Write your MySQL query statement below
SELECT buyer_id
FROM
    Sales
    JOIN Product USING (product_id)
GROUP BY 1
HAVING SUM(product_name = 'S8') > 0 AND SUM(product_name = 'iPhone') = 0;

1097.游戏玩法分析 V MIN() OVER (PARTITION BY

表：Activity 

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
（player_id，event_date）是此表的主键(具有唯一值的列的组合)
这张表显示了某些游戏的玩家的活动情况
每一行表示一个玩家的记录，在某一天使用某个设备注销之前，登录并玩了很多游戏（可能是 0）
 

玩家的 安装日期 定义为该玩家的第一个登录日。

我们将日期 x 的 第一天留存率 定义为：假定安装日期为 X 的玩家的数量为 N ，其中在 X 之后的一天重新登录的玩家数量为 M，M/N 就是第一天留存率，四舍五入到小数点后两位。

编写解决方案，报告所有安装日期、当天安装游戏的玩家数量和玩家的 第一天留存率。

以 任意顺序 返回结果表。

with t as(
select player_id,min(event_date)
from Activity
group by player_id
)

select t.event_date install_dt,count(*) installs,count(a.player_id)/ count(*) Day1_retention 
from t left join Activity a
on t.player_id = a.player_id and DATEDIFF(t.event_date,a.event_date)=1
group by t.event_date

# Write your MySQL query statement below
WITH
    T AS (
        SELECT
            player_id,
            event_date,
            MIN(event_date) OVER (PARTITION BY player_id) AS install_dt
        FROM Activity
    )
SELECT
    install_dt,
    COUNT(DISTINCT player_id) AS installs,
    ROUND(
        SUM(DATEDIFF(event_date, install_dt) = 1) / COUNT(DISTINCT player_id),
        2
    ) AS day1_retention
FROM T
GROUP BY 2;

1098.小众书籍

书籍表 Books：

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| book_id        | int     |
| name           | varchar |
| available_from | date    |
+----------------+---------+
book_id 是这个表的主键（具有唯一值的列）。
订单表 Orders：

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| order_id       | int     |
| book_id        | int     |
| quantity       | int     |
| dispatch_date  | date    |
+----------------+---------+
order_id 是这个表的主键（具有唯一值的列）。
book_id  是 Books 表的外键（reference 列）。
 

编写解决方案，筛选出过去一年中订单总量 少于 10 本 的 书籍，并且 不考虑 上架距今销售 不满一个月 的书籍 。假设今天是 2019-06-23 。

返回结果表 无顺序要求 。

# Write your MySQL query statement below
SELECT book_id, name
FROM
    Books
    LEFT JOIN Orders USING (book_id)
WHERE available_from < '2019-05-23'
GROUP BY 1
HAVING SUM(IF(dispatch_date >= '2018-06-23', quantity, 0)) < 10;

1107.每日新用户统计 MIN() OVER (PARTITION BY

Traffic 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| activity      | enum    |
| activity_date | date    |
+---------------+---------+
该表可能有重复的行。
activity 列是 ENUM 类型，可能取 ('login', 'logout', 'jobs', 'groups', 'homepage') 几个值之一。

 

编写解决方案，找出从今天起最多 90 天内，每个日期该日期首次登录的用户数。假设今天是 2019-06-30 。

以 任意顺序 返回结果表。

with t as(
select user_id,min(activity_date) time
from Traffic
where DATEDIFF('2019-06-30', activity_date) <= 90 and activity='login'
group by user_id
)

select time login_date,count(*) user_count  
from t
group by time

# Write your MySQL query statement below
WITH
    T AS (
        SELECT
            user_id,
            MIN(activity_date) OVER (PARTITION BY user_id) AS login_date
        FROM Traffic
        WHERE activity = 'login'
    )
SELECT login_date, COUNT(DISTINCT user_id) AS user_count
FROM T
WHERE DATEDIFF('2019-06-30', login_date) <= 90
GROUP BY 2;

1127.用户购买平台

支出表: Spending

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| spend_date  | date    |
| platform    | enum    | 
| amount      | int     |
+-------------+---------+
这张表记录了用户在一个在线购物网站的支出历史，该在线购物平台同时拥有桌面端（'desktop'）和手机端（'mobile'）的应用程序。
(user_id, spend_date, platform) 是这张表的主键(具有唯一值的列的组合)。
平台列 platform 是一种 ENUM ，类型为（'desktop', 'mobile'）。
 

编写解决方案找出每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额。

以 任意顺序 返回结果表。

示例 1：

输入：
Spending table:
+---------+------------+----------+--------+
| user_id | spend_date | platform | amount |
+---------+------------+----------+--------+
| 1       | 2019-07-01 | mobile   | 100    |
| 1       | 2019-07-01 | desktop  | 100    |
| 2       | 2019-07-01 | mobile   | 100    |
| 2       | 2019-07-02 | mobile   | 100    |
| 3       | 2019-07-01 | desktop  | 100    |
| 3       | 2019-07-02 | desktop  | 100    |
+---------+------------+----------+--------+
输出：
+------------+----------+--------------+-------------+
| spend_date | platform | total_amount | total_users |
+------------+----------+--------------+-------------+
| 2019-07-01 | desktop  | 100          | 1           |
| 2019-07-01 | mobile   | 100          | 1           |
| 2019-07-01 | both     | 200          | 1           |
| 2019-07-02 | desktop  | 100          | 1           |
| 2019-07-02 | mobile   | 100          | 1           |
| 2019-07-02 | both     | 0            | 0           |
+------------+----------+--------------+-------------+

select spend_date,platform, sum(amount) as total_amount, count(user_id) total_users
from
(select spend_date, user_id, 
(case count(distinct platform)
    when 1 then platform
    when 2 then 'both'
    end
) as  platform, sum(amount) as amount
from Spending
group by spend_date, user_id
) as temp2
group by spend_date, platform

但是这样得出来的只有某些spend_date某些user_id
的，题目要求是不存在也要放在结果里面,只是结果置0而已。

# Write your MySQL query statement below
WITH
    P AS (
        SELECT DISTINCT spend_date, 'desktop' AS platform FROM Spending
        UNION
        SELECT DISTINCT spend_date, 'mobile' FROM Spending
        UNION
        SELECT DISTINCT spend_date, 'both' FROM Spending
    ),
    T AS (
        SELECT
            user_id,
            spend_date,
            SUM(amount) AS amount,
            IF(COUNT(platform) = 1, platform, 'both') AS platform
        FROM Spending
        GROUP BY 1, 2
    )
SELECT
    p.*,
    IFNULL(SUM(amount), 0) AS total_amount,
    COUNT(t.user_id) AS total_users
FROM
    P AS p
    LEFT JOIN T AS t USING (spend_date, platform)
GROUP BY 1, 2;

1159.市场分析 I I

表: Users
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| join_date      | date    |
| favorite_brand | varchar |
+----------------+---------+
user_id 是该表的主键(具有唯一值的列)。
表中包含一位在线购物网站用户的个人信息，用户可以在该网站出售和购买商品。
表: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| item_id       | int     |
| buyer_id      | int     |
| seller_id     | int     |
+---------------+---------+
order_id 是该表的主键(具有唯一值的列)。
item_id 是 Items 表的外键(reference 列)。
buyer_id 和 seller_id 是 Users 表的外键。
表: Items

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| item_id       | int     |
| item_brand    | varchar |
+---------------+---------+
item_id 是该表的主键(具有唯一值的列)。

编写一个解决方案，为每个用户找出他们出售的第二件商品(按日期)的品牌是否是他们最喜欢的品牌。如果用户售出的商品少于两件，则该用户的结果为否。保证卖家不会在一天内卖出一件以上的商品。

以 任意顺序 返回结果表。
返回结果格式如下例所示：

# Write your MySQL query statement below
SELECT
    u.user_id AS seller_id,
    CASE
        WHEN u.favorite_brand = i.item_brand THEN 'yes'
        ELSE 'no'
    END AS 2nd_item_fav_brand
FROM
    users AS u
    LEFT JOIN (
        SELECT
            order_date,
            item_id,
            seller_id,
            RANK() OVER (
                PARTITION BY seller_id
                ORDER BY order_date
            ) AS rk
        FROM orders
    ) AS o
        ON u.user_id = o.seller_id AND o.rk = 2
    LEFT JOIN items AS i ON o.item_id = i.item_id;

1174.即时食物配送Ⅱ

配送表: Delivery

+-----------------------------+---------+
| Column Name                 | Type    |
+-----------------------------+---------+
| delivery_id                 | int     |
| customer_id                 | int     |
| order_date                  | date    |
| customer_pref_delivery_date | date    |
+-----------------------------+---------+
delivery_id 是该表中具有唯一值的列。
该表保存着顾客的食物配送信息，顾客在某个日期下了订单，并指定了一个期望的配送日期（和下单日期相同或者在那之后）。
 

如果顾客期望的配送日期和下单日期相同，则该订单称为 「即时订单」，否则称为「计划订单」。

「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。

编写解决方案以获取即时订单在所有用户的首次订单中的比例。保留两位小数。

with t as
(select customer_id,min(order_date)
from Delivery
group by customer_id
)

select round(count(d.customer_id)/count(t.customer_id)*100,2)
from t left join Delivery d
on t.customer_id = d.customer_id and t.order_date = d.order_date and d.order_date = d.customer_pref_delivery_date

WITH FirstOrders AS (
    SELECT
        customer_id,
        order_date,
        customer_pref_delivery_date,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
    FROM
        Delivery
)
SELECT
    ROUND(
        SUM(CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
        2
    ) AS immediate_percentage
FROM
    FirstOrders
WHERE
    rn = 1; -- 只考虑首次订单

select 
round(sum(order_date = customer_pref_delivery_date)/count(*)*100,2) immediate_percentage
from Delivery
where (customer_id,order_date) in 
(select customer_id,min(order_date) from Delivery group by customer_id)

1179.重新格式化部门表行转列

表 Department：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| revenue       | int     |
| month         | varchar |
+---------------+---------+
在 SQL 中，(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。
 

重新格式化表格，使得 每个月 都有一个部门 id 列和一个收入列。

以 任意顺序 返回结果表。

结果格式如以下示例所示。

 

示例 1：

输入：
Department table:
+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 9000    | Jan   |
| 3    | 10000   | Feb   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+------+---------+-------+
输出：
+------+-------------+-------------+-------------+-----+-------------+
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1    | 8000        | 7000        | 6000        | ... | null        |
| 2    | 9000        | null        | null        | ... | null        |
| 3    | null        | 10000       | null        | ... | null        |
+------+-------------+-------------+-------------+-----+-------------+

SELECT 
    id,
    SUM(IF(month = 'Jan', revenue, NULL)) AS Jan_Revenue,
    SUM(IF(month = 'Feb', revenue, NULL)) AS Feb_Revenue,
    SUM(IF(month = 'Mar', revenue, NULL)) AS Mar_Revenue,
    SUM(IF(month = 'Apr', revenue, NULL)) AS Apr_Revenue,
    SUM(IF(month = 'May', revenue, NULL)) AS May_Revenue,
    SUM(IF(month = 'Jun', revenue, NULL)) AS Jun_Revenue,
    SUM(IF(month = 'Jul', revenue, NULL)) AS Jul_Revenue,
    SUM(IF(month = 'Aug', revenue, NULL)) AS Aug_Revenue,
    SUM(IF(month = 'Sep', revenue, NULL)) AS Sep_Revenue,
    SUM(IF(month = 'Oct', revenue, NULL)) AS Oct_Revenue,
    SUM(IF(month = 'Nov', revenue, NULL)) AS Nov_Revenue,
    SUM(IF(month = 'Dec', revenue, NULL)) AS Dec_Revenue
FROM Department
GROUP BY id

select
id
,sum(case when month = 'Jan' then revenue else null end) Jan_Revenue
,sum(case when month = 'Feb' then revenue else null end) Feb_Revenue
,sum(case when month = 'Mar' then revenue else null end) Mar_Revenue
,sum(case when month = 'Apr' then revenue else null end) Apr_Revenue
,sum(case when month = 'May' then revenue else null end) May_Revenue
,sum(case when month = 'Jun' then revenue else null end) Jun_Revenue
,sum(case when month = 'Jul' then revenue else null end) Jul_Revenue
,sum(case when month = 'Aug' then revenue else null end) Aug_Revenue
,sum(case when month = 'Sep' then revenue else null end) Sep_Revenue
,sum(case when month = 'Oct' then revenue else null end) Oct_Revenue
,sum(case when month = 'Nov' then revenue else null end) Nov_Revenue
,sum(case when month = 'Dec' then revenue else null end) Dec_Revenue
from Department
group by 1

1193.每月交易| date_format(),left(),sub_string()

表：Transactions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| country       | varchar |
| state         | enum    |
| amount        | int     |
| trans_date    | date    |
+---------------+---------+
id 是这个表的主键。
该表包含有关传入事务的信息。
state 列类型为 ["approved", "declined"] 之一。
 

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

以 任意顺序 返回结果表。

# Write your MySQL query statement below
select 
date_format(trans_date,'%Y-%m') as month,
country,
count(*) as trans_count,
sum(if(state='approved',1,0)) as approved_count,
sum(amount) as trans_total_amount,
sum(if(state='approved',amount,0)) as approved_total_amount
from Transactions
group by month,country

select left(T.trans_date,7) month,
t.country,count(t.state) trans_count ,
sum(if(t.state='approved',1,0)) approved_count,
sum(t.amount) trans_total_amount ,
sum(if(t.state='approved',t.amount,0)) approved_total_amount
from Transactions T
group by left(T.trans_date,7),t.country;

1194.锦标赛优胜者列转行

Players 玩家表
+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| player_id   | int   |
| group_id    | int   |
+-------------+-------+
player_id 是此表的主键(具有唯一值的列)。
此表的每一行表示每个玩家的组。
Matches 赛事表

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| first_player  | int     |
| second_player | int     | 
| first_score   | int     |
| second_score  | int     |
+---------------+---------+
match_id 是此表的主键(具有唯一值的列)。
每一行是一场比赛的记录，first_player 和 second_player 表示该场比赛的球员 ID。
first_score 和 second_score 分别表示 first_player 和 second_player 的得分。
你可以假设，在每一场比赛中，球员都属于同一组。

每组的获胜者是在组内累积得分最高的选手。如果平局，player_id 最小 的选手获胜。
编写解决方案来查找每组中的获胜者。
返回的结果表单 没有顺序要求 。

with t as(
select player,sum(score)scoreall
from
(select first_player player,first_score score
from Matches
union all
select second_player player,second_score score
from Matches) a
group by player
)

with s as(
select group_id,max(scoreall) maxscore
from t,Players 
where t.player = Players.player_id
group by group_id
)

select s.group_id,min(player_id ) player_id  
from s,
(select group_id,t.player player,scoreall
from t,Players 
where t.player = Players.player_id) r
where s.group_id = r.group.id and s.maxscore = r.scoreall
group by s.group_id

# Write your MySQL query statement below
WITH
    s AS (
        SELECT first_player AS player_id, first_score AS score, group_id
        FROM
            Matches AS m
            JOIN Players AS p ON m.first_player = p.player_id
        UNION ALL
        SELECT second_player AS player_id, second_score AS score, group_id
        FROM
            Matches AS m
            JOIN Players AS p ON m.second_player = p.player_id
    ),
    t AS (
        SELECT group_id, player_id, SUM(score) AS scores
        FROM s
        GROUP BY player_id
    ),
    p AS (
        SELECT
            group_id,
            player_id,
            RANK() OVER (
                PARTITION BY group_id
                ORDER BY scores DESC, player_id
            ) AS rk
        FROM t
    )
SELECT group_id, player_id
FROM p
WHERE rk = 1;

1204.最后一个能进入巴士的人

表: Queue
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| person_id   | int     |
| person_name | varchar |
| weight      | int     |
| turn        | int     |
+-------------+---------+
person_id 是这个表具有唯一值的列。
该表展示了所有候车乘客的信息。
表中 person_id 和 turn 列将包含从 1 到 n 的所有数字，其中 n 是表中的行数。
turn 决定了候车乘客上巴士的顺序，其中 turn=1 表示第一个上巴士，turn=n 表示最后一个上巴士。
weight 表示候车乘客的体重，以千克为单位。
 

有一队乘客在等着上巴士。然而，巴士有1000  千克 的重量限制，所以其中一部分乘客可能无法上巴士。
编写解决方案找出 最后一个 上巴士且不超过重量限制的乘客，并报告 person_name 。题目测试用例确保顺位第一的人可以上巴士且不会超重。

# Write your MySQL query statement below
WITH
    T AS (
        SELECT
            person_name,
            SUM(weight) OVER (ORDER BY turn) AS s
        FROM Queue
    )
SELECT person_name
FROM T
WHERE s <= 1000
ORDER BY s DESC
LIMIT 1;

# Write your MySQL query statement below
SELECT a.person_name
FROM
    Queue AS a,
    Queue AS b
WHERE a.turn >= b.turn
GROUP BY a.person_id
HAVING SUM(b.weight) <= 1000
ORDER BY a.turn DESC
LIMIT 1;

1205.每月交易 | count(1),count(null),sum(0)

Transactions 记录表
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| id             | int     |
| country        | varchar |
| state          | enum    |
| amount         | int     |
| trans_date     | date    |
+----------------+---------+
id 是这个表的主键。
该表包含有关传入事务的信息。
状态列是枚举类型，值为 [approved、declined] 其中之一的列。
Chargebacks 表

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| trans_id       | int     |
| trans_date     | date    |
+----------------+---------+
退单包含有关放置在事务表中的某些事务的传入退单的基本信息。
trans_id 是 transactions 表的 id 列的外键（reference 列）。
每项退单都对应于之前进行的交易，即使未经批准。
 
编写一个解决方案，找出每个国家/地区的每月交易信息：已批准交易的数量及其总金额、退单的数量及其总金额。

注意：在你的解决方案中，只需显示给定月份和国家，忽略所有为零的行。
以 任意顺序 返回结果表。
结果格式如下所示。

示例 1:
输入：
Transactions 表：
+-----+---------+----------+--------+------------+
| id  | country | state    | amount | trans_date |
+-----+---------+----------+--------+------------+
| 101 | US      | approved | 1000   | 2019-05-18 |
| 102 | US      | declined | 2000   | 2019-05-19 |
| 103 | US      | approved | 3000   | 2019-06-10 |
| 104 | US      | declined | 4000   | 2019-06-13 |
| 105 | US      | approved | 5000   | 2019-06-15 |
+-----+---------+----------+--------+------------+
Chargebacks 表：
+----------+------------+
| trans_id | trans_date |
+----------+------------+
| 102      | 2019-05-29 |
| 101      | 2019-06-30 |
| 105      | 2019-09-18 |
+----------+------------+
输出：
+---------+---------+----------------+-----------------+------------------+-------------------+
| month   | country | approved_count | approved_amount | chargeback_count | chargeback_amount |
+---------+---------+----------------+-----------------+------------------+-------------------+
| 2019-05 | US      | 1              | 1000            | 1                | 2000              |
| 2019-06 | US      | 2              | 8000            | 1                | 1000              |
| 2019-09 | US      | 0              | 0               | 1                | 5000              |
+---------+---------+----------------+-----------------+------------------+-------------------+

select month, country,
count(case when state = 'approved' and tag=0 then 1 else null end) as approved_count,
sum(case when state = 'approved' and tag =0 then amount else 0 end)as approved_amount,
count(case when tag =1 then 1 else null end)as chargeback_count,
sum(case when tag =1 then amount else 0 end)as chargeback_amount
from 
(
-- 每个月和每个国家/地区的已批准交易的数量
select id,country,state,amount,date_format(trans_date ,"%Y-%m")as month,0 as tag
from Transactions
where state = 'approved'
union all
-- 每个月和每个国家/地区的退单的数量
select id,country,state,amount,date_format(c.trans_date ,"%Y-%m")as month,1 as tag
from Transactions as t inner join Chargebacks as c 
on t.id = c.trans_id
)temp
group by month,country

select
    trans_date,
    country,
    state,
    amount
from Transactions
where state = 'approved'
union all
select
    a.trans_date,
    b.country,
    'declined' state,
    b.amount
from Chargebacks a
inner join Transactions b
on a.trans_id = b.id
)
select
    date_format(trans_date,'%Y-%m') month,
    country,
    count(case when state = 'approved' then 1 else null end) approved_count,
    sum(case when state = 'approved' then amount else 0 end) approved_amount,
    count(case when state = 'declined' then 1 else null end) chargeback_count,
    sum(case when state = 'declined' then amount else 0 end) chargeback_amount
from tmp
group by date_format(trans_date,'%Y-%m'),country;

# Write your MySQL query statement below
WITH
    T AS (
        SELECT * FROM Transactions
        UNION
        SELECT id, country, 'chargeback', amount, c.trans_date
        FROM
            Transactions AS t
            JOIN Chargebacks AS c ON t.id = c.trans_id
    )
SELECT
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    SUM(state = 'approved') AS approved_count,
    SUM(IF(state = 'approved', amount, 0)) AS approved_amount,
    SUM(state = 'chargeback') AS chargeback_count,
    SUM(IF(state = 'chargeback', amount, 0)) AS chargeback_amount
FROM T
GROUP BY 1, 2
HAVING approved_amount OR chargeback_amount;

1212.查询球队积分列转行

表: Teams
+---------------+----------+
| Column Name   | Type     |
+---------------+----------+
| team_id       | int      |
| team_name     | varchar  |
+---------------+----------+
team_id 是该表具有唯一值的列。
表中的每一行都代表一支独立足球队。
 
表: Matches
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| host_team     | int     |
| guest_team    | int     | 
| host_goals    | int     |
| guest_goals   | int     |
+---------------+---------+
match_id 是该表具有唯一值的列。
表中的每一行都代表一场已结束的比赛。
比赛的主客队分别由它们自己的 id 表示，他们的进球由 host_goals 和 guest_goals 分别表示。

你希望在所有比赛之后计算所有球队的比分。积分奖励方式如下:

如果球队赢了比赛(即比对手进更多的球)，就得 3 分。
如果双方打成平手(即，与对方得分相同)，则得 1 分。
如果球队输掉了比赛(例如，比对手少进球)，就 不得分 。
编写解决方案，以找出每个队的 team_id，team_name 和 num_points。

返回的结果根据 num_points 降序排序，如果有两队积分相同，那么这两队按 team_id  升序排序。
返回结果格式如下。

with t as(
select host_team,case when host_goals>guest_goals then 3 when host_goals=guest_goals then 1 when host_goals<guest_goals then 0 END host_team_score,guest_team,case when host_goals<guest_goals then 3 when host_goals=guest_goals then 1 when host_goals>guest_goals then 0 END guest_team_score
from Matches
)

select team_id,team_name,sum(num_points) num_points
from
(select host_team team_id,host_team_score num_points
from t
union all
select guest_team team_id,guest_team_score num_points
from t) ut,Teams
where ut.team_id = Teams.team_id
group by ut.team_id
order by num_points DESC,team_id

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

表：Failed
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| fail_date    | date    |
+--------------+---------+
该表主键为 fail_date (具有唯一值的列)。
该表包含失败任务的天数.

表： Succeeded
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| success_date | date    |
+--------------+---------+
该表主键为 success_date (具有唯一值的列)。
该表包含成功任务的天数.

系统 每天 运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。

编写解决方案找出 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期（start_date 和 end_date）。即如果任务失败了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。

最后结果按照起始日期 start_date 排序
输入：
Failed table:
+-------------------+
| fail_date         |
+-------------------+
| 2018-12-28        |
| 2018-12-29        |
| 2019-01-04        |
| 2019-01-05        |
+-------------------+
Succeeded table:
+-------------------+
| success_date      |
+-------------------+
| 2018-12-30        |
| 2018-12-31        |
| 2019-01-01        |
| 2019-01-02        |
| 2019-01-03        |
| 2019-01-06        |
+-------------------+
输出：
+--------------+--------------+--------------+
| period_state | start_date   | end_date     |
+--------------+--------------+--------------+
| succeeded    | 2019-01-01   | 2019-01-03   |
| failed       | 2019-01-04   | 2019-01-05   |
| succeeded    | 2019-01-06   | 2019-01-06   |
+--------------+--------------+--------------+

select state as period_state,   min(dt ) as start_date,    max(dt) as end_date
from
(
    select *,rank()over(partition by state order by  dt ) as rk ,subdate(dt,rank()over(partition by state order by  dt)) as dif
    from 
    (
        select 'succeeded' as 'state',success_date as 'dt'
        from Succeeded where success_date between '2019-01-01' and '2019-12-31'
        union 
        select 'failed'as 'state',fail_date as 'dt'
        from Failed    where fail_date    between '2019-01-01' and '2019-12-31'
    )t1
)t2
group by state,dif
order by dt

# Write your MySQL query statement below
WITH
    T AS (
        SELECT fail_date AS dt, 'failed' AS st
        FROM Failed
        WHERE YEAR(fail_date) = 2019
        UNION ALL
        SELECT success_date AS dt, 'succeeded' AS st
        FROM Succeeded
        WHERE YEAR(success_date) = 2019
    )
SELECT
    st AS period_state,
    MIN(dt) AS start_date,
    MAX(dt) AS end_date
FROM
    (
        SELECT
            *,
            SUBDATE(
                dt,
                RANK() OVER (
                    PARTITION BY st
                    ORDER BY dt
                )
            ) AS pt
        FROM T
    ) AS t
GROUP BY 1, pt
ORDER BY 2;

1264.页面推荐

朋友关系列表： Friendship
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user1_id      | int     |
| user2_id      | int     |
+---------------+---------+
(user1_id, user2_id) 是这张表具有唯一值的列的组合。
这张表的每一行代表着 user1_id 和 user2_id 之间存在着朋友关系。
 

喜欢列表： Likes
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| page_id     | int     |
+-------------+---------+
(user_id, page_id) 是这张表具有唯一值的列的组合。
这张表的每一行代表着 user_id 喜欢 page_id。
 

编写解决方案，向user_id = 1 的用户，推荐其朋友们喜欢的页面。不要推荐该用户已经喜欢的页面。
以 任意顺序 返回结果，其中不应当包含重复项。

# Write your MySQL query statement below
WITH
    T AS (
        SELECT user1_id AS user_id FROM Friendship WHERE user2_id = 1
        UNION
        SELECT user2_id AS user_id FROM Friendship WHERE user1_id = 1
    )
SELECT DISTINCT page_id AS recommended_page
FROM
    T
    JOIN Likes USING (user_id)
WHERE page_id NOT IN (SELECT page_id FROM Likes WHERE user_id = 1);

with t as (
select case when user1_id=1 then user2_id case when user2_id=1 then user1_id end user_id
from Friendship
)

select page_id recommended_page 
from t,Likes
where t.user_id = Likes.user_id and Likes.page_id not in (select page_id from Likes where user_id=1)

1270.向公司 CEO 汇报工作的所有人难

员工表：Employees
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| employee_id   | int     |
| employee_name | varchar |
| manager_id    | int     |
+---------------+---------+
employee_id 是这个表具有唯一值的列。
这个表中每一行中，employee_id 表示职工的 ID，employee_name 表示职工的名字，manager_id 表示该职工汇报工作的直线经理。
这个公司 CEO 是 employee_id = 1 的人。

编写解决方案，找出所有直接或间接向公司 CEO 汇报工作的职工的 employee_id 。
由于公司规模较小，经理之间的间接关系 不超过 3 个经理 。
可以以 任何顺序 返回无重复项的结果。
返回结果示例如下。

示例 1：
输入：
Employees table:
+-------------+---------------+------------+
| employee_id | employee_name | manager_id |
+-------------+---------------+------------+
| 1           | Boss          | 1          |
| 3           | Alice         | 3          |
| 2           | Bob           | 1          |
| 4           | Daniel        | 2          |
| 7           | Luis          | 4          |
| 8           | Jhon          | 3          |
| 9           | Angela        | 8          |
| 77          | Robert        | 1          |
+-------------+---------------+------------+
输出：
+-------------+
| employee_id |
+-------------+
| 2           |
| 77          |
| 4           |
| 7           |
+-------------+

# Write your MySQL query statement below
SELECT e1.employee_id
FROM
    Employees AS e1
    JOIN Employees AS e2 ON e1.manager_id = e2.employee_id
    JOIN Employees AS e3 ON e2.manager_id = e3.employee_id
WHERE e1.employee_id != 1 AND e3.manager_id = 1;

with recursive temp as (
    select e.employee_id from Employees e where e.employee_id!=1 and manager_id=1
    union all 
    select e.employee_id from Employees e join temp t on t.employee_id=e.manager_id
)
select * from temp

1280.学生们参加各科测试的次数 ifnull 构造行

学生表: Students
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
+---------------+---------+
在 SQL 中，主键为 student_id（学生ID）。
该表内的每一行都记录有学校一名学生的信息。

科目表: Subjects
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+
在 SQL 中，主键为 subject_name（科目名称）。
每一行记录学校的一门科目名称。

考试表: Examinations
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| subject_name | varchar |
+--------------+---------+
这个表可能包含重复数据（换句话说，在 SQL 中，这个表没有主键）。
学生表里的一个学生修读科目表里的每一门科目。
这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。
 
查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。

with t as(
select e.student_id student_id,student_name,subject_name,count(*) attended_exams 
from Examinations e,Students s
where e.student_id = s.student_id 
group by e.student_id,subject_name 
)

with s as(
select student_id,student_name,subject_name 
from Students,Subjects
)

select s.student_id,s.student_name,s.subject_name,ifnull(attended_exams,0) attended_exams
from s left join t
on s.student_id = t.student_id and s.subject_name = t.subject_name 
order by student_id,subject_name

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

表：Logs
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| log_id        | int     |
+---------------+---------+
id 是上表具有唯一值的列。
上表的每一行包含日志表中的一个 ID。
 
编写解决方案，得到 Logs 表中的连续区间的开始数字和结束数字。
返回结果表按照 start_id 排序。
结果格式如下面的例子。

示例 1：
输入：
Logs 表：
+------------+
| log_id     |
+------------+
| 1          |
| 2          |
| 3          |
| 7          |
| 8          |
| 10         |
+------------+
输出：
+------------+--------------+
| start_id   | end_id       |
+------------+--------------+
| 1          | 3            |
| 7          | 8            |
| 10         | 10           |
+------------+--------------+

# Write your MySQL query statement below
WITH
    T AS (
        SELECT
            log_id,
            log_id - ROW_NUMBER() OVER (ORDER BY log_id) AS pid
        FROM Logs
    )
SELECT MIN(log_id) AS start_id, MAX(log_id) AS end_id
FROM T
GROUP BY pid;

1321.餐馆营业额变化增长 range row between and

表: Customer
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
| visited_on    | date    |
| amount        | int     |
+---------------+---------+
在 SQL 中，(customer_id, visited_on) 是该表的主键。
该表包含一家餐馆的顾客交易数据。
visited_on 表示 (customer_id) 的顾客在 visited_on 那天访问了餐馆。
amount 是一个顾客某一天的消费总额。

你是餐馆的老板，现在你想分析一下可能的营业额变化增长（每天至少有一位顾客）。

计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。average_amount 要 保留两位小数。
结果按 visited_on 升序排序。
返回结果格式的例子如下。

示例 1:
输入：
Customer 表:
+-------------+--------------+--------------+-------------+
| customer_id | name         | visited_on   | amount      |
+-------------+--------------+--------------+-------------+
| 1           | Jhon         | 2019-01-01   | 100         |
| 2           | Daniel       | 2019-01-02   | 110         |
| 3           | Jade         | 2019-01-03   | 120         |
| 4           | Khaled       | 2019-01-04   | 130         |
| 5           | Winston      | 2019-01-05   | 110         | 
| 6           | Elvis        | 2019-01-06   | 140         | 
| 7           | Anna         | 2019-01-07   | 150         |
| 8           | Maria        | 2019-01-08   | 80          |
| 9           | Jaze         | 2019-01-09   | 110         | 
| 1           | Jhon         | 2019-01-10   | 130         | 
| 3           | Jade         | 2019-01-10   | 150         | 
+-------------+--------------+--------------+-------------+
输出：
+--------------+--------------+----------------+
| visited_on   | amount       | average_amount |
+--------------+--------------+----------------+
| 2019-01-07   | 860          | 122.86         |
| 2019-01-08   | 840          | 120            |
| 2019-01-09   | 840          | 120            |
| 2019-01-10   | 1000         | 142.86         |
+--------------+--------------+----------------+

select visited_on, amount, average_amount
from (select row_number() over(order by visited_on) rn, 
             visited_on,
             round(sum(amount) over (order by visited_on rows between 6 preceding and current row), 2) amount,
             round(avg(amount) over (order by visited_on rows between 6 preceding and current row)/7, 2) average_amount
      from (select visited_on, sum(amount) amount
            from Customer
            group by visited_on) temp) result
where rn >= 7;

select 
    visited_on, amount, average_amount
from (
    select 
        distinct visited_on,  # 因为窗口函数是按照日期计算的。所以相同日期的结果也是相同的，直接去重即可
        sum(amount) over(order by visited_on range interval 6 day preceding) amount, # 按照日期排序，范围是当前日期和当前日期的前六天
        round(sum(amount) over(order by visited_on range interval 6 day preceding)/7, 2) average_amount # 同理
    from 
        Customer) t
where datediff(visited_on, (select min(visited_on) from Customer))>=6 #去除日期不足7日的结果
order by visited_on;

其实是先用窗口函数计算指定日期之内的销售总额，得到两个10号之前的总额，再去重，因为两个10号得到的金额是一样的。ROUND(SUM(amount) OVER (ORDER BY visited_on RANGE INTERVAL 6 DAY PRECEDING) / 7, 2) 计算这7天的消费平均值。这里的计算是针对每个日期的过去7天消费进行的，无论有多少顾客在这7天内消费，都将其总消费额平均。这里除以7就是因为窗口函数其实计算了八个顾客的总金额不能avg，只能除以七天

1322.广告效果 SUM(action = ‘xxx’)

表: Ads

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| ad_id         | int     |
| user_id       | int     |
| action        | enum    |
+---------------+---------+
(ad_id, user_id) 是该表的主键(具有唯一值的列的组合)
该表的每一行包含一条广告的 ID(ad_id)，用户的 ID(user_id) 和用户对广告采取的行为 (action)
action 列是一个枚举类型 ('Clicked', 'Viewed', 'Ignored') 。
 

一家公司正在运营这些广告并想计算每条广告的效果。

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



编写解决方案找出每一条广告的 ctr ，ctr 要 保留两位小数 。

返回结果需要按 ctr 降序、按 ad_id 升序 进行排序。

SELECT
    ad_id,
    ROUND(IFNULL(SUM(action = 'Clicked') / SUM(action IN('Clicked', 'Viewed')) * 100, 0), 2) AS ctr
FROM Ads
GROUP BY 1
ORDER BY 2 DESC, 1;

1336.每次访问的交易次数难应该是没读懂题目意思

表: Visits
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| visit_date    | date    |
+---------------+---------+
(user_id, visit_date) 是该表的主键(具有唯一值的列的组合)
该表的每行表示 user_id 在 visit_date 访问了银行
 
表: Transactions
+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| user_id          | int     |
| transaction_date | date    |
| amount           | int     |
+------------------+---------+
该表可能有重复行
该表的每一行表示 user_id 在 transaction_date 完成了一笔 amount 数额的交易
可以保证用户 (user) 在 transaction_date 访问了银行 (也就是说 Visits 表包含 (user_id, transaction_date) 行)
 
银行想要得到银行客户在一次访问时的交易次数和相应的在一次访问时该交易次数的客户数量的图表
编写解决方案找出多少客户访问了银行但没有进行任何交易，多少客户访问了银行进行了一次交易等等

结果包含两列：
transactions_count： 客户在一次访问中的交易次数
visits_count： 在 transactions_count 交易次数下相应的一次访问时的客户数量
transactions_count 的值从 0 到所有用户一次访问中的 max(transactions_count) 

结果按 transactions_count 排序

with t as(
select l.user_id user_id,l.visit_date date,count(t.user_id) transactions_count 
from Visits l left join Transactions t
on l.user_id = t.user_id and l.visit_date = t.transaction_date
group by l.user_id,l.visit_date
)

select transactions_count,count(distinct user_id) visits_count 
from t
group by transactions_count

1369.获取最近第二次的活动

表: UserActivity
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| username      | varchar |
| activity      | varchar |
| startDate     | Date    |
| endDate       | Date    |
+---------------+---------+
该表可能有重复的行
该表包含每个用户在一段时间内进行的活动的信息
名为 username 的用户在 startDate 到 endDate 日内有一次活动

编写解决方案展示每一位用户 最近第二次 的活动
如果用户仅有一次活动，返回该活动
一个用户不能同时进行超过一项活动，以 任意 顺序返回结果
下面是返回结果格式的例子。

SELECT
    username,
    activity,
    startdate,
    enddate
FROM
    (
        SELECT
            *,
            RANK() OVER (
                PARTITION BY username
                ORDER BY startdate DESC
            ) AS rk,
            COUNT(username) OVER (PARTITION BY username) AS cnt
        FROM UserActivity
    ) AS a
WHERE a.rk = 2 OR a.cnt = 1;

1384.按年度列出销售总额难

Product 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| product_name  | varchar |
+---------------+---------+
product_id 是这张表的主键(具有唯一值的列)。
product_name 是产品的名称。
 

Sales 表：

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| product_id          | int     |
| period_start        | date    |
| period_end          | date    |
| average_daily_sales | int     |
+---------------------+---------+
product_id 是这张表的主键(具有唯一值的列)。
period_start 和 period_end 是该产品销售期的起始日期和结束日期，且这两个日期包含在销售期内。
average_daily_sales 列存储销售期内该产品的日平均销售额。
销售日期范围为2018年到2020年。
 

编写解决方案，找出每个产品每年的总销售额，并包含 product_id , product_name , report_year 以及 total_amount 。

返回结果并按 product_id 和 report_year 排序。

返回结果格式如下例所示。

with s as(
select product_id,product_name,Year(period_start) report_year,datediff(period_end,period_start)*average_daily_sales total_amount
from Sales
where Year(period_start)=Year(period_end)
)

with t as(
select product_id,product_name,period_start,case when Year(period_start)=2018 and Year(period_end)=2019 then Year(period_start)+'12-31' case when Year(period_start)=2019 and Year(period_end)=2020 then Year(period_start)+'12-31' m-year,period_end,average_daily_sales 
from Sales
)

with d as(
select product_id,product_name,period_start,'2018-12-31' m-year,365,'2020-1-1' mm-year,period_end,average_daily_sales 
from Sales
where Year(period_start)=2018 and Year(period_end)=2020
)

select product_id,product_name,Year(period_start),datediff(m-year,period_start)*average_daily_sales total_amount
from t
union all 
select product_id,product_name,Year(period_end),datediff(period_end,date_add(m-year,interval 1 day)*average_daily_sales total_amount
from t
union all
select *
from s
union all
select product_id,product_name,Year(period_start),datediff(m-year,period_start)*average_daily_sales total_amount from d
union all
select product_id,product_name,2019,365*average_daily_sales total_amount from d
union all
select product_id,product_name,2020,datediff(period_end,mm-year)*average_daily_sales total_amount from d

1412.查找成绩处于中游的学生

表: Student
+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| student_id          | int     |
| student_name        | varchar |
+---------------------+---------+
student_id 是该表主键(具有唯一值的列)。
student_name 学生名字。

表: Exam
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| exam_id       | int     |
| student_id    | int     |
| score         | int     |
+---------------+---------+
(exam_id, student_id) 是该表主键(具有唯一值的列的组合)。
学生 student_id 在测验 exam_id 中得分为 score。

成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。
编写解决方案，找出在 所有 测验中都处于中游的学生 (student_id, student_name)。不要返回从来没有参加过测验的学生。

返回结果表按照 student_id 排序。

with t as(
select student_id
from Exam a,
(select exam_id,max(score) maxScore,min(score) minScore
from Exam
group by exam_id) s
where a.exam_id = s.exam_id and (a.score = minScore OR a.score = maxScore)
)

select student_id,student_name
from Exam,Student
where Exam.student_id = Student.student_id and Exam.student_id not in(
	select * from t
)
order by student_id

# Write your MySQL query statement below
WITH
    T AS (
        SELECT
            student_id,
            RANK() OVER (
                PARTITION BY exam_id
                ORDER BY score
            ) AS rk1,
            RANK() OVER (
                PARTITION BY exam_id
                ORDER BY score DESC
            ) AS rk2
        FROM Exam
    )
SELECT student_id, student_name
FROM
    T
    JOIN Student USING (student_id)
GROUP BY 1
HAVING SUM(rk1 = 1) = 0 AND SUM(rk2 = 1) = 0
ORDER BY 1;

1435.制作会话柱状图

表：Sessions
+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| session_id          | int     |
| duration            | int     |
+---------------------+---------+
session_id 是该表主键
duration 是用户访问应用的时间, 以秒为单位
 
你想知道用户在你的 app 上的访问时长情况。因此你决定统计访问时长区间分别为 "[0-5>"，"[5-10>"，"[10-15>" 和 "15 minutes or more" 的会话数量，并以此绘制柱状图。
写一个解决方案来报告 (bin, total) 。

返回结果 无顺序要求

select '[0-5>',sum(duration>=0 and duration<300) total
from Sessions
union all
select '[5-10>',sum(duration>=300 and duration<600) total
from Sessions
union all
select '[10-15>',sum(duration>=600 and duration<900) total
from Sessions
union all
select '15 or more',sum(duration>=900) total
from Sessions

1440.计算布尔表达式的值

表 Variables:
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| name          | varchar |
| value         | int     |
+---------------+---------+
在 SQL 中，name 是该表主键.
该表包含了存储的变量及其对应的值.
 
表 Expressions:
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| left_operand  | varchar |
| operator      | enum    |
| right_operand | varchar |
+---------------+---------+
在 SQL 中，(left_operand, operator, right_operand) 是该表主键.
该表包含了需要计算的布尔表达式.
operator 是枚举类型, 取值于('<', '>', '=')
left_operand 和 right_operand 的值保证存在于 Variables 表单中.

计算表 Expressions 中的布尔表达式。
返回的结果表 无顺序要求 。

1
2
3

select left_operand,operator,right_operand,case when operator='=' then v1.value = v2.value case when operator='>' then v1.value > v2.value case when operator='<' then v1.value < v2.value end value 
from Expressions e,Variables v1,Variables v2
where e.left_operand = v1.name and e.right_operand = v2.name

# Write your MySQL query statement below
SELECT
    left_operand,
    operator,
    right_operand,
    CASE
        WHEN (
            (operator = '=' AND v1.value = v2.value)
            OR (operator = '>' AND v1.value > v2.value)
            OR (operator = '<' AND v1.value < v2.value)
        ) THEN 'true'
        ELSE 'false'
    END AS value
FROM
    Expressions AS e
    JOIN Variables AS v1 ON e.left_operand = v1.name
    JOIN Variables AS v2 ON e.right_operand = v2.name;

1454.活跃用户

表 Accounts:
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id 是该表主键（具有唯一值的列）
该表包含账户 id 和账户的用户名.

表 Logins:
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| login_date    | date    |
+---------------+---------+
该表可能包含重复项.
该表包含登录用户的账户 id 和登录日期. 用户也许一天内登录多次.
 
活跃用户 是指那些至少连续 5 天登录账户的用户。
编写解决方案,  找到 活跃用户 的 id 和 name。
返回的结果表按照 id 排序 。

# Write your MySQL query statement below
WITH
    T AS (
        SELECT DISTINCT *
        FROM
            Logins
            JOIN Accounts USING (id)
    ),
    P AS (
        SELECT
            *,
            DATE_SUB(
                login_date,
                INTERVAL ROW_NUMBER() OVER (
                    PARTITION BY id
                    ORDER BY login_date
                ) DAY
            ) g
        FROM T
    )
SELECT DISTINCT id, name
FROM P
GROUP BY id, g
HAVING COUNT(*) >= 5
ORDER BY 1;

1479.周内每天的销售情况

表：Orders
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| order_date    | date    | 
| item_id       | varchar |
| quantity      | int     |
+---------------+---------+
(order_id, item_id) 是该表主键(具有唯一值的列的组合)
该表包含了订单信息
order_date 是id为 item_id 的商品被id为 customer_id 的消费者订购的日期.

表：Items
+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| item_id             | varchar |
| item_name           | varchar |
| item_category       | varchar |
+---------------------+---------+
item_id 是该表主键(具有唯一值的列)
item_name 是商品的名字
item_category 是商品的类别
 
你是企业主，想要获得分类商品和周内每天的销售报告。
编写解决方案，报告 周内每天 每个商品类别下订购了多少单位。
返回结果表单 按商品类别排序 。

# Write your MySQL query statement below
SELECT
    item_category AS c2ategory,
    SUM(IF(DAYOFWEEK(order_date) = '2', quantity, 0)) AS Monday,
    SUM(IF(DAYOFWEEK(order_date) = '3', quantity, 0)) AS Tuesday,
    SUM(IF(DAYOFWEEK(order_date) = '4', quantity, 0)) AS Wednesday,
    SUM(IF(DAYOFWEEK(order_date) = '5', quantity, 0)) AS Thursday,
    SUM(IF(DAYOFWEEK(order_date) = '6', quantity, 0)) AS Friday,
    SUM(IF(DAYOFWEEK(order_date) = '7', quantity, 0)) AS Saturday,
    SUM(IF(DAYOFWEEK(order_date) = '1', quantity, 0)) AS Sunday
FROM
    Orders AS o
    RIGHT JOIN Items AS i ON o.item_id = i.item_id
GROUP BY category
ORDER BY category

1	这里把if换成case when也可以，注意case when then ... when then ... end 和case when then end,case when then end的不同

1484.按日期分组销售产品 group_concat

表 Activities：
+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| sell_date   | date    |
| product     | varchar |
+-------------+---------+
该表没有主键(具有唯一值的列)。它可能包含重复项。
此表的每一行都包含产品名称和在市场上销售的日期。

编写解决方案找出每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 sell_date 排序的结果表。
结果表结果格式如下例所示。

示例 1:
输入：
Activities 表：
+------------+-------------+
| sell_date  | product     |
+------------+-------------+
| 2020-05-30 | Headphone   |
| 2020-06-01 | Pencil      |
| 2020-06-02 | Mask        |
| 2020-05-30 | Basketball  |
| 2020-06-01 | Bible       |
| 2020-06-02 | Mask        |
| 2020-05-30 | T-Shirt     |
+------------+-------------+
输出：
+------------+----------+------------------------------+
| sell_date  | num_sold | products                     |
+------------+----------+------------------------------+
| 2020-05-30 | 3        | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | Mask                         |
+------------+----------+------------------------------+

select
     sell_date,
     count(distinct product) as num_sold,
     group_concat(distinct product order by product separator ',') as products
 from Activities 
 group by sell_date
 order by sell_date

1517.查找拥有有效邮箱的用户 regexp REGEXP_LIKE 正则表达式

表: Users
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| name          | varchar |
| mail          | varchar |
+---------------+---------+
user_id 是该表的主键（具有唯一值的列）。
该表包含了网站已注册用户的信息。有一些电子邮件是无效的。
 
编写一个解决方案，以查找具有有效电子邮件的用户。
一个有效的电子邮件具有前缀名称和域，其中：

 前缀 名称是一个字符串，可以包含字母（大写或小写），数字，下划线 '_' ，点 '.' 和/或破折号 '-' 。前缀名称 必须 以字母开头。
域 为 '@leetcode.com' 。
以任何顺序返回结果表。

select *
from users
where mail regexp '^[a-zA-Z]+[a-zA-Z0-9_\\.\\/\\-]*@leetcode\\.com$';


SELECT * 
FROM users
WHERE REGEXP_LIKE(mail, '^[a-zA-Z][a-zA-Z0-9._-]*@leetcode\\.com$', 'c');

1527.患某种疾病的患者 like REGEXP

患者信息表： Patients
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| patient_id   | int     |
| patient_name | varchar |
| conditions   | varchar |
+--------------+---------+
在 SQL 中，patient_id （患者 ID）是该表的主键。
'conditions' （疾病）包含 0 个或以上的疾病代码，以空格分隔。
这个表包含医院中患者的信息。

查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀DIAB1 。

按 任意顺序 返回结果表。

1 2	select * from Patients where conditions like 'DIAB1%' or conditions like '% DIAB1%'

1
2
3

SELECT patient_id, patient_name, conditions
FROM Patients
WHERE conditions REGEXP '^DIAB1|\\sDIAB1';

1613.找到遗失的ID 递归 recursive

表: Customers
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| customer_name | varchar |
+---------------+---------+
customer_id 是该表主键.
该表第一行包含了顾客的名字和 id.
 
编写一个解决方案, 找到所有遗失的顾客 id。遗失的顾客 id 是指那些不在 Customers 表中, 值却处于 1 和表中 最大 customer_id 之间的 id.
注意: 最大的 customer_id 值不会超过 100.
返回结果按 ids 升序 排列

查询结果格式如下例所示。

# Write your MySQL query statement below
WITH RECURSIVE
    t AS (
        SELECT
            1 AS n
        UNION ALL
        SELECT
            n + 1
        FROM t
        WHERE n < 100
    )
SELECT
    n AS ids
FROM t
WHERE
    n < (
        SELECT
            MAX(customer_id)
        FROM Customers
    )
    AND n NOT IN (
        SELECT
            customer_id
        FROM Customers
    );

1635.Hopper 公司查询! recursive

表: Drivers
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| driver_id   | int     |
| join_date   | date    |
+-------------+---------+
driver_id 是该表的主键(具有唯一值的列)。
该表的每一行均包含驾驶员的ID以及他们加入Hopper公司的日期。

表: Rides
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| ride_id      | int     |
| user_id      | int     |
| requested_at | date    |
+--------------+---------+
ride_id 是该表的主键(具有唯一值的列)。
该表的每一行均包含行程ID(ride_id)，用户ID(user_id)以及该行程的日期(requested_at)。
该表中可能有一些不被接受的乘车请求。

表: AcceptedRides
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| ride_id       | int     |
| driver_id     | int     |
| ride_distance | int     |
| ride_duration | int     |
+---------------+---------+
ride_id 是该表的主键(具有唯一值的列)。
该表的每一行都包含已接受的行程信息。
表中的行程信息都在“Rides”表中存在。
编写解决方案以报告 2020 年每个月的以下统计信息：

截至某月底，当前在Hopper公司工作的驾驶员数量（active_drivers）。
该月接受的乘车次数（accepted_rides）。
返回按month 升序排列的结果表，其中month 是月份的数字（一月是1，二月是2，依此类推）。

返回结果格式如下例所示。

示例 1：
输入：
表 Drivers:
+-----------+------------+
| driver_id | join_date  |
+-----------+------------+
| 10        | 2019-12-10 |
| 8         | 2020-1-13  |
| 5         | 2020-2-16  |
| 7         | 2020-3-8   |
| 4         | 2020-5-17  |
| 1         | 2020-10-24 |
| 6         | 2021-1-5   |
+-----------+------------+
表 Rides:
+---------+---------+--------------+
| ride_id | user_id | requested_at |
+---------+---------+--------------+
| 6       | 75      | 2019-12-9    |
| 1       | 54      | 2020-2-9     |
| 10      | 63      | 2020-3-4     |
| 19      | 39      | 2020-4-6     |
| 3       | 41      | 2020-6-3     |
| 13      | 52      | 2020-6-22    |
| 7       | 69      | 2020-7-16    |
| 17      | 70      | 2020-8-25    |
| 20      | 81      | 2020-11-2    |
| 5       | 57      | 2020-11-9    |
| 2       | 42      | 2020-12-9    |
| 11      | 68      | 2021-1-11    |
| 15      | 32      | 2021-1-17    |
| 12      | 11      | 2021-1-19    |
| 14      | 18      | 2021-1-27    |
+---------+---------+--------------+
表 AcceptedRides:
+---------+-----------+---------------+---------------+
| ride_id | driver_id | ride_distance | ride_duration |
+---------+-----------+---------------+---------------+
| 10      | 10        | 63            | 38            |
| 13      | 10        | 73            | 96            |
| 7       | 8         | 100           | 28            |
| 17      | 7         | 119           | 68            |
| 20      | 1         | 121           | 92            |
| 5       | 7         | 42            | 101           |
| 2       | 4         | 6             | 38            |
| 11      | 8         | 37            | 43            |
| 15      | 8         | 108           | 82            |
| 12      | 8         | 38            | 34            |
| 14      | 1         | 90            | 74            |
+---------+-----------+---------------+---------------+
输出：
+-------+----------------+----------------+
| month | active_drivers | accepted_rides |
+-------+----------------+----------------+
| 1     | 2              | 0              |
| 2     | 3              | 0              |
| 3     | 4              | 1              |
| 4     | 4              | 0              |
| 5     | 5              | 0              |
| 6     | 5              | 1              |
| 7     | 5              | 1              |
| 8     | 5              | 1              |
| 9     | 5              | 0              |
| 10    | 6              | 0              |
| 11    | 6              | 2              |
| 12    | 6              | 1              |
+-------+----------------+----------------+
解释：
截至1月底->两个活跃的驾驶员（10,8），没有被接受的行程。
截至2月底->三个活跃的驾驶员（10,8,5），没有被接受的行程。
截至3月底->四个活跃的驾驶员（10,8,5,7），一个被接受的行程（10）。
截至4月底->四个活跃的驾驶员（10,8,5,7），没有被接受的行程。
截至5月底->五个活跃的驾驶员（10,8,5,7,4），没有被接受的行程。
截至6月底->五个活跃的驾驶员（10,8,5,7,4），一个被接受的行程（13）。
截至7月底->五个活跃的驾驶员（10,8,5,7,4），一个被接受的行程（7）。
截至8月底->五个活跃的驾驶员（10,8,5,7,4），一位接受的行程（17）。
截至9月底->五个活跃的驾驶员（10,8,5,7,4），没有被接受的行程。
截至10月底->六个活跃的驾驶员（10,8,5,7,4,1），没有被接受的行程。
截至11月底->六个活跃的驾驶员（10,8,5,7,4,1），两个被接受的行程（20,5）。
截至12月底->六个活跃的驾驶员（10,8,5,7,4,1），一个被接受的行程（2）。

# Write your MySQL query statement below
WITH
    recursive Months AS (
        SELECT
            1 AS month
        UNION ALL
        SELECT
            month + 1
        FROM Months
        WHERE month < 12
    ),
    Ride AS (
        SELECT MONTH(requested_at) AS month, COUNT(1) AS cnt
        FROM
            Rides AS r
            JOIN AcceptedRides AS a
                ON r.ride_id = a.ride_id AND YEAR(requested_at) = 2020
        GROUP BY month
    )

SELECT
    m.month,
    COUNT(driver_id) AS active_drivers,
    IFNULL(r.cnt, 0) AS accepted_rides
FROM
    Months AS m
    LEFT JOIN Drivers AS d
        ON (m.month >= MONTH(d.join_date) AND YEAR(d.join_date) = 2020)
        OR YEAR(d.join_date) < 2020
    LEFT JOIN Ride AS r ON m.month = r.month
GROUP BY month;

1651.Hopper 公司查询川 RECURSIVE OVER (ROWS BETWEEN)

表： Drivers
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| driver_id   | int     |
| join_date   | date    |
+-------------+---------+
driver_id 是该表具有唯一值的列。
该表的每一行均包含驾驶员的 ID 以及他们加入 Hopper 公司的日期。

表：Rides
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| ride_id      | int     |
| user_id      | int     |
| requested_at | date    |
+--------------+---------+
ride_id 是该表具有唯一值的列。 
该表的每一行均包含行程 ID(ride_id)，用户 ID(user_id) 以及该行程的日期(requested_at)。 
该表中可能有一些不被接受的乘车请求。

表：AcceptedRides
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| ride_id       | int     |
| driver_id     | int     |
| ride_distance | int     |
| ride_duration | int     |
+---------------+---------+
ride_id 是该表具有唯一值的列。 
该表的每一行都包含已接受的行程信息。 
表中的行程信息都在 "Rides" 表中存在。
 
编写一个解决方案，计算出从 2020 年 1 月至 3 月 至 2020 年 10 月至 12 月 的每三个月窗口的 average_ride_distance 和 average_ride_duration 。并将 average_ride_distance 和 average_ride_duration 四舍五入至 小数点后两位 。
通过将三个月的总 ride_distance 相加并除以 3 来计算 average_ride_distance 。average_ride_duration 的计算方法与此类似。
返回按 month 升序排列的结果表，其中 month 是起始月份的编号（一月为 1，二月为 2 ...）。

# Write your MySQL query statement below
WITH RECURSIVE
    Months AS (
        SELECT 1 AS month
        UNION ALL
        SELECT month + 1
        FROM Months
        WHERE month < 12
    ),
    Ride AS (
        SELECT
            month,
            SUM(IFNULL(ride_distance, 0)) AS ride_distance,
            SUM(IFNULL(ride_duration, 0)) AS ride_duration
        FROM
            Months AS m
            LEFT JOIN Rides AS r ON month = MONTH(requested_at) AND YEAR(requested_at) = 2020
            LEFT JOIN AcceptedRides AS a ON r.ride_id = a.ride_id
        GROUP BY month
    )
    
SELECT
    month,
    ROUND(
        AVG(ride_distance) OVER (ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING),
        2
    ) AS average_ride_distance,
    ROUND(
        AVG(ride_duration) OVER (ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING),
        2
    ) AS average_ride_duration
FROM Ride
ORDER BY month
LIMIT 10;

1661.每台机器的进程平均运行时间

表: Activity
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| machine_id     | int     |
| process_id     | int     |
| activity_type  | enum    |
| timestamp      | float   |
+----------------+---------+
该表展示了一家工厂网站的用户活动。
(machine_id, process_id, activity_type) 是当前表的主键（具有唯一值的列的组合）。
machine_id 是一台机器的ID号。
process_id 是运行在各机器上的进程ID号。
activity_type 是枚举类型 ('start', 'end')。
timestamp 是浮点类型,代表当前时间(以秒为单位)。
'start' 代表该进程在这台机器上的开始运行时间戳 , 'end' 代表该进程在这台机器上的终止运行时间戳。
同一台机器，同一个进程都有一对开始时间戳和结束时间戳，而且开始时间戳永远在结束时间戳前面。

现在有一个工厂网站由几台机器运行，每台机器上运行着 相同数量的进程 。编写解决方案，计算每台机器各自完成一个进程任务的平均耗时。

完成一个进程任务的时间指进程的'end' 时间戳 减去 'start' 时间戳。平均耗时通过计算每台机器上所有进程任务的总耗费时间除以机器上的总进程数量获得。

结果表必须包含machine_id（机器ID） 和对应的 average time（平均耗时） 别名 processing_time，且四舍五入保留3位小数。

以 任意顺序 返回表。

select
    machine_id,
    round(2*avg(if(activity_type = 'start',-1,1)*timestamp),3) as processing_time
from
    Activity
group by
    machine_id

1	sum(if(activity_type = 'start',-1*timestamp,timestamp))/count(distinct process_id)

1667.修复表中的名字 concat left right upper lower

表： Users
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| name           | varchar |
+----------------+---------+
user_id 是该表的主键(具有唯一值的列)。
该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。

编写解决方案，修复名字，使得只有第一个字符是大写的，其余都是小写的。

返回按 user_id 排序的结果表。

with cte1 as (
    #先全部小写处理
    select user_id, lower(name) as name from Users) 
    #首字母改成大写和字符串剩下部分进行拼接
    select user_id,  concat( 
        case substring(name,1,1) when "a" then "A" 
        when "b" then "B" 
        when "c" then "C" 
        when "d" then "D" 
        when "e" then "E" 
        when "f" then "F" 
        when "g" then "G" 
        when "h" then "H" 
        when "i" then "I" 
        when "j" then "J" 
        when "k" then "K" 
        when "l" then "L" 
        when "m" then "M" 
        when "n" then "N" 
        when "o" then "O" 
        when "p" then "P" 
        when "q" then "Q" 
        when "r" then "R" 
        when "s" then "S" 
        when "t" then "T" 
        when "u" then "U" 
        when "v" then "V" 
        when "w" then "W" 
        when "x" then "X" 
        when "y" then "Y" 
        when "z" then "Z" end ,
    substring(name,2)) as name from cte1 order by user_id;

select user_id,
concat(upper(left(name, 1)), lower(right(name, length(name) - 1))) name
from Users
order by user_id

1683.无效的推文

表：Tweets
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| tweet_id       | int     |
| content        | varchar |
+----------------+---------+
在 SQL 中，tweet_id 是这个表的主键。
content 只包含字母数字字符，'!'，' '，不包含其它特殊字符。
这个表包含某社交媒体 App 中所有的推文。

查询所有无效推文的编号（ID）。当推文内容中的字符数严格大于 15 时，该推文是无效的。

以任意顺序返回结果表。

# Write your MySQL query statement below
SELECT
    tweet_id
FROM Tweets
WHERE CHAR_LENGTH(content) > 15;

1709.访问日期之间最大的空档期 LEAD(xxx) OVER(partition by)

表： UserVisits
+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id     | int  |
| visit_date  | date |
+-------------+------+
该表没有主键，它可能有重复的行
该表包含用户访问某特定零售商的日期日志。
 
假设今天的日期是 '2021-1-1' 。

编写解决方案，对于每个 user_id ，求出每次访问及其下一个访问（若该次访问是最后一次，则为今天）之间最大的空档期天数 window 。

返回结果表，按用户编号 user_id 排序。

with t as(
	select user_id,visit_date,row_number() over(partition by user_id order by visit_date) rk
	from UserVisits
)

with s as(
select t1.user_id user_id,max(t2.visit_date-t1.visit_date) biggest_window
from t t1 join t t2 
on t1.user_id = t2.user_id and t2.rk - t1.rk = 1
group by t1.user_id
order by user_id
)

select * from s
union all
select user_id,datediff('2021-1-1',visit_date) biggest_window
from UserVisits
where user_id not in select user_id from s

# Write your MySQL query statement below
WITH
    T AS (
        SELECT
            user_id,
            DATEDIFF(
                LEAD(visit_date, 1, '2021-1-1') OVER (
                    PARTITION BY user_id
                    ORDER BY visit_date
                ),
                visit_date
            ) AS diff
        FROM UserVisits
    )
SELECT user_id, MAX(diff) AS biggest_window
FROM T
GROUP BY 1
ORDER BY 1;

1972 同一天的第一个电话和最后一个电话难

1
2
3

编写一个 SQL 查询来找出那些ID们在任意一天的第一个电话和最后一个电话都是和同一个人的。这些电话不论是拨打者还是接收者都会被记录。
结果请放在一个任意次序约束的表中。
查询结果格式如下所示：

--MySQL 
with temp as (select * from calls
            union all
            select recipient_id caller_id, caller_id recipient_id, call_time from calls
    ),

temp1 as (select
            *,
            dense_rank() over (partition by date_format(call_time,"%Y-%m-%d"),caller_id order by call_time asc) rk1,
            dense_rank() over (partition by date_format(call_time,"%Y-%m-%d"),caller_id order by call_time desc) rk2
        from temp
    )

select
    distinct caller_id as user_id
from temp1
where rk1 = 1 or rk2 = 1
group by caller_id, date_format(call_time,"%Y-%m-%d")
having count(distinct recipient_id) = 1


with t1 as (select
caller_id,recipient_id,call_time #选出 所有id 在某个时间 跟另一个通话的id
from
Calls
 
union all
 
select
recipient_id,caller_id,call_time
from
Calls
),
t2 as (
select#按 caller_id 和 每天 分组  对 call_time标号 便于 找到 每天 第一个 和最后的一通电话
caller_id,recipient_id,call_time,row_number() over(partition by caller_id,date_format(call_time,'%Y-%m-%d') order by call_time) rn,row_number() over(partition by caller_id,date_format(call_time,'%Y-%m-%d') order by call_time desc) rn2
from
t1
),
t3 as (
select
caller_id,recipient_id,date_format(call_time,'%Y-%m-%d') day #某天第一次通话
from
t2
where rn = 1 
union all
select
caller_id,recipient_id,date_format(call_time,'%Y-%m-%d') day#某天最后一次通话
from
t2
where rn2 = 1 
)
select
distinct caller_id user_id
from
 
t3
group by 
caller_id,day #对 某人某天第一次 和最后一次通话的id 去重计数  值为1就是题中需求
having
count(distinct recipient_id)=1

t2结果

mysql注释问题

在MySQL中，注释不应该使用井号（#）在SQL语句的中间部分，尤其是在SELECT列表或其他语句结构中。井号（#）在MySQL中通常用于行尾来标记注释的开始，直到行末。如果您想在SQL语句的某部分添加注释，您应该使用两个连字符（--）后跟空格，或者如果注释跨越多行，您可以使用/* 注释内容 */。

mysql修改列数据类型

MySQL 
alter table name modify column col1 int;

sql server 
alter table name alter column col1 int;

1988

表： Schools

+-------------+------+
| Column Name | Type |
+-------------+------+
| school_id   | int  |
| capacity    | int  |
+-------------+------+
school_id 是该表的主键。
此表包含了一些学校的容纳能力。容纳能力指的是学校能够接纳学生的最大数量。

表： Exam

+---------------+------+
| Column Name   | Type |
+---------------+------+
| score         | int  |
| student_count | int  |
+---------------+------+
score 是该表的主键。
表中每一行表示有 student_count 名学生在考试中至少获得了 score 分。
表中的数据在逻辑上是正确的，即记录了高score的行相比记录了低score的行拥有相同或更少的student_count。
也就是说，对于表中的i行与j行，如果scorei > scorej，那么student_counti <= student_countj

每年，学校会公布学生申请所需的最低分数要求。学校根据所有学生的考试成绩来决定其最低分数要求。

学校希望确保即使每一个满足分数要求的学生都申请该学校，学校也有足够的能力接纳每一个学生。
学校也希望尽可能多的学生能申请该学校。
学校必须使用在 Exam 表中的 score 来作为最低分数要求。


编写一个SQL查询来查询每所学校的最低分数要求。如果同时有多个score值满足上述要求，则选择其中最小的一个。如果数据不足以决定最低分数要求，那么输出 -1。

返回的结果表可以按任意顺序排序。

查询结果格式如下例所示：

示例：

输入:
Schools 表:
+-----------+----------+
| school_id | capacity |
+-----------+----------+
| 11        | 151      |
| 5         | 48       |
| 9         | 9        |
| 10        | 99       |
+-----------+----------+
Exam 表:
+-------+---------------+
| score | student_count |
+-------+---------------+
| 975   | 10            |
| 966   | 60            |
| 844   | 76            |
| 749   | 76            |
| 744   | 100           |
+-------+---------------+
输出:
+-----------+-------+
| school_id | score |
+-----------+-------+
| 5         | 975   |
| 9         | -1    |
| 10        | 749   |
| 11        | 744   |
+-----------+-------+
解释： 
- School 5：学校的容纳能力为 48 。选择 975 作为最低分数要求，因为学校最多会收到 10 份申请，这在学校的容纳能力以内。
- School 10：学校的容纳能力为 99 。可以选择 844 或 749 作为最低分数要求，因为学校最多会收到 76 份申请，这在学校的容纳能力以内。又因为 749 是所有可选项中最小的，因此我们选择 749 。
- School 11：学校的容纳能力为 151 。选择 744 作为最低分数要求，因为学校最多会收到 100 份申请，这在学校的容纳能力以内。
- School 9：给出的数据不足以确定最低分数要求。如果选择 975 作为最低分数要求，学校可能会收到 10 份申请，然而学校的容纳能力只有 9 。我们没有关于更高分数的信息，因此我们返回 -1 。

select
    school_id,
    ifnull(min(score),-1) as score
from Schools
left join Exam
on capacity>=student_count
group by school_id;

1990

问题：
三个实验平台中每种实验完成的次数
请注意，每一对（实验平台、实验名称）都应包含在输出中，包括平台上实验次数是零的。

表: Experiments

+-----------------+------+
| Column Name     | Type |
+-----------------+------+
| experiment_id   | int  |
| platform        | enum |
| experiment_name | enum |
+-----------------+------+

experiment_id 是这个表的主键.
platform 是枚举类型的，取值是这三种 ('Android', 'IOS', 'Web') 之一.
experiment_name 也是枚举类型的，取值是这三种 ('Reading', 'Sports', 'Programming') 之一.
这个表包含有关随机实验人员进行的实验的 ID、用于做实验的平台以及实验名称的信息。

结果可以以任意顺序给出。
查询的结果如下所示：

示例：
输入：
Experiments table:
+---------------+----------+-----------------+
| experiment_id | platform | experiment_name |
+---------------+----------+-----------------+
| 4             | IOS      | Programming     |
| 13            | IOS      | Sports          |
| 14            | Android  | Reading         |
| 8             | Web      | Reading         |
| 12            | Web      | Reading         |
| 18            | Web      | Programming     |
+---------------+----------+-----------------+
输出：
+----------+-----------------+-----------------+
| platform | experiment_name | num_experiments |
+----------+-----------------+-----------------+
| Android  | Reading         | 1               |
| Android  | Sports          | 0               |
| Android  | Programming     | 0               |
| IOS      | Reading         | 0               |
| IOS      | Sports          | 1               |
| IOS      | Programming     | 1               |
| Web      | Reading         | 2               |
| Web      | Sports          | 0               |
| Web      | Programming     | 1               |
+----------+-----------------+-----------------+
解释：
在安卓平台上, 我们只做了一个"Reading" 实验.
在 "IOS" 平台上，我们做了一个"Sports" 实验和一个"Programming" 实验.
在 "Web" 平台上，我们做了两个"Reading" 实验和一个"Programming" 实验.

-- https://leetcode.cn/problems/count-the-number-of-experiments
drop table if EXISTS Experiments;
Create table If Not Exists Experiments (experiment_id int, platform enum('Android','IOS','Web'), experiment_name enum('Programming','Sports','Reading'));
insert into Experiments values (4,'IOS','Programming');
insert into Experiments values (13,'IOS','Sports');
insert into Experiments values (14,'Android','Reading');
insert into Experiments values (8,'Web','Reading');
insert into Experiments values (12,'Web','Reading');
insert into Experiments values (18,'Web','Programming');
select * from Experiments;

with pe as
(   select * from
    (
    select 'IOS' platform union all
    select 'Android' platform union all
    select 'Web' platform
    )p
cross join
(
    select 'Programming' experiment_name union all
    select 'Sports' experiment_name union all
    select 'Reading' experiment_name
)e
)
-- select * from pe;
select pe.platform,pe.experiment_name,count(w.platform) as num_experiments
from pe left join Experiments as w using(platform,experiment_name)
group by platform,experiment_name;

# Write your MySQL query statement below
with new_table as(
    select 'Android' platform
    union select 'IOS'
    union select 'Web'
),
other_table as(
    select 'Reading' experiment_name
    union select 'Sports'
    union select 'Programming'
)
select new_table.platform ,other_table.experiment_name,count(experiment_id)num_experiments
from new_table join other_table #笛卡尔积
left join Experiments E 
on E.platform = new_table.platform 
AND other_table.experiment_name = E.experiment_name
group by new_table.platform ,other_table.experiment_name

2004

SQL架构

表: Candidates

+-------------+------+
| Column Name | Type |
+-------------+------+
| employee_id | int  |
| experience  | enum |
| salary      | int  |
+-------------+------+
employee_id是此表的主键列。
经验是包含一个值（“高级”、“初级”）的枚举类型。
此表的每一行都显示候选人的id、月薪和经验。
一家公司想雇佣新员工。公司的工资预算是 70000 美元。公司的招聘标准是：

雇佣最多的高级员工。
在雇佣最多的高级员工后，使用剩余预算雇佣最多的初级员工。
编写一个SQL查询，查找根据上述标准雇佣的高级员工和初级员工的数量。
按 任意顺序 返回结果表。
查询结果格式如下例所示。

示例 1:

输入: 
Candidates table:
+-------------+------------+--------+
| employee_id | experience | salary |
+-------------+------------+--------+
| 1           | Junior     | 10000  |
| 9           | Junior     | 10000  |
| 2           | Senior     | 20000  |
| 11          | Senior     | 20000  |
| 13          | Senior     | 50000  |
| 4           | Junior     | 40000  |
+-------------+------------+--------+
输出: 
+------------+---------------------+
| experience | accepted_candidates |
+------------+---------------------+
| Senior     | 2                   |
| Junior     | 2                   |
+------------+---------------------+
说明：
我们可以雇佣2名ID为（2,11）的高级员工。由于预算是7万美元，他们的工资总额是4万美元，我们还有3万美元，但他们不足以雇佣ID为13的高级员工。
我们可以雇佣2名ID为（1,9）的初级员工。由于剩下的预算是3万美元，他们的工资总额是2万美元，我们还有1万美元，但他们不足以雇佣ID为4的初级员工。

with t1 as (select
employee_id,experience,salary,sum(salary) over(partition by experience order by salary rows between unbounded preceding and current row) so
#按 experience 分组开窗,按salary 生序排列逐一求和   然后 <=70000 就是能招聘的 员工
from
Candidates
),
t2 as (
select
"Senior" experience,count(employee_id) accepted_candidates,70000-ifnull(max(so),0) m   # 能招聘的 Senior 等级员工
from
t1
where
experience = "Senior" and so<=70000
 
)
 
select
experience,accepted_candidates
from
t2
union all #上下两表拼接 就是题中需求
select
"Junior" experience,count(employee_id) accepted_candidates # 能招聘的 Junior 等级员工
from
t1
where
experience = "Junior" and so<=(select m from t2)

自己思路：找出高级和低级的生成临时表，然后用变量from t,@var=0,然后对高级和低级临时表的工资逐行加，再筛选出低于预备工资的数据，得到高级多少人并且一共花多少，再和低级临时表连接，条件是高级的总工资+低级总工资<预算，得到低级多少人和低级总工资

2020 简单

在这里插入图片描述

简单 别人的答案

# Write your MySQL query statement below
with new_table as(
    select  DISTINCT account_id from Subscriptions where 
    date_format(start_date,'%Y') = '2021' or
    date_format(end_date,'%Y') = '2021' 
)
select count(*) accounts_count
from Streams S right join new_table N
on N.account_id = S.account_id
where substring(stream_date,1,4) <> '2021'

1	用year函数，or, not in，子查询就可以了

2026 简单

1	简单但我只想到了select是算出比率，然后再select

在这里插入图片描述

# Write your MySQL query statement below
SELECT problem_id
FROM Problems
#这里不要用round四舍五入
WHERE likes/(likes+dislikes)<0.60
order by problem_id asc

2041 简单

简单

2051

在这里插入图片描述

# Write your MySQL query statement below
select M.member_id,M.name,(
case
    when count(V.visit_id) = 0 then 'Bronze'
    when count(P.visit_id)/count(V.visit_id) < 0.5 then 'Silver'
    when count(P.visit_id)/count(V.visit_id) <0.8 then 'Gold'
    else 'Diamond'
    end) AS category
from Members M left join Visits V
on M.member_id = V.member_id 
left join Purchases P 
on P.visit_id = V.visit_id
group by M.member_id

2066.账户余额 sum()over

表名: Transactions

+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id  | int  |
| day         | date |
| type        | ENUM |
| amount      | int  |
+-------------+------+
(account_id, day) 是该Transactions表的主键.
表中的每行数据表示一次交易的信息, 包括此次交易的账号(account_id), 交易类型(type), 交易发生时间(day), 交易发生金额(amount).
其中交易类型(type)字段包括了两种行为：存入 ('Deposit'), 取出('Withdraw').
 

请写出能够返回用户每次交易完成后的账户余额. 我们约定所有用户在进行交易前的账户余额都为0， 并且保证所有交易行为后的余额不为负数。

返回的结果请依次按照 账户（account_id), 日期( day ) 进行升序排序 .

查询结果的格式请参照以下测试样例.

# Write your MySQL query statement below
SELECT
    account_id,
    day,
    SUM(IF(type = 'Deposit', amount, -amount)) OVER (
        PARTITION BY account_id
        ORDER BY day
    ) AS balance
FROM Transactions
ORDER BY 1, 2;

2072.赢得比赛的大学

表： NewYork
+-------------+------+
| Column Name | Type |
+-------------+------+
| student_id  | int  |
| score       | int  |
+-------------+------+
在 SQL 中，student_id 是这个表的主键。
每一行包含纽约大学 (New York University) 中一名学生一次考试的成绩。
 
表： California
+-------------+------+
| Column Name | Type |
+-------------+------+
| student_id  | int  |
| score       | int  |
+-------------+------+
在 SQL 中，student_id 是这个表的主键。
每一行包含加州大学 (California University) 中一名学生一次考试的成绩。
 
纽约大学和加州大学之间举行了一场比赛。这场比赛由两所大学中相同数量的学生参加。拥有更多优秀学生的大学赢得这场比赛。如果两所大学的优秀学生数量相同，则这场比赛平局。
优秀学生是指在考试中获得 90% 或更高成绩的学生。
返回：

"New York University" 若纽约大学赢得这场比赛。
"California University" 若加州大学赢得这场比赛。
"No Winner" 若这场比赛平局。

# Write your MySQL query statement below
SELECT
    CASE
        WHEN n1.cnt > n2.cnt THEN 'New York University'
        WHEN n1.cnt < n2.cnt THEN 'California University'
        ELSE 'No Winner'
    END AS winner
FROM
    (SELECT COUNT(1) AS cnt FROM NewYork WHERE score >= 90) AS n1,
    (SELECT COUNT(1) AS cnt FROM California WHERE score >= 90) AS n2;

select case when n1>n2 then 'NewYork' when n1<n2 then 'California' else 'No Winner' end AS winner
(
select count(1) n1,(select count(1)
from California
where score>=90) n2
from NewYork
where score>=90
) t

2084.为订单类型为 0的客户删除类型为 1的订单

活动表: Orders
+-------------+------+
| Column Name | Type |
+-------------+------+
| order_id    | int  | 
| customer_id | int  |
| order_type  | int  | 
+-------------+------+
order_id是此表的主键列。
此表的每一行都表示订单的ID、订购该订单的客户的ID以及订单类型。
订单可以是类型0或类型1。

编写SQL查询以根据以下条件报告所有订单：

如果客户至少有一个类型为0的订单，则不要报告该客户的任何类型为1的订单。
否则，报告客户的所有订单。
按任意顺序返回结果表。
查询结果格式如下例所示。

# Write your MySQL query statement below
WITH
    T AS (
        SELECT DISTINCT customer_id
        FROM Orders
        WHERE order_type = 0
    )
SELECT *
FROM Orders AS o
WHERE order_type = 0 OR NOT EXISTS (SELECT 1 FROM T AS t WHERE t.customer_id = o.customer_id);

WITH
    T AS (
        SELECT DISTINCT customer_id
        FROM Orders
        WHERE order_type = 0
    )
SELECT *
FROM Orders AS o
WHERE order_type = 0 OR customer_id NOT IN (SELECT * FROM T);

1
2
3

也可以使用union

订单类型为0的并上客户id不为订单类型为0的

2118.建立方程 GROUP_CONCAT CONCAT 游标

表: Terms
+-------------+------+
| Column Name | Type |
+-------------+------+
| power       | int  |
| factor      | int  |
+-------------+------+
power 是该表具有唯一值的列。
该表的每一行包含关于方程的一项的信息。
power 是范围为 [0, 100] 的整数。
factor 是范围为 [-100,100] 的整数，且不能为零。
 
你有一个非常强大的程序，可以解决世界上任何单变量的方程。传递给程序的方程必须格式化如下:

左边 (LHS) 应该包含所有的术语。
右边 (RHS) 应该是零。
LHS 的每一项应遵循 "<sign><fact>X^<pow>" 的格式，其中:
<sign> 是 "+" 或者 "-"。
<fact> 是 factor 的 绝对值。
<pow> 是 power 的值。
如果幂是 1, 不要加上 "^<pow>".
例如, 如果 power = 1 并且 factor = 3, 将有 "+3X"。
如果幂是 0, 不要加上 "X" 和 "^<pow>".
例如, 如果 power = 0 并且 factor = -3, 将有 "-3"。
LHS 中的幂应该按 降序排序。
编写一个解决方案来构建方程。

结果格式如下所示。

示例 1:
输入: 
Terms 表:
+-------+--------+
| power | factor |
+-------+--------+
| 2     | 1      |
| 1     | -4     |
| 0     | 2      |
+-------+--------+
输出: 
+--------------+
| equation     |
+--------------+
| +1X^2-4X+2=0 |
+--------------+

# Write your MySQL query statement below
WITH
    T AS (
        SELECT
            power,
            CASE power
                WHEN 0 THEN IF(factor > 0, CONCAT('+', factor), factor)
                WHEN 1 THEN CONCAT(
                    IF(factor > 0, CONCAT('+', factor), factor),
                    'X'
                )
                ELSE CONCAT(
                    IF(factor > 0, CONCAT('+', factor), factor),
                    'X^',
                    power
                )
            END AS it
        FROM Terms
    )
SELECT
    CONCAT(GROUP_CONCAT(it ORDER BY power DESC SEPARATOR ""), '=0') AS equation
FROM T;

用游标应该很简单
set @s = '';
declare my_cursor cursor for select power,factor from Terms order by power desc;
open my_cursor;
fetch my_cursor into @power,@factor
while @@fetch_status=0 
do 
if @facter > 0 then
	set @facter = concat("+",@facter)
end if
if @power = 0 then
	set @power = @facter
else if @power = 1 then
	set @power = concat(@facter,'X')
else if @power > 1 then
	set @power = concat(@facter,'X^',@power)
end if
set @s=concat(@s,@power)
fetch my_cursor into @power,@factor
end while
close my_cursor;
select

2142.每辆车的乘客人数I lag

表: Buses

+--------------+------+
| Column Name  | Type |
+--------------+------+
| bus_id       | int  |
| arrival_time | int  |
+--------------+------+
bus_id 是该表的主键。
该表的每一行都包含关于 LeetCode 站点的公交车到达时间的信息。
不会出现两辆公交车会同时到达。
 

表: Passengers

+--------------+------+
| Column Name  | Type |
+--------------+------+
| passenger_id | int  |
| arrival_time | int  |
+--------------+------+
passenger_id 是该表的主键。
该表的每一行都包含乘客到达 LeetCode 站的时间信息。

公交车和乘客到达 LeetCode 站。如果一辆公交车在时间 tbus 到站，乘客在时间 tpassenger 到站，其中 tpassenger <= tbus，该乘客之前没有赶上任何公交车，则该乘客将搭乘该公交车。

编写一个 SQL 来查询使用每辆公交车的用户数量。

返回按 bus_id 升序排序 的结果表。

# Write your MySQL query statement below
SELECT
    bus_id,
    COUNT(passenger_id) - LAG(COUNT(passenger_id), 1, 0) OVER (
        ORDER BY b.arrival_time
    ) AS passengers_cnt
FROM
    Buses AS b
    LEFT JOIN Passengers AS p ON p.arrival_time <= b.arrival_time
GROUP BY 1
ORDER BY 1;

2153.每辆车的乘客人数 II 难游标

表: Buses
+--------------+------+
| Column Name  | Type |
+--------------+------+
| bus_id       | int  |
| arrival_time | int  |
| capacity     | int  |
+--------------+------+
bus_id 包含唯一的值。
该表的每一行都包含关于公交车到达 LeetCode 站点的时间和它的容量 (空座位的数量) 的信息。
不会出现两辆公交车同时到达，所有公交车的容量都是正整数。

表: Passengers
+--------------+------+
| Column Name  | Type |
+--------------+------+
| passenger_id | int  |
| arrival_time | int  |
+--------------+------+
passenger_id 包含唯一的值。
该表的每一行都包含乘客到达 LeetCode 站的时间信息。

公交车和乘客到达 LeetCode 站。如果一辆公交车在 tbus 时间点到达车站，乘客在 tpassenger 到达车站，其中 tpassenger <= tbus，而该乘客没有赶上任何公交车，则该乘客将搭乘该公交车。此外，每辆公交车都有一个容量。如果在公交车到站的那一刻，等待的乘客超过了它的载客量 capacity，只有 capacity 个乘客才会搭乘该公交车。

编写解决方案，报告使用每条总线的用户数量。
返回按 bus_id 升序排序 的结果表。

declare my_cursor cursor for 
select bus_id,capacity,count(1) num from Buses b inner join Passengers p
on b.arrival_time >= p.arrival_time
group by bus_id
order by b.arrival_time

open my_cursor;
set @sum=0;
fetch my_cursor into @bus_id,@capacity,@num;
while @@fetch_status=0 do
@v=@num-@sum
if @v=0 then
	@people=0;
else if @capacity>=@v then
	@people=@v;
else then
	@people=@capacity;
end if
select @bus_id,@people;
fetch my_cursor into @bus_id,@capacity,@num;
end while

close my_cursor;

3087.查找热门话题标签 SUBSTRING_INDEX

表：Tweets
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| tweet_id    | int     |
| tweet_date  | date    |
| tweet       | varchar |
+-------------+---------+
tweet_id 是这张表的主键 (值互不相同的列)。
这张表的每一行都包含 user_id, tweet_id, tweet_date 和 tweet。
编写一个解决方案来找到 2024 年 二月 的 前 3 热门话题 标签。每条推文只包含一个标签。

返回结果表，根据标签的数量和名称 降序 排序。

结果格式如下所示。

示例 1：
输入：
Tweets 表：
+---------+----------+----------------------------------------------+------------+
| user_id | tweet_id | tweet                                        | tweet_date |
+---------+----------+----------------------------------------------+------------+
| 135     | 13       | Enjoying a great start to the day! #HappyDay | 2024-02-01 |
| 136     | 14       | Another #HappyDay with good vibes!           | 2024-02-03 |
| 137     | 15       | Productivity peaks! #WorkLife                | 2024-02-04 |
| 138     | 16       | Exploring new tech frontiers. #TechLife      | 2024-02-04 |
| 139     | 17       | Gratitude for today's moments. #HappyDay     | 2024-02-05 |
| 140     | 18       | Innovation drives us. #TechLife              | 2024-02-07 |
| 141     | 19       | Connecting with nature's serenity. #Nature   | 2024-02-09 |
+---------+----------+----------------------------------------------+------------+
 
输出：
+-----------+--------------+
| hashtag   | hashtag_count|
+-----------+--------------+
| #HappyDay | 3            |
| #TechLife | 2            |
| #WorkLife | 1            |
+-----------+--------------+

# Write your MySQL query statement below
SELECT
    CONCAT('#', SUBSTRING_INDEX(SUBSTRING_INDEX(tweet, '#', -1), ' ', 1)) AS hashtag,
    COUNT(1) AS hashtag_count
FROM Tweets
WHERE DATE_FORMAT(tweet_date, '%Y%m') = '202402'
GROUP BY 1
ORDER BY 2 DESC, 1 DESC
LIMIT 3;

pandas

import pandas as pd


def find_trending_hashtags(tweets: pd.DataFrame) -> pd.DataFrame:
    # 过滤数据框以获取特定日期的数据
    tweets = tweets[tweets["tweet_date"].dt.strftime("%Y%m") == "202402"]

    # 提取 Hashtag
    tweets["hashtag"] = "#" + tweets["tweet"].str.extract(r"#(\w+)")

    # 统计 Hashtag 出现次数
    hashtag_counts = tweets["hashtag"].value_counts().reset_index()
    hashtag_counts.columns = ["hashtag", "hashtag_count"]

    # 根据出现次数降序排序 Hashtag
    hashtag_counts = hashtag_counts.sort_values(
        by=["hashtag_count", "hashtag"], ascending=[False, False]
    )

    # 返回前三个热门 Hashtag
    top_3_hashtags = hashtag_counts.head(3)

    return top_3_hashtags

难 3089.查找突发行为 DATE_ADD

表：Posts
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| post_id     | int     |
| user_id     | int     |
| post_date   | date    |
+-------------+---------+
post_id 是这张表的主键（有不同值的列）。
这张表的每一行包含 post_id，user_id 和 post_date。
编写一个解决方案来找到在 2024 年 2 月期间在发帖行为中表现出 突发行为 的用户。突发行为 指用户在 2024 年 2 月 存在一个 连续 7 天 的时段中发帖频率是其 平均 每周发帖频率的 至少两倍。

注意： 在你的统计中只包含 2 月 1 日 到 2 月 28 日，也就是说你应该把 2 月记为正好 4 周。

返回结果表，以 user_id 升序 排序。

结果格式如下所示。

示例：
输入：
Posts 表：
+---------+---------+------------+
| post_id | user_id | post_date  |
+---------+---------+------------+
| 1       | 1       | 2024-02-27 |
| 2       | 5       | 2024-02-06 |
| 3       | 3       | 2024-02-25 |
| 4       | 3       | 2024-02-14 |
| 5       | 3       | 2024-02-06 |
| 6       | 2       | 2024-02-25 |
+---------+---------+------------+

输出：
+---------+----------------+------------------+
| user_id | max_7day_posts | avg_weekly_posts |
+---------+----------------+------------------+
| 1       | 1              | 0.2500           |
| 2       | 1              | 0.2500           |
| 5       | 1              | 0.2500           |
+---------+----------------+------------------+

# Write your MySQL query statement below
WITH
    P AS (
        SELECT p1.user_id AS user_id, COUNT(1) AS cnt
        FROM
            Posts AS p1
            JOIN Posts AS p2
                ON p1.user_id = p2.user_id
                AND p2.post_date BETWEEN p1.post_date AND DATE_ADD(p1.post_date, INTERVAL 6 DAY)
        GROUP BY p1.user_id, p1.post_id
    ),
    T AS (
        SELECT user_id, COUNT(1) / 4 AS avg_weekly_posts
        FROM Posts
        WHERE post_date BETWEEN '2024-02-01' AND '2024-02-28'
        GROUP BY 1
    )
SELECT user_id, MAX(cnt) AS max_7day_posts, avg_weekly_posts
FROM
    P
    JOIN T USING (user_id)
GROUP BY 1
HAVING max_7day_posts >= avg_weekly_posts * 2
ORDER BY 1;

3103.查找热门话题标签II

表：Tweets
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| tweet_id    | int     |
| tweet_date  | date    |
| tweet       | varchar |
+-------------+---------+
tweet_id 是这张表的主键 (值互不相同的列)。
这张表的每一行都包含 user_id, tweet_id, tweet_date 和 tweet。
题目保证所有 tweet_date 都是 2024 年 2 月的合法日期。
编写一个解决方案来找到 2024 年 二月 的 前 3 热门话题 标签。一条推文可能含有 多个标签。

返回结果表，根据标签的数量和名称 降序 排序。

结果格式如下所示。

示例 1：
输入：
Tweets 表：
+---------+----------+------------------------------------------------------------+------------+
| user_id | tweet_id | tweet                                                      | tweet_date |
+---------+----------+------------------------------------------------------------+------------+
| 135     | 13       | Enjoying a great start to the day. #HappyDay #MorningVibes | 2024-02-01 |
| 136     | 14       | Another #HappyDay with good vibes! #FeelGood               | 2024-02-03 |
| 137     | 15       | Productivity peaks! #WorkLife #ProductiveDay               | 2024-02-04 |
| 138     | 16       | Exploring new tech frontiers. #TechLife #Innovation        | 2024-02-04 |
| 139     | 17       | Gratitude for today's moments. #HappyDay #Thankful         | 2024-02-05 |
| 140     | 18       | Innovation drives us. #TechLife #FutureTech                | 2024-02-07 |
| 141     | 19       | Connecting with nature's serenity. #Nature #Peaceful       | 2024-02-09 |
+---------+----------+------------------------------------------------------------+------------+
 
输出：
+-----------+-------+
| hashtag   | count |
+-----------+-------+
| #HappyDay | 3     |
| #TechLife | 2     |
| #WorkLife | 1     |
+-----------+-------+

pandas

def fun(Tweets:pd.DataFrame)->pd.DataFrame:
    tweets_feb_2024 = Tweets[Tweets["tweet_date"].between("2024-02-01", "2024-02-29")]
    hashtags = tweets_feb_2024["tweet"].str.findall(r"#\w+")
    all_hashtags = [tag for sublist in hashtags for tag in sublist]
    hashtag_counts = pd.Series(all_hashtags).value_counts().reset_index()
    hashtag_counts.columns = ["hashtag", "count"]
    hashtag_counts = hashtag_counts.sort_values(
        by=["count", "hashtag"], ascending=[False, False]
    )

    top_3_hashtags = hashtag_counts.head(3)

    return top_3_hashtags

难 3118.发生在周五的交易 III

表：Purchases
+---------------+------+
| Column Name   | Type |
+---------------+------+
| user_id       | int  |
| purchase_date | date |
| amount_spend  | int  |
+---------------+------+
(user_id, purchase_date, amount_spend) 是该表的主键(具有唯一值的列)。
purchase_date 的范围从 2023 年 11 月 1 日到 2023 年 11 月 30 日，并包括这两个日期。
每一行包含 user_id, purchase_date，和 amount_spend。

表：Users
+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id     | int  |
| membership  | enum |
+-------------+------+
user_id 是这张表的主键。
membership 是 ('Standard', 'Premium', 'VIP') 的枚举类型。
这张表的每一行表示 user_id 和会员类型。
编写一个解决方案来计算 Premium 和 VIP 会员在 2023 年 11 月 每周的周五 的 总花费。如果某个周五没有 Premium 或 VIP 会员购买，把它当作 0。

按照每月的周次序 升序 排列结果表，然后以 membership 升序 排序。

结果格式如下所示。

3126.服务器利用时间 FLOOR TIMESTAMPDIFF LEAD

表：Servers
+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| server_id      | int      |
| status_time    | datetime |
| session_status | enum     |
+----------------+----------+
(server_id, status_time, session_status) 是这张表的主键（有不同值的列的组合）。
session_status 是 ('start', 'stop') 的 ENUM (category)。
这张表的每一行包含 server_id, status_time 和 session_status。
编写一个解决方案来查找服务器 运行 的 总时间。输出应向下舍入为最接近的 整天数。

以 任意 顺序返回结果表。

结果格式如下所示。

示例：
输入：
Servers 表：
+-----------+---------------------+----------------+
| server_id | status_time         | session_status |
+-----------+---------------------+----------------+
| 3         | 2023-11-04 16:29:47 | start          |
| 3         | 2023-11-05 01:49:47 | stop           |
| 3         | 2023-11-25 01:37:08 | start          |
| 3         | 2023-11-25 03:50:08 | stop           |
| 1         | 2023-11-13 03:05:31 | start          |
| 1         | 2023-11-13 11:10:31 | stop           |
| 4         | 2023-11-29 15:11:17 | start          |
| 4         | 2023-11-29 15:42:17 | stop           |
| 4         | 2023-11-20 00:31:44 | start          |
| 4         | 2023-11-20 07:03:44 | stop           |
| 1         | 2023-11-20 00:27:11 | start          |
| 1         | 2023-11-20 01:41:11 | stop           |
| 3         | 2023-11-04 23:16:48 | start          |
| 3         | 2023-11-05 01:15:48 | stop           |
| 4         | 2023-11-30 15:09:18 | start          |
| 4         | 2023-11-30 20:48:18 | stop           |
| 4         | 2023-11-25 21:09:06 | start          |
| 4         | 2023-11-26 04:58:06 | stop           |
| 5         | 2023-11-16 19:42:22 | start          |
| 5         | 2023-11-16 21:08:22 | stop           |
+-----------+---------------------+----------------+
输出：
+-------------------+
| total_uptime_days |
+-------------------+
| 1                 |
+-------------------+

select floor(sum(TIMESTAMPDIFF(SECOND,s2.status_time,s1.status_time))/86400) total_uptime_days
from Servers s1,(select *,row_number() over(order by server_id) rk
from Servers) s2
where s1.session_status = 'start' and s1.rk +1 = s2.rk and s2.session_status = 'stop'

# Write your MySQL query statement below
WITH
    T AS (
        SELECT
            session_status,
            status_time,
            LEAD(status_time) OVER (
                PARTITION BY server_id
                ORDER BY status_time
            ) AS next_status_time
        FROM Servers
    )
SELECT FLOOR(SUM(TIMESTAMPDIFF(SECOND, status_time, next_status_time)) / 86400) AS total_uptime_days
FROM T
WHERE session_status = 'start';

3140.连续空余座位||

表：Cinema
+-------------+------+
| Column Name | Type |
+-------------+------+
| seat_id     | int  |
| free        | bool |
+-------------+------+
seat_id 是这张表中的自增列。
这张表的每一行表示第 i 个作为是否空余。1 表示空余，而 0 表示被占用。
编写一个解决方案来找到电影院中 最长的空余座位 的 长度。

注意：
保证 最多有一个 最长连续序列。
如果有 多个 相同长度 的连续序列，将它们全部输出。
返回结果表以 first_seat_id 升序排序。

结果表的格式如下所示。

示例：
输入：
Cinema 表：
+---------+------+
| seat_id | free |
+---------+------+
| 1       | 1    |
| 2       | 0    |
| 3       | 1    |
| 4       | 1    |
| 5       | 1    |
+---------+------+
输出：
+-----------------+----------------+-----------------------+
| first_seat_id   | last_seat_id   | consecutive_seats_len |
+-----------------+----------------+-----------------------+
| 3               | 5              | 3                     |
+-----------------+----------------+-----------------------+

with t as (
select seat_id,seat_id-rk s
from (
select *,row_number() over(order by seat_id) rk
from Cinema
where free = 1) t
),p as
(
select min(seat_id) first_seat_id,max(seat_id) last_seat_id,count(1) consecutive_seats_len,dense_rank() over(order by count(1) desc) rk
from t
group by s
)

select first_seat_id, last_seat_id, consecutive_seats_len
from p
where rk=1

# Write your MySQL query statement below
WITH
    T AS (
        SELECT
            *,
            seat_id - (RANK() OVER (ORDER BY seat_id)) AS gid
        FROM Cinema
        WHERE free = 1
    ),
    P AS (
        SELECT
            MIN(seat_id) AS first_seat_id,
            MAX(seat_id) AS last_seat_id,
            COUNT(1) AS consecutive_seats_len,
            RANK() OVER (ORDER BY COUNT(1) DESC) AS rk
        FROM T
        GROUP BY gid
    )
SELECT first_seat_id, last_seat_id, consecutive_seats_len
FROM P
WHERE rk = 1
ORDER BY 1;

3150.无效的推文I

表：Tweets
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| tweet_id       | int     |
| content        | varchar |
+----------------+---------+
tweet_id 是这个表的主键（有不同值的列）。
这个表包含某社交媒体 App 中所有的推文。
编写一个解决方案来找到 无效的推文。如果一条推文满足下面 任一 条件会被认为无效：
长度超过 140 个字符。
有超过 3 次提及。
有超过 3 个标签。
以 tweet_id 升序 返回结果表。

查询结果格式如下所示：
示例：
输入：
Tweets 表：
  +----------+-----------------------------------------------------------------------------------+
  | tweet_id | content                                                                           |
  +----------+-----------------------------------------------------------------------------------+
  | 1        | Traveling, exploring, and living my best life @JaneSmith @SaraJohnson @LisaTaylor |
  |          | @MikeBrown #Foodie #Fitness #Learning                                             | 
  | 2        | Just had the best dinner with friends! #Foodie #Friends #Fun                      |
  | 4        | Working hard on my new project #Work #Goals #Productivity #Fun                    |
  +----------+-----------------------------------------------------------------------------------+
  
输出：
  +----------+
  | tweet_id |
  +----------+
  | 1        |
  | 4        |
  +----------+

# Write your MySQL query statement below
SELECT tweet_id
FROM Tweets
WHERE
    LENGTH(content) > 140
    OR (LENGTH(content) - LENGTH(REPLACE(content, '@', ''))) > 3
    OR (LENGTH(content) - LENGTH(REPLACE(content, '#', ''))) > 3
ORDER BY 1;

难 3156.员工任务持续时间和并发任务

表：Tasks
+---------------+----------+
| Column Name   | Type     |
+---------------+----------+
| task_id       | int      |
| employee_id   | int      |
| start_time    | datetime |
| end_time      | datetime |
+---------------+----------+
(task_id, employee_id) 是这张表的主键。
这张表的每一行包含任务标识，员工标识和每个任务的开始和结束时间。
编写一个解决方案来查找 每个 员工的任务 总持续时间 以及员工在任何时间点处理的 最大并发任务数。总时长应该 舍入 到最近的 整小时。
7
返回结果表以 employee_id 升序 排序。

结果格式如下所示。

示例：
输入：
Tasks 表：
+---------+-------------+---------------------+---------------------+
| task_id | employee_id | start_time          | end_time            |
+---------+-------------+---------------------+---------------------+
| 1       | 1001        | 2023-05-01 08:00:00 | 2023-05-01 09:00:00 |
| 2       | 1001        | 2023-05-01 08:30:00 | 2023-05-01 10:30:00 |
| 3       | 1001        | 2023-05-01 11:00:00 | 2023-05-01 12:00:00 |
| 7       | 1001        | 2023-05-01 13:00:00 | 2023-05-01 15:30:00 |
| 4       | 1002        | 2023-05-01 09:00:00 | 2023-05-01 10:00:00 |
| 5       | 1002        | 2023-05-01 09:30:00 | 2023-05-01 11:30:00 |
| 6       | 1003        | 2023-05-01 14:00:00 | 2023-05-01 16:00:00 |
+---------+-------------+---------------------+---------------------+
输出：
+-------------+------------------+----------------------+
| employee_id | total_task_hours | max_concurrent_tasks |
+-------------+------------------+----------------------+
| 1001        | 6                | 2                    |
| 1002        | 2                | 2                    |
| 1003        | 2                | 1                    |
+-------------+------------------+----------------------+

3262.查找重叠的班次

表：EmployeeShifts

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| employee_id      | int     |
| start_time       | time    |
| end_time         | time    |
+------------------+---------+
(employee_id, start_time) 是此表的唯一主键。
这张表包含员工的排班工作，包括特定日期的开始和结束时间。
编写一个解决方案来为每个员工计算 重叠排班 的数量。如果一个排班的 end_time 比另一个排班的 start_time 更晚 则认为两个排班重叠。
返回结果表以 employee_id 升序 排序。

查询结果格式如下所示。

示例：
输入：
EmployeeShifts 表：
+-------------+------------+----------+
| employee_id | start_time | end_time |
+-------------+------------+----------+
| 1           | 08:00:00   | 12:00:00 |
| 1           | 11:00:00   | 15:00:00 |
| 1           | 14:00:00   | 18:00:00 |
| 2           | 09:00:00   | 17:00:00 |
| 2           | 16:00:00   | 20:00:00 |
| 3           | 10:00:00   | 12:00:00 |
| 3           | 13:00:00   | 15:00:00 |
| 3           | 16:00:00   | 18:00:00 |
| 4           | 08:00:00   | 10:00:00 |
| 4           | 09:00:00   | 11:00:00 |
+-------------+------------+----------+
输出：
+-------------+--------------------+
| employee_id | overlapping_shifts |
+-------------+--------------------+
| 1           | 2                  |
| 2           | 1                  |
| 4           | 1                  |
+-------------+--------------------+

SELECT
    t1.employee_id,
    COUNT(*) AS overlapping_shifts
FROM
    EmployeeShifts t1
    JOIN EmployeeShifts t2
        ON t1.employee_id = t2.employee_id
        AND t1.start_time < t2.start_time
        AND t1.end_time > t2.start_time
GROUP BY 1
HAVING overlapping_shifts > 0
ORDER BY 1;

难 3268.查找重善的班次II

表：EmployeeShifts
+------------------+----------+
| Column Name      | Type     |
+------------------+----------+
| employee_id      | int      |
| start_time       | datetime |
| end_time         | datetime |
+------------------+----------+
(employee_id, start_time) 是此表的唯一主键。
这张表包含员工的排班工作，包括特定日期的开始和结束时间。
编写一个解决方案来为每个员工分析重叠排班。如果两个排班在 同一天 且一个排班的 end_time 比另一个排班的 start_time 更晚 则认为两个排班重叠。

对于 每个员工，计算如下内容：

任何 给定时间 的 最多重叠 班次数。
所有重叠班次的 总持续时间，以分钟为单位。
返回结果表以 employee_id 升序 排序。

查询结果格式如下所示。

示例：
输入：
EmployeeShifts 表：
+-------------+---------------------+---------------------+
| employee_id | start_time          | end_time            |
+-------------+---------------------+---------------------+
| 1           | 2023-10-01 09:00:00 | 2023-10-01 17:00:00 |
| 1           | 2023-10-01 15:00:00 | 2023-10-01 23:00:00 |
| 1           | 2023-10-01 16:00:00 | 2023-10-02 00:00:00 |
| 2           | 2023-10-01 09:00:00 | 2023-10-01 17:00:00 |
| 2           | 2023-10-01 11:00:00 | 2023-10-01 19:00:00 |
| 3           | 2023-10-01 09:00:00 | 2023-10-01 17:00:00 |
+-------------+---------------------+---------------------+
输出：
+-------------+---------------------------+------------------------+
| employee_id | max_overlapping_shifts    | total_overlap_duration |
+-------------+---------------------------+------------------------+
| 1           | 3                         | 600                    |
| 2           | 2                         | 360                    |
| 3           | 1                         | 0                      |
+-------------+---------------------------+------------------------+

~~~



#### 3278.寻找数据科学家职位的候选人II

候选人必须拥有项目所需的所有技能。
为每个候选人-项目对计算如下的分数：
从 100 分开始。
对于每一个技能，当熟练程度 > 重要性加 10 分。
对于每一个技能，当熟练程度 < 重要性减 5 分。
如果候选人的技能熟练程度等于项目的技能重要性，则分数保持不变
仅包括每个项目的最佳候选人（最高分）。如果相同，选择有更小 candidate_id 的候选人。如果一个项目没有适合的候选人，不要返回那个项目。

返回结果表以 project_id 升序排序。

输出格式如下所示。

示例：
输入：
Candidates 表：
+————–+———–+————-+
| candidate_id | skill | proficiency |
+————–+———–+————-+
| 101 | Python | 5 |
| 101 | Tableau | 3 |
| 101 | PostgreSQL| 4 |
| 101 | TensorFlow| 2 |
| 102 | Python | 4 |
| 102 | Tableau | 5 |
| 102 | PostgreSQL| 4 |
| 102 | R | 4 |
| 103 | Python | 3 |
| 103 | Tableau | 5 |
| 103 | PostgreSQL| 5 |
| 103 | Spark | 4 |
+————–+———–+————-+
Projects 表：
+————-+———–+————+
| project_id | skill | importance |
+————-+———–+————+
| 501 | Python | 4 |
| 501 | Tableau | 3 |
| 501 | PostgreSQL| 5 |
| 502 | Python | 3 |
| 502 | Tableau | 4 |
| 502 | R | 2 |
+————-+———–+————+
输出：
+————-+————–+——-+
| project_id | candidate_id | score |
+————-+————–+——-+
| 501 | 101 | 105 |
| 502 | 102 | 130 |
+————-+————–+——-+
解释：

对于项目 501, 候选人 101 有最高的 105 分。所有其他的候选人有相同的分数，但候选人 101 有比他们更小的 candidate_id。
对于项目 502，候选人 102 有最高的 130 分。
输出表以 project_id 升序排序。

WITH
S AS (
SELECT
candidate_id,
project_id,
COUNT(*) matched_skills,
SUM(
CASE
WHEN proficiency > importance THEN 10
WHEN proficiency < importance THEN -5
ELSE 0
END
) + 100 AS score
FROM
Candidates
JOIN Projects USING (skill)
GROUP BY 1, 2
),
T AS (
SELECT project_id, COUNT(1) required_skills
FROM Projects
GROUP BY 1
),
P AS (
SELECT
project_id,
candidate_id,
score,
RANK() OVER (
PARTITION BY project_id
ORDER BY score DESC, candidate_id
) rk
FROM
S
JOIN T USING (project_id)
WHERE matched_skills = required_skills
)
SELECT project_id, candidate_id, score
FROM P
WHERE rk = 1
ORDER BY 1;




#### 3328.查找每个州的城市 I I group_concat

用逗号分隔字符串组合每一个州的所有城市。
只显示有至少 3 个城市的州。
只显示至少有一个城市以与州名相同字母开头的州。
返回结果表以字母匹配城市的数量降序排序，然后按州名称升序排序的结果表。

结果格式如下所示。

Write your MySQL query statement below

SELECT
state,
GROUP_CONCAT(city ORDER BY city SEPARATOR ‘, ‘) AS cities,
COUNT(
CASE
WHEN LEFT(city, 1) = LEFT(state, 1) THEN 1
END
) AS matching_letter_count
FROM cities
GROUP BY 1
HAVING COUNT(city) >= 3 AND matching_letter_count > 0
ORDER BY 3 DESC, 1;




#### 3368.首字母大写 recursive

把每个单词的首字母变成大写
其它字母保持小写
保留所有现有空格
注意：content_text 中没有特殊字符。

返回结果表，同时包含原来的 content_text 以及将所有单词首字母变成大写的修改后文本。
结果格式如下所示。

WITH RECURSIVE
capitalized_words AS (
SELECT
content_id,
content_text,
SUBSTRING_INDEX(content_text, ‘ ‘, 1) AS word,
SUBSTRING(
content_text,
LENGTH(SUBSTRING_INDEX(content_text, ‘ ‘, 1)) + 2
) AS remaining_text,
CONCAT(
UPPER(LEFT(SUBSTRING_INDEX(content_text, ‘ ‘, 1), 1)),
LOWER(SUBSTRING(SUBSTRING_INDEX(content_text, ‘ ‘, 1), 2))
) AS processed_word
FROM user_content
UNION ALL
SELECT
c.content_id,
c.content_text,
SUBSTRING_INDEX(c.remaining_text, ‘ ‘, 1),
SUBSTRING(c.remaining_text, LENGTH(SUBSTRING_INDEX(c.remaining_text, ‘ ‘, 1)) + 2),
CONCAT(
c.processed_word,
‘ ‘,
CONCAT(
UPPER(LEFT(SUBSTRING_INDEX(c.remaining_text, ‘ ‘, 1), 1)),
LOWER(SUBSTRING(SUBSTRING_INDEX(c.remaining_text, ‘ ‘, 1), 2))
)
)
FROM capitalized_words c
WHERE c.remaining_text != ‘’
)
SELECT
content_id,
content_text AS original_text,
MAX(processed_word) AS converted_text
FROM capitalized_words
GROUP BY 1, 2;

1
2
3


#### 3374.首字母大写II recursive

将每个单词的第一个字母转换为大写，其余字母保持小写。
特殊处理包含特殊字符的单词：
对于用短横-连接的词语，两个部份都应该大写（例如，top-rated → Top-Rated）
所有其他格式和空格应保持不变

with recursive cte as(
select content_id,
content_text ‘original_text’,
case when instr(substring_index(content_text,’ ‘,1),’-‘)>0
then upper(substring_index(content_text,’ ‘,1))
else concat(upper(left(substring_index(content_text,’ ‘,1),1)),
lower(substring(substring_index(content_text,’ ‘,1),2))) end processed_w,
substring(content_text,length(substring_index(content_text,’ ‘,1))+2) remainStr
from user_content
union all
select content_id,
content_text,
concat(processed_w,’ ‘,
case when instr(substring_index(remainStr,’ ‘,1),’-‘)>0
then upper(substring_index(remainStr,’ ‘,1))
else concat(upper(left(substring_index(remainStr,’ ‘,1),1)),
lower(substring(substring_index(remainStr,’ ‘,1),2))) end ),
substring(remainStr,length(substring_index(remainStr,’ ‘,1))+2) remainStr
from cte
where remain!=’’
)

select content_id,original_text,max(processed_w) converted_text
from cte
group by content_id




#### 难 3390.最长团队传球连击

成功连击的定义为连续传球，其中：
pass_from 和 pass_to 表示的队员来自同一队伍
当出现以下情况时，连击就会中断：
传球被截获（由对方球队的一名球员接住）
返回结果表以 team_name 升序排序

结果格式如下所示。

示例：
输入：
Teams 表：
+———–+———–+
| player_id | team_name |
+———–+———–+
| 1 | Arsenal |
| 2 | Arsenal |
| 3 | Arsenal |
| 4 | Arsenal |
| 5 | Chelsea |
| 6 | Chelsea |
| 7 | Chelsea |
| 8 | Chelsea |
+———–+———–+
Passes 表：
+———–+————+———+
| pass_from | time_stamp | pass_to |
+———–+————+———+
| 1 | 00:05 | 2 |
| 2 | 00:07 | 3 |
| 3 | 00:08 | 4 |
| 4 | 00:10 | 5 |
| 6 | 00:15 | 7 |
| 7 | 00:17 | 8 |
| 8 | 00:20 | 6 |
| 6 | 00:22 | 5 |
| 1 | 00:25 | 2 |
| 2 | 00:27 | 3 |
+———–+————+———+
输出：
+———–+—————-+
| team_name | longest_streak |
+———–+—————-+
| Arsenal | 3 |
| Chelsea | 4 |
+———–+—————-+

使用游标应该简单




#### 3401.寻找环形礼物交换链 regexp 难

环形链被定义为一系列交换，其中：

每位员工都正好向另一位员工赠送一份礼物。
每位员工都正好从另一位员工那里收到一份礼物。
交换形成一个连续的循环（即员工 A 给 B 一份礼物，B 给 C，C 再给 A）。
返回结果以链的长度和总礼物价值降序排序。

结果格式如下所示。

示例：
输入：
SecretSanta 表：
+———-+————-+————+
| giver_id | receiver_id | gift_value |
+———-+————-+————+
| 1 | 2 | 20 |
| 2 | 3 | 30 |
| 3 | 1 | 40 |
| 4 | 5 | 25 |
| 5 | 4 | 35 |
+———-+————-+————+
输出：
+———-+————–+——————+
| chain_id | chain_length | total_gift_value |
+———-+————–+——————+
| 1 | 3 | 90 |
| 2 | 2 | 60


~~~.
with recursive cte as(
select giver_id,receiver_id,gift_value,giver_id start_id from SecretSanta
union all
select SecretSanta.giver_id,SecretSanta.receiver_id,SecretSanta.gift_value gift_value,cte.start_id
from SecretSanta inner join cte 
on cte.receiver_id = SecretSanta.giver_id and cte.start_id != SecretSanta.receiver_id
)

select distinct count(*) chain_length,sum(gift_value) total_gift_value 
from cte
group by start_id

WITH RECURSIVE cte AS (
    SELECT 
        giver_id, 
        receiver_id, 
        gift_value,
        giver_id AS start, 
        giver_id AS min_id, 
        gift_value AS total_value, 
        1 AS length
    FROM SecretSanta
    UNION ALL
    SELECT 
        s.giver_id, 
        s.receiver_id, 
        s.gift_value,
        cte.start,
        LEAST(cte.min_id, s.giver_id) AS min_id,
        cte.total_value + s.gift_value AS total_value,
        cte.length + 1 AS length
    FROM cte
    JOIN SecretSanta s ON cte.receiver_id = s.giver_id
    WHERE cte.receiver_id <> cte.start
),
rings AS (
    SELECT 
        min_id AS original_chain_id, 
        length AS chain_length, 
        total_value AS total_gift_value
    FROM cte
    WHERE receiver_id = start
        AND start = min_id
)
SELECT 
    ROW_NUMBER() OVER (ORDER BY chain_length DESC, total_gift_value DESC) AS chain_id,
    chain_length,
    total_gift_value
FROM rings
ORDER BY chain_length DESC, total_gift_value DESC;

3415.查找具有三个连续数字的产品 regexp

表：Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| name        | varchar |
+-------------+---------+
product_id 是这张表的唯一主键。
这张表的每一行包含产品的 ID 和名字。
编写一个解决方案来找到所有名字中包含 三位连续数字 且无连续三位以上数字的所有 产品。

返回结果表以 product_id 升序 排序。

结果格式如下所示。

# Write your MySQL query statement below
SELECT product_id, name
FROM Products
WHERE name REGEXP '(^|[^0-9])[0-9]{3}([^0-9]|$)'
ORDER BY 1;

select *
from Products
where str regexp '[^0-9][0-9]{3}[^0-9]'   -- 匹配 %123%
    or str regexp '[^0-9][0-9]{3}$'             -- 匹配 %123
    or str regexp '^[0-9]{3}[^0-9]'             -- 匹配 123%

3421.查找进步的学生

表：Scores

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| student_id  | int     |
| subject     | varchar |
| score       | int     |
| exam_date   | varchar |
+-------------+---------+
(student_id, subject, exam_date) 是这张表的主键。
每一行包含有关学生在特定考试日期特定科目成绩的信息。分数范围从 0 到 100（包括边界）。
编写一个解决方案来查找 进步的学生。如果 同时 满足以下两个条件，则该学生被认为是进步的：

在 同一科目 至少参加过两个不同日期的考试。
他们在该学科 最近的分数 比他们 第一次该学科考试的分数更高。
返回结果表以 student_id，subject 升序 排序。

with rk as (
    select *, rank() over(partition by student_id,subject order by exam_date) r1,
        rank() over(partition by student_id,subject order by exam_date desc) r2
    from Scores
)
select a.student_id, a.subject, a.score first_score, b.score latest_score
from rk a join rk b on
    a.student_id=b.student_id and a.subject=b.subject and
    a.r1=1 and b.r2=1 and a.r1!=a.r2 and a.score<b.score

1	先筛选出一门学科至少参加过两次的人，对这些人的这些学科再按照上面那样做也可以

3436.查找合法邮箱 regexp

表：Users

+-----------------+---------+
| Column Name     | Type    |
+-----------------+---------+
| user_id         | int     |
| email           | varchar |
+-----------------+---------+
(user_id) 是这张表的唯一主键。
每一行包含用户的唯一 ID 和邮箱地址。
编写一个解决方案来查找所有 合法邮箱地址。一个合法的邮箱地址符合下述条件：

只包含一个 @ 符号。
以 .com 结尾。
@ 符号前面的部分只包含 字母数字 字符和 下划线。
@ 符号后面与 .com 前面的部分 包含 只有字母 的域名。
返回结果表以 user_id 升序 排序。

1
2
3

select user_id, email from Users
where email rlike '^[a-zA-Z0-9_]+@[a-zA-Z]+\\.com$'
order by user_id asc

3451.查找无效的 IP 地址 substring_index cast regepx replace

表：logs
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| log_id      | int     |
| ip          | varchar |
| status_code | int     |
+-------------+---------+
log_id 是这张表的唯一主键。
每一行包含服务器访问日志信息，包括 IP 地址和 HTTP 状态码。
编写一个解决方案来查找 无效的 IP 地址。一个 IPv4 地址如果满足以下任何条件之一，则无效：

任何 8 位字节中包含大于 255 的数字
任何 8 位字节中含有 前导零（如 01.02.03.04）
少于或多于 4 个 8 位字节
返回结果表分别以 invalid_count，ip 降序 排序。

结果格式如下所示。

示例：
输入：

logs 表：
+--------+---------------+-------------+
| log_id | ip            | status_code | 
+--------+---------------+-------------+
| 1      | 192.168.1.1   | 200         | 
| 2      | 256.1.2.3     | 404         | 
| 3      | 192.168.001.1 | 200         | 
| 4      | 192.168.1.1   | 200         | 
| 5      | 192.168.1     | 500         | 
| 6      | 256.1.2.3     | 404         | 
| 7      | 192.168.001.1 | 200         | 
+--------+---------------+-------------+
输出：
+---------------+--------------+
| ip            | invalid_count|
+---------------+--------------+
| 256.1.2.3     | 2            |
| 192.168.001.1 | 2            |
| 192.168.1     | 1            |
+---------------+--------------+

# Write your MySQL query statement below

select ip, count(*) as invalid_count
from logs
WHERE ip like "%.0%" or ip like "0%" 
or length(ip) - length(replace(ip, '.', '')) != 3 
or CAST(substring_index(ip, ".", 1) AS UNSIGNED)>255 
or CAST(substring_index(substring_index(ip, ".", 2), ".", -1) AS UNSIGNED) >255 
or CAST(substring_index(substring_index(ip, ".", 3), ".", -1) AS UNSIGNED) >255 
or CAST(substring_index(ip, ".", -1) AS UNSIGNED) >255
GROUP BY ip
ORDER BY invalid_count DESC, ip DESC;

一个0-255的数字字符串，匹配字符是这样的：
250-255：25[0-5]
200-249：2[0-4][0-9]
100-199：1[0-9]{2}
10-99（无前导零）：[1-9][0-9]
0-9：[0-9]

串起来就是：
25[0-5]|2[0-4][0-9]|1[0-9]{2}|[1-9][0-9]|[0-9]

这样的4个数字+三个点拼起来就可以了，可以写成一个数字字符串+一个点重复三次，加一个单独的数字字符串即可，即：
'^((25[0-5]|2[0-4][0-9]|1[0-9]{2}|[1-9][0-9]|[0-9])\\.){3}(25[0-5]|2[0-4][0-9]|1[0-9]{2}|[1-9][0-9]|[0-9])$'
最终做一个正则表达式匹配，完了分组输出即可。


SELECT ip, count(ip) as invalid_count            
FROM logs 
WHERE ip not REGEXP '^((25[0-5]|2[0-4][0-9]|1[0-9]{2}|[1-9][0-9]|[0-9])\\.){3}(25[0-5]|2[0-4][0-9]|1[0-9]{2}|[1-9][0-9]|[0-9])$'
group by ip
order by invalid_count desc, ip desc

3465.查找具有有效序列号的产品 REGEXP

表：products

+--------------+------------+
| Column Name  | Type       |
+--------------+------------+
| product_id   | int        |
| product_name | varchar    |
| description  | varchar    |
+--------------+------------+
(product_id) 是这张表的唯一主键。
这张表的每一行表示一个产品的唯一 ID，名字和描述。
编写一个解决方案来找到所有描述中 包含一个有效序列号 模式的产品。一个有效序列号符合下述规则：

以 SN 字母开头（区分大小写）。
后面有恰好 4 位数字。
接着是一个短横（-）， 短横后面还有另一组 4 位数字
序列号必须在描述内（可能不在描述的开头）
返回结果表以 product_id 升序 排序。

1	select product_id, product_name, description from products where description regexp '\\bSN[0-9]{4}-[0-9]{4}\\b' order by product_id

3475.DNA 模式识别 REGEXP LIKE LEFT RIGHT INSTR regexp_like

表：Samples
+----------------+---------+
| Column Name    | Type    | 
+----------------+---------+
| sample_id      | int     |
| dna_sequence   | varchar |
| species        | varchar |
+----------------+---------+
sample_id 是这张表的唯一主键。
每一行包含一个 DNA 序列以一个字符（A，T，G，C）组成的字符串表示以及它所采集自的物种。
生物学家正在研究 DNA 序列中的基本模式。编写一个解决方案以识别具有以下模式的 sample_id：

以 ATG 开头 的序列（一个常见的 起始密码子）
以 TAA，TAG 或 TGA 结尾 的序列（终止密码子）
包含基序 ATAT 的序列（一个简单重复模式）
有 至少 3 个连续 G 的序列（如 GGG 或 GGGG）
返回结果表以 sample_id 升序 排序。

结果格式如下所示。

示例：
输入：
Samples 表：
+-----------+------------------+-----------+
| sample_id | dna_sequence     | species   |
+-----------+------------------+-----------+
| 1         | ATGCTAGCTAGCTAA  | Human     |
| 2         | GGGTCAATCATC     | Human     |
| 3         | ATATATCGTAGCTA   | Human     |
| 4         | ATGGGGTCATCATAA  | Mouse     |
| 5         | TCAGTCAGTCAG     | Mouse     |
| 6         | ATATCGCGCTAG     | Zebrafish |
| 7         | CGTATGCGTCGTA    | Zebrafish |
+-----------+------------------+-----------+
输出：
+-----------+------------------+-------------+-------------+------------+------------+------------+
| sample_id | dna_sequence     | species     | has_start   | has_stop   | has_atat   | has_ggg    |
+-----------+------------------+-------------+-------------+------------+------------+------------+
| 1         | ATGCTAGCTAGCTAA  | Human       | 1           | 1          | 0          | 0          |
| 2         | GGGTCAATCATC     | Human       | 0           | 0          | 0          | 1          |
| 3         | ATATATCGTAGCTA   | Human       | 0           | 0          | 1          | 0          |
| 4         | ATGGGGTCATCATAA  | Mouse       | 1           | 1          | 0          | 1          |
| 5         | TCAGTCAGTCAG     | Mouse       | 0           | 0          | 0          | 0          |
| 6         | ATATCGCGCTAG     | Zebrafish   | 0           | 1          | 1          | 0          |
| 7         | CGTATGCGTCGTA    | Zebrafish   | 0           | 0          | 0          | 0          |
+-----------+------------------+-------------+-------------+------------+------------

# Write your MySQL query statement below
SELECT
    sample_id,
    dna_sequence,
    species,
    dna_sequence LIKE 'ATG%' AS has_start,
    dna_sequence REGEXP 'TAA$|TAG$|TGA$' AS has_stop,
    dna_sequence LIKE '%ATAT%' AS has_atat,
    dna_sequence REGEXP 'GGG+' AS has_ggg
FROM Samples
ORDER BY 1;

select *,
    left(dna_sequence,3)='ATG' has_start,
    right(dna_sequence,3) in ('TAA','TAG','TGA') has_stop,
    dna_sequence like '%ATAT%' has_atat,
    instr(dna_sequence,'GGG')>0 has_ggg
from Samples

CASE WHEN regexp_like(dna_sequence, '^ATG') THEN 1 ELSE 0 END has_start,
CASE WHEN regexp_like(dna_sequence, '(TAA$|TAG$|TGA$)') THEN 1 ELSE 0 END has_stop,
CASE WHEN regexp_like(dna_sequence, 'ATAT') THEN 1 ELSE 0 END has_atat,
CASE WHEN regexp_like(dna_sequence, 'G{3}') THEN 1 ELSE 0 END has_ggg

3482.分析组织层级递归

表：Employees

+----------------+---------+
| Column Name    | Type    | 
+----------------+---------+
| employee_id    | int     |
| employee_name  | varchar |
| manager_id     | int     |
| salary         | int     |
| department     | varchar |
+----------------+----------+
employee_id 是这张表的唯一主键。
每一行包含关于一名员工的信息，包括他们的 ID，姓名，他们经理的 ID，薪水和部门。
顶级经理（CEO）的 manager_id 是空的。
编写一个解决方案来分析组织层级并回答下列问题：

层级：对于每名员工，确定他们在组织中的层级（CEO 层级为 1，CEO 的直接下属员工层级为 2，以此类推）。
团队大小：对于每个是经理的员工，计算他们手下的（直接或间接下属）总员工数。
薪资预算：对于每个经理，计算他们控制的总薪资预算（所有手下员工的工资总和，包括间接下属，加上自己的工资）。
返回结果表以 层级 升序 排序，然后以预算 降序 排序，最后以 employee_name 升序 排序。

WITH RECURSIVE cte (employee_id, employee_name, salary, level, label) AS
(
    SELECT employee_id, employee_name, salary, 1, concat(employee_id) FROM Employees WHERE manager_id IS NULL
    UNION all
    SELECT e.employee_id, e.employee_name, e.salary, level + 1, concat(label, ',', e.employee_id) FROM Employees e JOIN cte ON e.manager_id = cte.employee_id
)

SELECT c.employee_id, c.employee_name, c.level, count(1) - 1 team_size, sum(c1.salary) budget 
FROM cte c JOIN cte c1 
ON c1.label REGEXP concat('^', c.label, '\\b') 
GROUP BY c.employee_id 
ORDER BY c.level, budget DESC, c.employee_name

# Write your MySQL query statement below

with recursive EmployeeTree as (
    select *, 1 as level
    from Employees
    where manager_id is null
    union all
    select e.*, et.level + 1 as level
    from Employees e
    join EmployeeTree et on et.employee_id = e.manager_id
),
AllSubsident as (
    select 
        employee_id as employee_id,
        employee_id as subsident_id,
        salary
    from Employees
    union all
    select 
        a.employee_id,
        e.employee_id as subsident_id,
        e.salary
    from Employees e
    join AllSubsident a on a.subsident_id = e.manager_id
),
grouped as (
    select employee_id, count(subsident_id) - 1 as team_size, sum(salary) as budget
    from AllSubsident
    group by employee_id
    order by employee_id
)
select 
    g.employee_id,
    e.employee_name,
    e.level,
    g.team_size,
    g.budget
from grouped g
join EmployeeTree e
on g.employee_id = e.employee_id
order by e.level,g.budget desc,e.employee_name;

3554.查找类别推荐对

表：ProductPurchases
+-------------+------+
| Column Name | Type | 
+-------------+------+
| user_id     | int  |
| product_id  | int  |
| quantity    | int  |
+-------------+------+
(user_id, product_id) 是这张表的唯一主键。
每一行代表用户以特定数量购买的一种产品。
表：ProductInfo

+-------------+---------+
| Column Name | Type    | 
+-------------+---------+
| product_id  | int     |
| category    | varchar |
| price       | decimal |
+-------------+---------+
product_id 是这张表的唯一主键。
每一行表示一件商品的类别和价格。
亚马逊想要了解不同产品类别的购物模式。编写一个解决方案：

查找所有 类别对（其中 category1 < category2）
对于每个类别对，确定同时购买了两类别产品的不同用户数量
如果至少有 3 个不同的客户购买了两个类别的产品，则类别对被视为 可报告的。

返回可报告类别对的结果表以 customer_count 降序 排序，并且为了防止排序持平，以 category1 字典序 升序 排序，然后以 category2 升序 排序。

# Write your MySQL query statement below
WITH
    user_category AS (
        SELECT DISTINCT
            user_id,
            category
        FROM
            ProductPurchases
            JOIN ProductInfo USING (product_id)
    ),
    pair_per_user AS (
        SELECT
            a.user_id,
            a.category AS category1,
            b.category AS category2
        FROM
            user_category AS a
            JOIN user_category AS b ON a.user_id = b.user_id AND a.category < b.category
    )
SELECT category1, category2, COUNT(DISTINCT user_id) AS customer_count
FROM pair_per_user
GROUP BY 1, 2
HAVING customer_count >= 3
ORDER BY 3 DESC, 1, 2;

3564.季节性销售分析

表：sales

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| sale_id       | int     |
| product_id    | int     |
| sale_date     | date    |
| quantity      | int     |
| price         | decimal |
+---------------+---------+
sale_id 是这张表的唯一主键。
每一行包含一件产品的销售信息，包括 product_id，销售日期，销售数量，以及单价。
表：products

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| product_name  | varchar |
| category      | varchar |
+---------------+---------+
product_id 是这张表的唯一主键。
每一行包含一件产品的信息，包括它的名字和分类。
编写一个解决方案来找到每个季节最受欢迎的产品分类。季节定义如下：

冬季：十二月，一月，二月
春季：三月，四月，五月
夏季：六月，七月，八月
秋季：九月，十月，十一月
一个 分类 的 受欢迎度 由某个 季节 的 总销售量 决定。如果有并列，选择总收入最高的类别 (quantity × price)。

返回结果表以季节 升序 排序。

结果格式如下所示。

 

示例：

输入：

sales 表：

+---------+------------+------------+----------+-------+
| sale_id | product_id | sale_date  | quantity | price |
+---------+------------+------------+----------+-------+
| 1       | 1          | 2023-01-15 | 5        | 10.00 |
| 2       | 2          | 2023-01-20 | 4        | 15.00 |
| 3       | 3          | 2023-03-10 | 3        | 18.00 |
| 4       | 4          | 2023-04-05 | 1        | 20.00 |
| 5       | 1          | 2023-05-20 | 2        | 10.00 |
| 6       | 2          | 2023-06-12 | 4        | 15.00 |
| 7       | 5          | 2023-06-15 | 5        | 12.00 |
| 8       | 3          | 2023-07-24 | 2        | 18.00 |
| 9       | 4          | 2023-08-01 | 5        | 20.00 |
| 10      | 5          | 2023-09-03 | 3        | 12.00 |
| 11      | 1          | 2023-09-25 | 6        | 10.00 |
| 12      | 2          | 2023-11-10 | 4        | 15.00 |
| 13      | 3          | 2023-12-05 | 6        | 18.00 |
| 14      | 4          | 2023-12-22 | 3        | 20.00 |
| 15      | 5          | 2024-02-14 | 2        | 12.00 |
+---------+------------+------------+----------+-------+
products 表：

+------------+-----------------+----------+
| product_id | product_name    | category |
+------------+-----------------+----------+
| 1          | Warm Jacket     | Apparel  |
| 2          | Designer Jeans  | Apparel  |
| 3          | Cutting Board   | Kitchen  |
| 4          | Smart Speaker   | Tech     |
| 5          | Yoga Mat        | Fitness  |
+------------+-----------------+----------+
输出：

+---------+----------+----------------+---------------+
| season  | category | total_quantity | total_revenue |
+---------+----------+----------------+---------------+
| Fall    | Apparel  | 10             | 120.00        |
| Spring  | Kitchen  | 3              | 54.00         |
| Summer  | Tech     | 5              | 100.00        |
| Winter  | Apparel  | 9              | 110.00        |
+---------+----------+----------------+---------------+

with details as (
select
,
case when month(sale_date) in ('12','1','2') then 'Winter'
when month(sale_date) in ('3','4','5') then 'Spring'
when month(sale_date) in ('6','7','8') then 'Summer'
when month(sale_date) in ('9','10','11') then 'Fall'
end as season
from
sales
),
group_details as (select
a.season,b.category,
sum(quantity) as total_quantity,
sum(quantityprice) as total_revenue,
row_number() over(partition by a.season order by sum(quantity) desc,sum(quantityprice) desc) rn
from
details a
left join
products b
on a.product_id = b.product_id
group by a.season,b.category)
select
season,category,
total_quantity,total_revenue
from
group_details
where rn=1

with t as (
    select *, 
        case
            when month(sale_date) between 3 and 5 then "Spring"
            when month(sale_date) between 6 and 8 then "Summer"
            when month(sale_date) between 9 and 11 then "Fall"
            else "Winter"
        end as "season"
     from sales 
),t2 as (
    select 
        sum(quantity * price) "total_revenue",
        season,category,sum(quantity) as "total_quantity",
        rank()over(partition by season order by sum(quantity) desc,sum(quantity * price) desc) as rk
    from t join products p using(product_id) group by season,category
    
)
select 
    season,category,total_quantity,total_revenue
from t2 where rk = 1 order by season 

#########
with t as (
    select *, 
        case
            when month(sale_date) between 3 and 5 then "Spring"
            when month(sale_date) between 6 and 8 then "Summer"
            when month(sale_date) between 9 and 11 then "Fall"
            else "Winter"
        end as "season"
     from sales 
),t2 as (
    select 
        sum(quantity * price)over(partition by season,category) as "total_revenue",
        season,category,sum(quantity)over(partition by season,category) as "total_quantity"
    from t join products p using(product_id)
),t3 as (
    select *,rank()over(partition by season order by total_quantity desc,total_revenue desc) as rk
    from t2
)
select 
    distinct season,category,total_quantity,total_revenue
from t3 where rk = 1 order by season

WITH temp AS (
    SELECT *, Month(sale_date) AS month
    FROM sales
), get_season AS (
    SELECT *, CASE WHEN month in (12,1,2) THEN 'Winter'
                    WHEN month in (3,4,5) THEN 'Spring'
                    WHEN month in (6,7,8) THEN 'Summer'
                    ELSE 'Fall' END AS season
    FROM temp
), get_cat AS (
    SELECT g.product_id, quantity, price, category, season
    FROM get_season g LEFT JOIN products p ON g.product_id = p.product_id
) 
,get_prod_quant AS (
    SELECT product_id,season,SUM(quantity) AS total_quantity,
                            SUM(quantity*price) AS total_revenue
    FROM get_cat
    GROUP BY category, season
), get_rank AS (
    SELECT *
    FROM (
        SELECT *, DENSE_RANK() OVER(PARTITION BY season ORDER BY total_quantity DESC, total_revenue DESC ) rnk
        FROM get_prod_quant
    ) tmp
    WHERE rnk = 1
), final AS (
    SELECT season, category, total_quantity, total_revenue
    FROM get_rank g LEFT JOIN products p ON g.product_id = p.product_id
    ORDER BY season 
)

SELECT * FROM final

3580.寻找持续进步的员工

表：employees
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
+-------------+---------+
employee_id 是这张表的唯一主键。
每一行包含一名员工的信息。
表：performance_reviews

+-------------+------+
| Column Name | Type |
+-------------+------+
| review_id   | int  |
| employee_id | int  |
| review_date | date |
| rating      | int  |
+-------------+------+
review_id 是这张表的唯一主键。
每一行表示一名员工的绩效评估。评分在 1-5 的范围内，5分代表优秀，1分代表较差。
编写一个解决方案，以找到在过去三次评估中持续提高绩效的员工。

员工 至少需要 3 次评估 才能被考虑
员工过去的 3 次评估，评分必须 严格递增（每次评价都比上一次好）
根据 review_date 为每位员工分析最近的 3 次评估
进步分数 为最后 3 次评估中最后一次评分与最早一次评分之间的差值
返回结果表以 进步分数 降序 排序，然后以 名字 升序 排序。

with t as(
select employee_id,review_date,rating,lead(rating,1) over(partition by employee_id order by review_date) lrating
from performance_reviews pr
where lrating is not null and pr.employee_id in
(
select employee_id
from performance_reviews p
group by employee_id
having count(*)>=3
)
)


with p as(
select distinct employee_id
from(
select employee_id,review_date,lrating>rating r
from t) s
where r=0
)

select y.employee_id,name,max(rating)-min(rating) improvement_score 
from(
select employee_id,review_date,rating,row_number() over(partition by employee_id order by review_date desc) rk
from t 
where t.employee_id not in (
select * from p
)
) y join employees on y.employee_id = employees.employee_id
where y.rk <=3
group by y.employee_id

1	这里理解有点问题，它的意思是最近三次是依次增加就行，不用全部都是一次增加

3586.寻找 COVID 康复患者

表：patients

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| patient_id  | int     |
| patient_name| varchar |
| age         | int     |
+-------------+---------+
patient_id 是这张表的唯一主键。
每一行表示一个患者的信息。
表：covid_tests

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| test_id     | int     |
| patient_id  | int     |
| test_date   | date    |
| result      | varchar |
+-------------+---------+
test_id 是这张表的唯一主键。
每一行代表一个 COVID 检测结果。结果可以是阳性、阴性或不确定。
编写一个解决方案以找到从 COVID 中康复的患者——那些曾经检测呈阳性但后来检测呈阴性的患者。

患者如果 至少有一次阳性 检测结果后，在 之后的日期 至少有一次 阴性 检测结果，则被认为已康复。
计算从 首次阳性检测 结果到 该阳性检测 后的 首次阴性检测结果 之间的 康复时间（以天为单位）
仅包括 同时具有阳性及阴性检测结果的患者
返回结果表以 recovery_time 升序 排序，然后以 patient_name 升序 排序。

with t as(
select patient_id,min(result) result,min(test_date) test_date
from covid_tests
where result='Positive'
group by patient_id 
)

select covid_tests.patient_id patient_id,patient_name ,age,min(covid_tests.test_date)-t.test_date recovery_time 
from covid_tests join t 
on covid_tests.patient_id = t.patient_id and covid_tests.test_date > t.test_date 
join patients on t.patient_id = patients.patient_id
where covid_tests.result = 'Negative'
group by covid_tests.patient_id

稀土掘金

url

什么是幻读，脏读，不可重复读呢？

1
2
3

脏读  x
幻读  数据量不一样
不可重复读 数据值不一样

limit 1000000 加载很慢的话，你是怎么解决的呢？

方案一：如果id是连续的，可以这样，返回上次查询的最大记录(偏移量)，再往下limit
复制代码select id，name from employee where id>1000000 limit 10.

方案二：在业务允许的情况下限制页数：
建议跟业务讨论，有没有必要查这么后的分页啦。因为绝大多数用户都不会往后翻太多页。

方案三：order by + 索引（id为索引）
复制代码select id，name from employee order by id  limit 1000000，10

方案四：利用延迟关联或者子查询优化超多分页场景。（先快速定位需要获取的id段，然后再关联）
复制代码SELECT a.* FROM employee a, (select id from employee where 条件 LIMIT 1000000,10 ) b where a.id=b.id

在高并发情况下，如何做到安全的修改同一行数据？

1	要安全的修改同一行数据，就要保证一个线程在修改时其它线程无法更新这行记录。一般有悲观锁和乐观锁两种方案~

使用悲观锁

悲观锁思想就是，当前线程要进来修改数据时，别的线程都得拒之门外~
比如，可以使用select…for update ~
select * from User where name=‘jay’ for update
update xxx

以上这条sql语句会锁定了User表中所有符合检索条件（name=‘jay’）的记录。本次事务提交之前，别的线程都无法修改这些记录。

使用乐观锁

乐观锁思想就是，有线程过来，先放过去修改，如果看到别的线程没修改过，就可以修改成功，如果别的线程修改过，就修改失败或者重试。实现方式：乐观锁一般会使用版本号机制或CAS算法实现。

CSDN案例

案例一

csdn链接

# 学生表 Student：
 
create table Student(
 
SId varchar(10) ,
 
Sname varchar(10),
 
Sage datetime,
 
Ssex varchar(10));
 
 
# 教师表 Teacher
 
create table Teacher(
 
TId varchar(10),
 
Tname varchar(10)); 
 
 
# 科目表 Course
 
create table Course(
 
CId varchar(10),
 
Cname nvarchar(10),
 
TId varchar(10)); 
 
 
# 成绩表 SC
 
create table SC(
 
SId varchar(10),
 
CId varchar(10),
 
score decimal(18,1));

1.1查询同时存在01课程和02课程的情况

1.2查询存在01课程但可能不存在02课程的情况（不存在时显示为null）

1.3查询不存在01课程但存在02课程的学生情况

2.0查询平均成绩大于等于60分的学生编号和学生姓名和平均成绩
where group by having

3.0查询在sc表存在成绩的学生信息

4.0查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（没成绩的显示为nulll）
IFNULL(a,b)
sum(case when then else)
5.0查询[李]姓老师的数量

6.0查询学过[张三]老师授课的同学信息(难度系数★★★)

7.0查询没有学全所有课程的同学的信息

8.0查询至少有一门课学号与01的同学所学相同的同学信息

9.0查询和01号同学学习的课程完全相同的其他同学的信息

10.查询没学过[张三]老师讲授的任一门课程的学生姓名

11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

12.检索01课程分数小于60，按分数降序排列的学生信息

13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩1

14.查询各科成绩最高分、最低分和平均分

15.按各科成绩进行排名，并显示排名，score重复时继续排序

16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺

17.统计各科成绩分数段人数，课程编号，课程名称，[100-85][85-70][70-60][60-0]及所占百分比

18.查询各科成绩前三名的记录

19.查询每门课程被选修的学生数

20.查询出只选修两门课程的学生学号和姓名

21.查询男生、女生人数

22.查询名字中含有[风]字的学生信息

23.查询同名同姓学生名单，并统计同名同性人数

24.查询1990年出生的学生名单

25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同，按课程编号升序排列

26.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

27.查询课程名称为[数学]，且分数低于60的学生姓名和分数

28.查询所有学生的课程及分数情况（存在学生没成绩没选课的情况）

29.查询任何一门课程成绩在70分以上的姓名、课程名称和分数

30.查询不及格的课程 

31.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名 

32.求每门课程的学生人数 

33.假设成绩不重复，查询选修[张三]老师所授课程的学生中，成绩最高的学生信息及成绩 

34.假设成绩有重复，查询选修[张三]老师所授课程的学生中，成绩最高的学生信息及成绩 

35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 

36.查询每门科目成绩最好的前两名

1.1

1.
SELECT a.Sid,a.score 01_score,b.score 02_score
FROM (SELECT * FROM sc WHERE Cid = 01) AS a,(SELECT * FROM sc WHERE Cid = 02) AS b 
WHERE a.Sid = b.Sid ;

2.
SELECT C1.sid, C3.sname, C3.sage, C3.ssex, C1.score AS score1, 
	C2.score AS score2 
FROM (SELECT * FROM SC WHERE Cid = '01') AS C1 
	INNER JOIN (SELECT * FROM SC WHERE Cid = '02') AS C2 
ON C1.sid = C2.sid 
	INNER JOIN Student AS C3 
    ON C1.sid = C3.sid ;

1.2

查询存在01课程但可能不存在02课程的情况（不存在时显示为null）


SELECT *
FROM (SELECT * FROM sc WHERE cid = '01') a
LEFT JOIN sc 
ON a.sid = sc.sid AND sc.cid = '02';


----
SELECT *
FROM (SELECT * FROM sc WHERE cid = '01') a
LEFT JOIN (SELECT * FROM sc WHERE cid = '02') b
ON a.sid = b.sid;


---
SELECT *
FROM sc a
LEFT JOIN sc b
ON a.sid = b.sid AND b.cid = '02'
WHERE a.cid = '01';

1.3

查询不存在01课程但存在02课程的学生情况

SELECT *
FROM sc s1
WHERE s1.CId='02' AND s1.SId not in (
SELECT s2.SId
FROM sc s2
WHERE s2.CId='01');

3 EXISTS

SELECT s.student id,s.name,s.age, s.genderFRoM student s
JOIN SC sc ON s.student id= sc.student id


SELECT s.student id,s.name, s.age, s.genderFROM student s
WHERE EXISTS(
SELECT 1
FROM SC SC
WHERE sc.student id=s.student id

7 having的一个使用技巧

查询没有学全所有课程的同学的信息


SELECT SC.SId,COUNT(cid) 选课数目
			FROM sc
			GROUP BY sid
			HAVING 选课数目 < (SELECT COUNT(cid)FROM course c)

8

查询至少有一门课学号与01的同学所学相同的同学信息

SELECT DISTINCT a.*
FROM student a
INNER JOIN (SELECT *
			FROM sc
			WHERE cid IN (SELECT cid FROM sc WHERE sid = "01")) b
ON a.sid = b.sid;
 
----
SELECT DISTINCT s.*
FROM sc
INNER JOIN student s
ON sc.sid = s.sid
WHERE sc.cid IN (SELECT cid FROM sc WHERE sid = "01");

9

查询和01号同学学习的课程完全相同的其他同学的信息

SELECT SId,COUNT(*) num
FROM 
(
SELECT s1.SId,s1.CId
FROM sc s1
INNER JOIN
(
SELECT sc.*
FROM sc
WHERE sc.SId='01') s2
WHERE s1.CId=s2.CId
) t
GROUP BY SId
HAVING num = (SELECT COUNT(*) FROM sc WHERE sc.SId='01')

13 avg() over(partition by)

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT *
FROM sc s1
INNER JOIN
(
SELECT SId,AVG(score) as avg
FROM sc
GROUP BY SId
) s2
on s1.SId=s2.SId
ORDER BY avg DESC,s1.SId

SELECT
    s.student_name AS 学生姓名,
    c.course_name AS 课程名称,
    g.score AS 成绩,
    AVG(g.score) OVER (PARTITION BY s.student_id) AS 平均成绩
FROM
    grades g
JOIN students s ON g.student_id = s.student_id
JOIN courses c ON g.course_id = c.course_id
ORDER BY
    平均成绩 DESC,
    s.student_name,
    c.course_name;

14

查询各科成绩最高分、最低分和平均分

SELECT
    c.course_name AS 课程名称,
    MAX(g.score) AS 最高分,
    MIN(g.score) AS 最低分,
    ROUND(AVG(g.score), 2) AS 平均分  -- 保留两位小数
FROM
    grades g
JOIN courses c ON g.course_id = c.course_id  -- 关联课程名称
GROUP BY
    c.course_id  -- 按课程分组（避免课程名重复）
ORDER BY c.course_name;  -- 按课程名称排序

15 变量的使用

按各科成绩进行排名，并显示排名，score重复时继续排序

SELECT sc.* ,DENSE_RANK() over(PARTITION by CId ORDER BY score DESC) r
FROM sc


-----  它这个使用变量的方法也不对 如图片
SELECT sid,
	   cid,
	   score,
	   @rank:=@rank+1 as rk
FROM sc,(SELECT @rank:=0) as t
ORDER BY score DESC;
 
 
-- 自定义变量 --
# ① 定义变量 set @a:=2 (@用来标识变量),使用变量select @a
# ② 定义并使用变量 select @b:=4 
# 修改或者重新赋值 select @b:=6

16 dense_rank()、变量的使用

查询学生的总成绩，并进行排名，总分重复时保留名次空缺

SELECT SId,totalscore,DENSE_RANK()over(ORDER BY totalscore DESC) r
FROM
(
SELECT SId,SUM(score) totalscore
FROM sc
GROUP BY SId
) t

SELECT a.*,
		@rank:=IF(@sco=sum_score,@rank+0,@rank+1) rk
FROM (SELECT sid,
			 SUM(score) sum_score
	  FROM sc
	  GROUP BY sid
	  ORDER BY sum_score DESC) a,(select @sco:=null,@rank:=0) b

1
2
3

rank()排序会有重复的，会出现不连续的排序
dense_rank()排序相同时会重复，但是各个排序序号都是连续的
row_number()排序不会重复，且序号连续

17 区域段 case when then concat

统计各科成绩分数段人数，课程编号，课程名称，[100-85][85-70][70-60][60-0]及所占百分比


SELECT sc.CId 课程编号,c.Cname 课程名称,COUNT(1) 选修人数
	   ,CONCAT(ROUND(SUM(CASE WHEN sc.score >=0 AND sc.score <= 60 THEN 1 ELSE 0 END)/COUNT(1),2),'%') AS '(0-60]'
       ,CONCAT(ROUND(SUM(CASE WHEN sc.score >=60 AND sc.score <= 70 THEN 1 ELSE 0 END)/COUNT(1),2),'%') AS '(60-70]'
       ,CONCAT(ROUND(SUM(CASE WHEN sc.score >= 70 AND sc.score <= 85 THEN 1 ELSE 0 END)/COUNT(1),2),'%') AS '(70-85]'
       ,CONCAT(ROUND(SUM(CASE WHEN sc.score >= 85 AND sc.score <= 100 THEN 1 ELSE 0 END)/COUNT(1),2),'%') AS '(85-100]'
FROM sc
LEFT JOIN course c
ON sc.cId = c.CId
GROUP BY sc.CId,c.Cname

22 like

查询名字中含有[风]字的学生信息

SELECT *
FROM student 
WHERE sname LIKE '%风%';

25

SELECT CId,AVG(score) avg
FROM sc
GROUP BY CId
ORDER BY avg desc,CId

33 变量的使用 limit

假设成绩不重复，查询选修[张三]老师所授课程的学生中，成绩最高的学生信息及成绩 


# 法一
SELECT *
FROM teacher t
LEFT JOIN course c
ON t.tid = c.tid
LEFT JOIN sc
ON c.cid = sc.cid
LEFT JOIN student s
ON s.sid = sc.sid
WHERE t.tname = "张三"
ORDER BY sc.score DESC
LIMIT 1;
 
# 法二
-- 1.先查询[张三]老师所带的课程编号cid和tid --
SELECT *
FROM teacher t
LEFT JOIN course c
ON t.tid = c.tid
WHERE t.tname = "张三";
-- 2.将以上结果与成绩表sc关联 --
SELECT sc.sid,a.cid,a.cname,a.tname,sc.score
FROM (SELECT t.tid,t.tname,c.cid,c.cname
	  FROM teacher t
	  LEFT JOIN course c
	  ON t.tid = c.tid
	  WHERE t.tname = "张三") a 
LEFT JOIN sc
ON a.cid = sc.cid
ORDER BY sc.score DESC
LIMIT 1;
-- 3.将以上结果与学生表student关联 --
SELECT s.sid 学号,s.sname 学生姓名,a.cname 课程,a.tname 教师姓名,a.score 成绩
FROM (SELECT sc.sid,a.cid,a.cname,a.tname,sc.score
	  FROM (SELECT t.tid,t.tname,c.cid,c.cname
			FROM teacher t
			LEFT JOIN course c
			ON t.tid = c.tid
			WHERE t.tname = "张三") a 
		LEFT JOIN sc
		ON a.cid = sc.cid
ORDER BY sc.score DESC
LIMIT 1) a
LEFT JOIN student s
ON a.sid = s.sid;

34 变量的使用

1	假设成绩有重复，查询选修[张三]老师所授课程的学生中，成绩最高的学生信息及成绩

SET @score:=NULL,@rank:=0;

SELECT *
FROM 
(SELECT a.*,
		CASE WHEN @score=score THEN @rank
			 ELSE @score:=score AND @rank:=@rank+1 END rk
FROM 
(SELECT s.sid,s.sname,sc.score,c.cid,c.cname
 FROM teacher t
 LEFT JOIN course c
 ON t.tid = c.tid
 LEFT JOIN sc
 ON c.cid = sc.cid
 LEFT JOIN student s
 ON s.sid = sc.sid
 WHERE t.tname = "张三") a) s
 ORDER BY rk DESC
LIMIT 1;

SELECT *
FROM 
(SELECT a.*,
		CASE WHEN @score=score THEN @rank
			 ELSE @score:=score AND @rank:=@rank+1 END rk
FROM 
(SELECT s.sid,s.sname,sc.score,c.cid,c.cname
 FROM teacher t
 LEFT JOIN course c
 ON t.tid = c.tid
 LEFT JOIN sc
 ON c.cid = sc.cid
 LEFT JOIN student s
 ON s.sid = sc.sid
 WHERE t.tname = "张三") a,(SELECT @score:=NULL,@rank:=0) t) s
 ORDER BY rk DESC
LIMIT 1;

41 current_day(),substring(),year(),now()

1
2
3

current_day()	2024-10-14

now()	2024-10-14 11:01:11

按照出生日期来算，当前月日 < 出生年月的月日 则年龄减一 

SELECT student.SId,CASE 
	WHEN SUBSTRING(CURRENT_DATE(),6,10)-SUBSTRING(Sage,6,10)>=0
	THEN
		YEAR(CURRENT_DATE())-YEAR(Sage)
	ELSE
		YEAR(CURRENT_DATE())-YEAR(Sage)-1
END age
FROM student

SELECT 
    Sno, 
    Sname, 
    YEAR(CURDATE()) - YEAR(Sage) - 
    (DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(Sage, '%m%d')) AS Age
FROM 
    student;

42 week

查询本周过生日的学生

-- 【分析】使用week()函数
SELECT *,
       WEEK(sage),
       WEEK(NOW())
FROM student
WHERE WEEK(sage)=WEEK(NOW());

43 month

查询本月过生日的学生


-- 【分析】使用month()函数 --
SELECT *,
       MONTH(sage),
       MONTH(NOW())
FROM student
WHERE MONTH(sage)=MONTH(NOW());

44 month

查询下月过生日的学生 

SELECT *,
       MONTH(sage),
       MONTH(NOW())
FROM student
WHERE MONTH(sage)=MONTH(NOW())+1;

案例二

url

student

字段名	
Sno	学生编号
Sname	学生姓名
Sage	出生年月
Ssex	学生性别

course

字段名	--
Cno	科目编号
Cname	科目名称
Tno	任课老师编号

teacher

字段名	--
Tno	任课教师标号
Tname	任课老师姓名

SC

字段名	--
Sno	学生编号
Cno	科目编号
score	成绩

1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

1.1 查询同时存在" 01 "课程和" 02 "课程的情况

1.2 查询存在"01"课程但可能不存在"02"课程的情况(不存在时显示为 null )

1.3 查询不存在"01"课程但存在"02"课程的情况

2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

3. 查询在 SC 表存在成绩的学生信息

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

4.1 查有成绩的学生信息

5. 查询「李」姓老师的数量 

6. 查询学过「张三」老师授课的同学的信息 

7. 查询没有学全所有课程的同学的信息 

8. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息 

9. 查询和"01"号的同学学习的课程完全相同的其他同学的信息 

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名 

11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 

12. 检索"01"课程分数小于60，按分数降序排列的学生信息

13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

14. 查询各科成绩最高分、最低分和平均分：
    以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
    及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
    还要求选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

15. 按各科成绩进行排序，并显示排名，Score重复时保留名次空缺

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

16.  查询学生的总成绩，并进行排名，总分重复时保留名次空缺

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

18. 查询各科成绩前三名的记录

19. 查询每门课程被选修的学生数 

20. 查询出只选修两门课程的学生学号和姓名 

21. 查询男生、女生人数

22. 查询名字中含有「风」字的学生信息

23. 查询同名同性学生名单，并统计同名人数

24. 查询 1990 年出生的学生名单

25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 

27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数 

28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

30. 查询不及格的课程

31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

32. 求每门课程的学生人数 

33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 

36. 查询每门功成绩最好的前两名

37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。

38. 检索至少选修两门课程的学生学号 

39. 查询选修了全部课程的学生信息

40. 查询各学生的年龄，只按年份来算 

41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

42. 查询本周过生日的学生

43. 查询下周过生日的学生

44. 查询本月过生日的学生

45. 查询下月过生日的学生

9 !外连接实现except

SELECT *
from
(
SELECT Sno
from sc
where Cno and Sno='01' in (
    SELECT Cno
    from sc
    where Sno='01')
    group by Sno
) t1
left join
(
SELECT Sno
from sc
where Cno not in (
    SELECT Cno
    from sc
    where Sno='01')
    group by Sno
) t2
on t1.Sno=t2.Sno
where t2.Sno is null

10

-- 1.先查询出选修了张三老师讲授课程的sid -- 
SELECT s.sid
FROM  teacher t
LEFT JOIN course c
ON t.tid = c.tid
LEFT JOIN sc
ON c.cid = sc.cid
LEFT JOIN student s
ON sc.sid = s.sid
WHERE tname = "张三";
-- 2.与学生表student关联,筛选出不在以上结果内的sid --
SELECT s.*
FROM student s
WHERE sid NOT IN (SELECT s.sid
				  FROM  teacher t
				  LEFT JOIN course c
				  ON t.tid = c.tid
				  LEFT JOIN sc
				  ON c.cid = sc.cid
				  LEFT JOIN student s
				  ON sc.sid = s.sid
				  WHERE tname = "张三");

select sid
from student
where sid not in
(
select sid
from sc
where cid in
(
select cid
from teacher t
inner join course c
on t.tid=c.tid
)
group by sid
)

11

select a.sid,b.sname,avg(a.score) as avg_score
from sc a
left join student b
on a.sid = b.sid
where a.score < 60
group by a.sid
having count(a.cid) >= 2;

13

14 14 case when round 值得注意

SELECT MIN(score) min_score
			,MAX(score) max_score
			,AVG(score) avg_score
			,COUNT(1) stu_number
			,SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END)/COUNT(1) 及格率
			,SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END)/COUNT(1) 中等率
			,SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END)/COUNT(1) 优良率
			,SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END)/COUNT(1) 优秀率
FROM sc
LEFT JOIN course c
ON sc.cid = c.cid 
GROUP BY sc.cid
ORDER BY stu_number DESC ,sc.cid ASC;

SELECT courseid,MAX(score) AS '最高分', MIN(score)AS '最低分' ,AVG(score) AS '平均分' ,COUNT(*) AS '选修人数',
     SUM(CASE WHEN student_score.score >= 60 THEN 1 ELSE 0 END)/COUNT(*) AS '及格率',
     SUM(CASE WHEN student_score.score >= 70 AND student_score.score< 80 THEN 1 ELSE 0 END)/COUNT(*) AS '中等率',
     SUM(CASE WHEN student_score.score >= 80 AND student_score.score<90 THEN 1 ELSE 0 END)/COUNT(*) AS '优良率',
     SUM(CASE WHEN student_score.score >= 90 THEN 1 ELSE 0 END)/COUNT(*) AS '优秀率'
FROM student_score GROUP BY courseid ORDER BY COUNT(*)DESC,student_score.courseid ASC ;

15 PARTITION BY 下面那个with案例值得注意

SELECT   
    Sno,  
    Cno,  
    score,  
    DENSE_RANK() OVER (PARTITION BY Cno ORDER BY score DESC) AS ranking  
FROM   
    sc  
ORDER BY   
    Cno, score DESC;
    
    在上述查询中，ORDER BY Cno, score DESC是最终的排序，它确保结果首先按课程号排序，然后按分数降序排序。如果你只关心排名而不关心最终排序顺序，可以省略此ORDER BY子句。

案例

select Department, Employee, Salary
from (
    select d.name Department, ee.name Employee, ee.salary Salary, dense_rank() over(partition by departmentId order by salary desc) ranks
    from Employee ee
    left join Department d
    on ee.departmentId = d.id
) t
where ranks <= 3

案例

WITH t AS (
    SELECT
        employee_id,
        department_id,
        primary_flag,
        COUNT(*) OVER(PARTITION BY employee_id) AS count_over
    FROM Employee
            )
SELECT employee_id,department_id
FROM t
WHERE count_over = 1 or primary_flag = 'Y'

16 变量的使用法一没看懂法二法六值得注意

!16 法一
-- 1、学生的总成绩 --
SELECT sid,SUM(score) sum_score
FROM sc
GROUP BY sid
ORDER BY sum_score DESC;

-- 2、名次排序 --
SELECT a.*,
		@rank:=IF(@sco=sum_score,' ',@rank+1) rk
FROM (SELECT sid,
			 SUM(score) sum_score
	  FROM sc
	  GROUP BY sid
	  ORDER BY sum_score DESC) a,(select @sco:=null,@rank:=0) b

法二 (法二-法五是leecode的例子，但用了这里的数据，把sc行数据理解成是总成绩)
select a.Sno, a.score,(select count(b.score)+1 --结果有莫名错误
from sc as b
where b.score>a.score)  as 'r'
from sc as a
order by 'r';

select a.Sno, a.score,(select count(b.score)+1 --这样接正确了，奇怪
from sc as b
where b.score>a.score)  as r
from sc as a
order by r;

--等同于rank窗口函数实现的结果

法三 (法二-法五是leecode的例子，但用了这里的数据，把sc行数据理解成是总成绩)

select sc.Sno, sc.score,DENSE_RANK() OVER(ORDER BY sc.score desc) as r
from sc;

法四 (法二-法五是leecode的例子，但用了这里的数据，把sc行数据理解成是总成绩)

select sc.Sno, sc.score,RANK() OVER(ORDER BY sc.score desc) as r
from sc;

法五 (法二-法五是leecode的例子，但用了这里的数据，把sc行数据理解成是总成绩)

select sc.Sno, sc.score,ROW_NUMBER() OVER(ORDER BY sc.score desc) as r
from sc;

法六 (法二-法五是leecode的例子，但用了这里的数据，把sc行数据理解成是总成绩)

select a.id, a.score, count(*) as r
from scores a
left join scores b
on a.score < b.score
group by id, score
order by score DESC

错误写法 (法二-法五是leecode的例子，但用了这里的数据，把sc行数据理解成是总成绩)

select *
from sc as a
where EXISTS (
select b.score
from sc as b
where a.Sno!=b.Sno and a.score<=b.score
);

select a.Sno, count(*) as coun
from SC as a
where a.score<=(
select b.score
from SC as b
where a.Sno!=b.Sno
)
group by a.Sno
order by coun;

SELECT 
    s.Sno, 
    s.Sname, 
    SUM(sc.score) AS TotalScore,
    DENSE_RANK() OVER (ORDER BY SUM(sc.score) DESC) AS Rank
FROM 
    student s
JOIN 
    SC sc ON s.Sno = sc.Sno
GROUP BY 
    s.Sno, s.Sname
ORDER BY 
    TotalScore DESC;

17 18值得注意 22 ROUND\CASE\CONCAT

！17 不会
SELECT sc.cid 课程编号,c.cname 课程名称,COUNT(1) 选修人数
	   ,CONCAT(ROUND(SUM(CASE WHEN sc.score >=0 AND sc.score <= 60 THEN 1 ELSE 0 END)/COUNT(1),2),'%') AS '(0-60]'
       ,CONCAT(ROUND(SUM(CASE WHEN sc.score >=60 AND sc.score <= 70 THEN 1 ELSE 0 END)/COUNT(1),2),'%') AS '(60-70]'
       ,CONCAT(ROUND(SUM(CASE WHEN sc.score >= 70 AND sc.score <= 85 THEN 1 ELSE 0 END)/COUNT(1),2),'%') AS '(70-85]'
       ,CONCAT(ROUND(SUM(CASE WHEN sc.score >= 85 AND sc.score <= 100 THEN 1 ELSE 0 END)/COUNT(1),2),'%') AS '(85-100]'
FROM sc
LEFT JOIN course c
ON sc.cid = c.cid
GROUP BY sc.cid;
 
-- concat的作用是将两个字符拼接起来 --

！18  不会 好像有点问题
SELECT a.*,c.cname
FROM sc a
LEFT JOIN course c
ON a.cid = c.cid 
WHERE (SELECT COUNT(1) 
	   FROM sc b
       WHERE b.cid = a.cid AND b.score > a.score) < 3
ORDER BY cid DESC,score DESC;

19
SELECT sc.sid 学生编号,s.sname 学生姓名,COUNT(sc.cid) 选课数量
FROM sc
LEFT JOIN student s
ON sc.sid = s.sid
GROUP BY sc.sid,s.sname
HAVING COUNT(sc.cid) = 2;

20   
SELECT sc.sid 学生编号,s.sname 学生姓名,COUNT(sc.cid) 选课数量
FROM sc
LEFT JOIN student s
ON sc.sid = s.sid
GROUP BY sc.sid，s.sname
HAVING COUNT(sc.cid) = 2;

21 x

!22 
SELECT *
FROM student 
WHERE sname LIKE '%风%';

23
SELECT sname 姓名,ssex 性别,COUNT(*) 同名人数
FROM student
GROUP BY sname,ssex;


24 x+

25 !
select a.Cno,b.Cname,avg(a.score) as avg_score -- 这里有b.Cname错误了
from SC a
left join Course b
on a.Cno = b.Cno
group by a.Cno
order by avg_score desc,a.Cno asc;

26
select a.sid,b.sname,avg(a.score) as avg_score
from sc a
left join student b
on a.sid = b.sid
group by a.sid
having avg_score >= 85;

27 x

！33 34 35 36 37 39 40 HAVING 变量

28 x

29 x

30 x

31 x

32 x

33
# 法一
SELECT *
FROM teacher t
LEFT JOIN course c
ON t.tid = c.tid
LEFT JOIN sc
ON c.cid = sc.cid
LEFT JOIN student s
ON s.sid = sc.sid
WHERE t.tname = "张三"
ORDER BY sc.score DESC
LIMIT 1;
 
# 法二
-- 1.先查询[张三]老师所带的课程编号cid和tid --
SELECT *
FROM teacher t
LEFT JOIN course c
ON t.tid = c.tid
WHERE t.tname = "张三";
-- 2.将以上结果与成绩表sc关联 --
SELECT sc.sid,a.cid,a.cname,a.tname,sc.score
FROM (SELECT t.tid,t.tname,c.cid,c.cname
	  FROM teacher t
	  LEFT JOIN course c
	  ON t.tid = c.tid
	  WHERE t.tname = "张三") a 
LEFT JOIN sc
ON a.cid = sc.cid
ORDER BY sc.score DESC
LIMIT 1;
-- 3.将以上结果与学生表student关联 --
SELECT s.sid 学号,s.sname 学生姓名,a.cname 课程,a.tname 教师姓名,a.score 成绩
FROM (SELECT sc.sid,a.cid,a.cname,a.tname,sc.score
	  FROM (SELECT t.tid,t.tname,c.cid,c.cname
			FROM teacher t
			LEFT JOIN course c
			ON t.tid = c.tid
			WHERE t.tname = "张三") a 
		LEFT JOIN sc
		ON a.cid = sc.cid
ORDER BY sc.score DESC
LIMIT 1) a
LEFT JOIN student s
ON a.sid = s.sid;

34 ! rank dense_rank也行
SELECT *
FROM 
(SELECT a.*,
		CASE WHEN @score=score THEN @rank
			 WHEN @score:=score THEN @rank:=@rank+1 END rk
FROM 
(SELECT s.sid,s.sname,sc.score,c.cid,c.cname
 FROM teacher t
 LEFT JOIN course c
 ON t.tid = c.tid
 LEFT JOIN sc
 ON c.cid = sc.cid
 LEFT JOIN student s
 ON s.sid = sc.sid
 WHERE t.tname = "张三") a,(SELECT @score:=NULL,@rank:=0) t) s
WHERE rk=1;

35
SELECT a.sid,a.cid,a.score
FROM sc a
INNER JOIN sc b
ON a.sid = b.sid
WHERE a.cid != b.cid AND a.score = b.score
GROUP BY a.cid,a.sid;

36 !
SELECT sid,cid,score,rk
FROM(
	SELECT sc.*,
		   @rank:=if(@c_cid=cid,if(@sco=score,@rank,@rank+1),1) rk,
		   @sco:=score,
		   @c_cid:=cid
		FROM sc,(SELECT @sco=null,@rank:=0,@c_cid:=null) b
ORDER BY cid,score DESC) a
where a.rk<3;

37
# 法一
SELECT cid,COUNT(sid) 选修人数
FROM sc 
GROUP BY cid
HAVING COUNT(sid) > 5;
 
# 法二
SELECT cid,COUNT(1) 选修人数
FROM sc 
GROUP BY cid
HAVING COUNT(1) > 5;
 
-- 知识点补充 --
# COUNT(1) COUNT(*) COUNT(列名)的区别
# COUNT(1)包含忽略所有列，用1代表代码行，统计时不会忽略列值为NULL
# COUNT(*)包含所有列，相当于行数，统计时，不会忽略列值为NULL
# COUNT(列名)只包含列名那一列，统计时忽略列为NULL(某字段为NULL,不计数)

38 x

39
SELECT s.*
FROM sc
INNER JOIN student s
ON sc.sid = s.sid
GROUP BY sid
HAVING COUNT(1) = (SELECT COUNT(1) FROM course);
 
SELECT s.*
FROM sc
INNER JOIN student s
ON sc.sid = s.sid
GROUP BY sid
HAVING COUNT(sc.cid) = (SELECT COUNT(cid) FROM course);

40
-- 【分析】使用日期函数进行相减 --
SELECT *,YEAR(CURDATE())-YEAR(sage) 年龄
FROM student;
 
SELECT *,YEAR(NOW())-YEAR(sage) 年龄
FROM student;

！timestampdiff()\week()\month()\NOW()\YEAR(x)\CURDATE()

41

41 !
-- 【分析】日期相减函数timestampdiff(year\,参数1,参数2) --
SELECT *,
	  TIMESTAMPDIFF(YEAR,sage,NOW()) age
FROM student;

SELECT 
    Sno, 
    Sname, 
    YEAR(CURDATE()) - YEAR(Sage) - 
    (DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(Sage, '%m%d')) AS Age
FROM 
    student;

SELECT 
    Sno,
    Sname,
    Sage,
    CASE 
        WHEN DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(Sage, '%m%d')
        THEN YEAR(CURDATE()) - YEAR(Sage) - 1
        ELSE YEAR(CURDATE()) - YEAR(Sage)
    END as actual_age
FROM student;

42

42
-- 【分析】使用week()函数
SELECT *,
       WEEK(sage),
       WEEK(NOW())
FROM student
WHERE WEEK(sage)=WEEK(NOW());

43

43
SELECT *,
       WEEK(sage),
       WEEK(NOW())
FROM student
WHERE WEEK(sage)=WEEK(NOW())+1;

44

44
-- 【分析】使用month()函数 --
SELECT *,
       MONTH(sage),
       MONTH(NOW())
FROM student
WHERE MONTH(sage)=MONTH(NOW());

45

45
SELECT *,
       MONTH(sage),
       MONTH(NOW())
FROM student
WHERE MONTH(sage)=MONTH(NOW())+1;

案例三

1
2
3

https://blog.51cto.com/u_16099270/8081043
https://blog.csdn.net/abend11/article/details/136830750
https://blog.csdn.net/pattyi/article/details/116146698

数据库系统-刷题

数据库系统-刷题

mysql

常问问题

LEECODE题目

177.第N高的薪水 函数 dense_rank limit

178.分数排名 dense_rank b.score >= a.score

180.连续出现的数字 lag lead with 变量 多同表连接

184.部门工资最高的员工

185.部门工资前三高的所有员工

196.删除重复的电子邮箱 连表delete

197.上升的温度 datediff lag date_add

262.行程和用户 IF Case

avg精彩用法

534.游戏玩法分析 III sum() over(partition by

难 569.游戏玩法分析 IV partition by 中位数

难 571.给定数字的频率查询中位数

578.查询回答率最高的问题

579.查询员工的累计薪水

580.统计各专业学生人数

585.2016年的投资

597.好友申请1:总体通过率

601.体育馆的人流量 lag() lead() leaset()

602 case when

603.连续空余座位 lag lead ROWS BETWEEN AND

608 union

610.判断三角形

614.二级关注者

615.平均工资:部门与公司比较

618.学生地理信息报告 行转列

626.换座位 换序号

627.变更性别 update replace

1070.产品销售分析 川

1083.销售分析 | sum(表达式)

1097.游戏玩法分析 V MIN() OVER (PARTITION BY

1098.小众书籍

1107.每日新用户统计 MIN() OVER (PARTITION BY

1127.用户购买平台

1159.市场分析 I I

1174.即时食物配送Ⅱ

1179.重新格式化部门表 行转列

1193.每月交易| date_format(),left(),sub_string()

1194.锦标赛优胜者 列转行

1204.最后一个能进入巴士的人

1205.每月交易 | count(1),count(null),sum(0)

1212.查询球队积分 列转行

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

1264.页面推荐

1270.向公司 CEO 汇报工作的所有人 难

1280.学生们参加各科测试的次数 ifnull 构造行

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

1321.餐馆营业额变化增长 range row between and

1322.广告效果 SUM(action = ‘xxx’)

1336.每次访问的交易次数 难 应该是没读懂题目意思

1369.获取最近第二次的活动

1384.按年度列出销售总额 难

1412.查找成绩处于中游的学生

1435.制作会话柱状图

1440.计算布尔表达式的值

1454.活跃用户

1479.周内每天的销售情况

1484.按日期分组销售产品 group_concat

1517.查找拥有有效邮箱的用户 regexp REGEXP_LIKE 正则表达式

1527.患某种疾病的患者 like REGEXP

1613.找到遗失的ID 递归 recursive

1635.Hopper 公司查询! recursive

1651.Hopper 公司查询 川 RECURSIVE OVER (ROWS BETWEEN)

1661.每台机器的进程平均运行时间

1667.修复表中的名字 concat left right upper lower

1683.无效的推文

1709.访问日期之间最大的空档期 LEAD(xxx) OVER(partition by)

1972 同一天的第一个电话和最后一个电话 难

mysql注释问题

mysql修改列数据类型

1988

1990

2004

2020 简单

2026 简单

2041 简单

177.第N高的薪水函数 dense_rank limit

180.连续出现的数字 lag lead with 变量多同表连接

196.删除重复的电子邮箱连表delete

618.学生地理信息报告行转列

626.换座位换序号

1070.产品销售分析川

1179.重新格式化部门表行转列

1194.锦标赛优胜者列转行

1212.查询球队积分列转行

1270.向公司 CEO 汇报工作的所有人难

1336.每次访问的交易次数难应该是没读懂题目意思

1384.按年度列出销售总额难

1651.Hopper 公司查询川 RECURSIVE OVER (ROWS BETWEEN)

1972 同一天的第一个电话和最后一个电话难

2153.每辆车的乘客人数 II 难游标

3482.分析组织层级递归

16 变量的使用法一没看懂法二法六值得注意