【LeetCode】精选数据库70题（2022-10-14完结啦~）

努力努力再努力@_@

已于 2022-10-14 14:51:44 修改

阅读量927

点赞数

分类专栏： LeetCode SQL 文章标签： leetcode sql 学习数据库 mysql

于 2022-09-25 14:44:15 首次发布

本文链接：https://blog.csdn.net/guliguliguliguli/article/details/127036585

版权

LeetCode 同时被 2 个专栏收录

69 篇文章 2 订阅

订阅专栏

SQL

25 篇文章 1 订阅

订阅专栏

文章目录

题目链接
511 游戏玩法分析
512 游戏玩法分析II
534 游戏玩法分析III
550 游戏玩法分析IV
569 员工薪水中位数（困难...还没解决...窗口函数）
570 至少有5名直接下属的经理
571 给定数字的频率查询中位数
574 当选者
577 员工奖金
578 查询回答率最高的问题
579 查询员工的累计薪水
580 统计各专业学生人数
584 寻找用户推荐人
585 2016年的投资
586 订单最多的客户
597 好友申请
602 好友申请II
603 连续空余座位
607 销售员
608 树节点
610 判断三角形
612 平面上的最近距离
613 直线上的最近距离
614 二级关注者
615 平均工资：部门与公司比较
618 学生地理信息报告
619 只出现一次的最大数字
1045 买下所有产品的客户
1050 合作过至少三次的演员和导演
1068 产品销售分析
1069 产品销售分析II
1070 产品销售分析III
1075 项目员工 I
1076 项目员工II
1077 项目员工III
1082 销售分析
1083 销售分析II
1084 销售分析III
1097 游戏玩法分析 V
1098 小众书籍
1107 每日新用户统计
1112 每位学生的最高成绩
1113 报告的记录
1126 查询活跃业务
1127 用户购买平台
1132 报告的记录III
1141 查询近30天活跃用户
1142 过去30天的用户活动II
1148 文章浏览I
1149 文章浏览
1158 市场分析I
1159 市场分析II
1164 指定日期的产品价格
1173 即时食物配送
1174 即时食物配送II
1193 每月交易I
1194 锦标赛优胜者
1204 最后一个进入电梯的人
1205 每月交易II
1211 查询结果的质量和占比
1212 查询球队积分
1225 报告系统状态的连续日期
1241 每个帖子的评论数
1251 平均售价
1264 页面推荐
1270 向公司CEO汇报工作的所有人
1280 学生们参加各科测试的次数
1285 找到连续区间的开始和结束数字
1294 不同国家的天气类型
1303 求团队人数
1308 不同性别每日分数总计
1321 餐馆营业额变化增长

题目链接

💙LeetCode精选数据库70题

511 游戏玩法分析

按照player_id进行分组，找出其中最小的日期

SELECT player_id, MIN(event_date) first_login
FROM Activity
GROUP BY player_id;

512 游戏玩法分析II

SELECT player_id, device_id
FROM Activity
WHERE (player_id, event_date) IN (
    SELECT player_id, MIN(event_date)
    FROM Activity
    GROUP BY player_id
);

534 游戏玩法分析III

自连接

SELECT t1.player_id, t1.event_date, SUM(t2.games_played) games_played_so_far
FROM Activity t1
CROSS JOIN Activity t2
WHERE t1.player_id = t2.player_id AND t1.event_date >= t2.event_date
GROUP BY t1.player_id,t1.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;

550 游戏玩法分析IV

题目要求：计算在首次登陆的第二天再次登录的玩家的比率

也就是计算从首次登陆日起开始至少连续两天登陆的玩家的数量，然后除以玩家总数。

从首次登陆日期开始至少连续两天，等价于event_date必须含有首次登陆日期+1，至于event_date是不是包含别的日期，不是这道题关心的，满足了这一条件，即可说明在首次登陆的第二天再次登陆了。

首先找出每个player_id首次登陆的第二天的日期

SELECT player_id,DATE(MIN(event_date)+1)
FROM Activity
GROUP BY player_id;

在Activity表中把event_date和上述SQL的结果进行匹配。找出哪些player_id的event_date里含有首次登陆后的第二天的日期

SELECT ROUND(
    COUNT(player_id)/(SELECT COUNT(DISTINCT player_id) FROM Activity) ,2
) fraction
FROM Activity
WHERE (player_id,event_date) IN (
    SELECT player_id,DATE(MIN(event_date)+1)
    FROM Activity
    GROUP BY player_id
);

569 员工薪水中位数（困难…还没解决…窗口函数）

表的自连接

SELECT t1.id,t1.company,t1.salary
FROM Employee t1
JOIN EMployee t2
ON t1.company = t2.company
GROUP BY t1.id
HAVING SUM(t1.salary>=t2.salary) >= COUNT(t1.id)/2
AND SUM(t1.salary<=t2.salary) >= COUNT(t1.id)/2

输入

{"headers": {"Employee": ["id", "company", "salary"]}, "rows": {"Employee": [[1, "A", 2341],[2, "A", 341],[3, "A", 15],[4, "A", 15314],[5, "A", 451],[6, "A", 513],[7, "B", 15],[8, "B", 13],[9, "B", 1154],[10, "B", 1345],[11, "B", 1221],[12, "B", 234],[13, "C", 2345],[14, "C", 2645],[15, "C", 2645],[16, "C", 2652],[17, "C", 65]]}}

在这里插入图片描述
方法一：窗口函数+表的自连接

在原始表数据后面添加了ROW_NUMBER()进行排序，排序以后再进行表连接判断

比如，如果一个company下有6个（偶数个）员工，那么在HAVING子句中，只有需要为3、4的满足条件，如果一个company下有5个（奇数个）员工，那么在HAVING子句中，只有3满足条件，这样就可以解决偶数个员工输出两个，奇数个员工输出一个的问题了
在这里插入图片描述

WITH temp AS (
	SELECT
		id,
		company,
		salary,
		ROW_NUMBER() OVER(PARTITION BY company ORDER BY salary) rk
	FROM Employee
)

SELECT t1.id,any_value(t1.company) company,any_value(t1.salary) salary
FROM temp t1
JOIN temp t2
ON t1.company = t2.company
GROUP BY t1.id
HAVING SUM(t1.rk>=t2.rk) >= COUNT(t1.id)/2 AND SUM(t1.rk<=t2.rk)>=COUNT(t1.id)/2

方法二：正反序

正反序，就是按照salary排序，如果salary相同，再比较id

SELECT
	id,
	company,
	salary,
	ROW_NUMBER() OVER(PARTITION BY company ORDER BY salary,id) rk1,
	ROW_number() OVER(PARTITION BY company ORDER BY salary DESC,id DESC) rk2,
	COUNT(id) OVER(PARTITION BY company) cnt
FROM Employee

在这里插入图片描述

必须满足rk1>=cnt/2，且rk2>=cnt/2

SELECT
	id,
	company,
	salary
FROM (
	SELECT
		id,
		company,
		salary,
		ROW_NUMBER() OVER(PARTITION BY company ORDER BY salary,id) rk1,
		ROW_number() OVER(PARTITION BY company ORDER BY salary DESC,id DESC) rk2,
		COUNT(id) OVER(PARTITION BY company) cnt
	FROM Employee
) temp
WHERE rk1 >= cnt/2 AND rk2 >= cnt/2;

在这里插入图片描述
方法三：奇偶数讨论

和方法一有点类似，与方法二的区别在于，where子句的条件不同，但是，异曲同工

SELECT
	id,
	company,
	salary
FROM (
	SELECT
		id,
		company,
		salary,
		ROW_NUMBER() OVER(PARTITION BY company ORDER BY salary,id) rk,
		COUNT(id) OVER(PARTITION BY company) cnt
	FROM Employee
) temp
WHERE rk IN (cnt/2,cnt/2+1,(cnt+1)/2)

在这里插入图片描述

570 至少有5名直接下属的经理

表的自连接

SELECT t2.name
FROM Employee t1
JOIN Employee t2 ON t1.managerId = t2.id
GROUP BY t1.managerId
HAVING COUNt(t1.managerId) >= 5;

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

中位数就是将所有数字按照升序或者降序排列，然后取最中间的数字

数字个数是奇数的话，那么中位数会在这个序列中
数字个数是偶数的话，那么中位数是最中间的两个数的平均值

步骤：

使用sum(frequency) over() total_frequency计算出所有数字的个数，计算总数还可以使用SELECT SUM(frequency) total_frequency FROM Numbers
sum(frequency) over(order by num desc) desc_frequency，使用窗口函数，按照num降序计算出当前数字和之前数字出现的次数
sum(frequency) over(order by num asc) asc_frequency，使用窗口函数，按照num升序计算出当前数字和之前数字出现的次数

将查询出来的num，desc_frequency，asc_frequency，total_frequency作为临时表，筛选条件是desc_frequency >= total_frequency/2，asc_frequency >= total_frequency/2

SELECT 
    num,
    SUM(frequency) OVER(ORDER BY num ASC) desc_frequency,
    SUM(frequency) OVER(ORDER BY num DESC) asc_frequency,
    SUM(frequency) OVER() total_frequency
FROM Numbers


{"headers": 
["num", "desc_frequency", "asc_frequency", "total_frequency"],
 "values":
  [[3, 12, 1, 12], 
  [2, 11, 4, 12], 
  [1, 8, 5, 12], 
  [0, 7, 12, 12]]}

SELECT ROUND(AVG(num),1) median 
FROM (
    SELECT 
        num,
        SUM(frequency) OVER(ORDER BY num ASC) desc_frequency,
        SUM(frequency) OVER(ORDER BY num DESC) asc_frequency,
        SUM(frequency) OVER() total_frequency
    FROM Numbers
)temp
WHERE desc_frequency>=total_frequency/2 AND asc_frequency>=total_frequency/2;

574 当选者

SELECT name
FROM candidate t1
JOIN (
    SELECT candidateId, COUNT(*) votes
    FROM Vote
    GROUP BY candidateId
) t2 ON t1.id = t2.candidateId
ORDER BY votes DESC
LIMIT 1;

SELECT name
FROM candidate t1
JOIN (
    SELECT candidateId, COUNT(*) votes
    FROM Vote
    GROUP BY candidateId
    ORDER BY votes DESC
    LIMIT 1
) t2 ON t1.id = t2.candidateId;

577 员工奖金

题目要求选出所有bonus<1000的员工的name及其bonus，如果该员工没有bonus，也要输出，不过bonus那一栏为null，所以在将两个表连接时，使用外连接

SELECT name,bonus
FROM Employee t1 
LEFT JOIN Bonus t2 ON t1.empId = t2.empId
WHERE t1.empId NOT IN (
    SELECT empId
    FROM Bonus
    WHERE bonus >= 1000
);

SELECT name,bonus
FROM Employee t1 
LEFT JOIN Bonus t2 ON t1.empId = t2.empId
WHERE bonus < 1000 OR bonus IS NULL;

578 查询回答率最高的问题

SELECT question_id survey_log 
FROM (
    SELECT question_id, SUM(IF(action='answer',1,0))/SUM(IF(action='show',1,0)) rate
    FROM SurveyLog
    GROUP BY question_id
    ORDER BY rate DESC,question_id ASC
    LIMIT 1
) t;

SELECT question_id survey_log 
FROM SurveyLog
GROUP BY question_id
ORDER BY SUM(IF(action='answer',1,0))/SUM(IF(action='show',1,0)) DESC, question_id ASC
LIMIT 1;

579 查询员工的累计薪水

自连接

首先把每个员工最近一个月的薪水信息排除，生成一个临时表temp

然后将temp表进行自连接，连接的条件是Id相等，Month的差的范围在0，2，也就是最近三个月，最后使用SUM进行累加即可
在这里插入图片描述

WITH temp AS(
        SELECT Id,`Month`,Salary
        FROM Employee
        WHERE (Id,`Month`) NOT IN (
            SELECT Id, MAX(`Month`) max_month
            FROM Employee
            GROUP BY Id
        )
)

SELECT 
    t1.Id,
    t1.`Month`,
    SUM(t2.Salary) Salary
FROM temp t1
JOIN temp t2 ON t1.Id = t2.Id AND t1.`Month`-t2.`Month`>=0 AND t1.`Month`-t2.`Month`<=2
GROUP BY t1.Id,t1.`Month`
ORDER BY Id ASC, `Month` DESC;

窗口函数
在这里插入图片描述

SELECT
    id,
    `Month`,
    SUM(Salary) OVER(PARTITION BY Id ORDER BY `Month` RANGE 2 PRECEDING) Salary
FROM Employee
WHERE (Id,`Month`) NOT IN (
    SELECT Id,MAX(`Month`)
    FROM Employee
    GROUP BY Id
)
ORDER BY Id ASC,`Month` DESC;

580 统计各专业学生人数

SELECT dept_name, IFNULL(COUNT(t2.dept_id),0) student_number
FROM Department t1
LEFT JOIN Student t2 ON t1.dept_id = t2.dept_id
GROUP BY t1.dept_id
ORDER BY student_number DESC,dept_name ASC;

584 寻找用户推荐人

只加入referee_id != 2的条件，无法得到referee_id 为 NULL的记录，所以需要再加上referee_id IS NULL的条件

SELECT name
FROM customer
WHERE referee_id IS NULL OR referee_id != 2

585 2016年的投资

检查每一个TIV_2015是否是唯一的，如果不是唯一的且同时坐标是唯一的，那么这条记录就符合题目要求。应该被统计到答案中。

SELECT ROUND(SUM(TIV_2016),2) TIV_2016
FROM insurance
WHERE TIV_2015 IN (
    SELECT TIV_2015
    FROM insurance
    GROUP BY TIV_2015
    HAVING COUNT(*) > 1
) AND (LAT,LON) IN (
    SELECT LAT,LON
    FROM insurance
    GROUP BY LAT,LON
    HAVING COUNT(*)=1
);

上述SQL执行结果
在这里插入图片描述

SELECT ROUND(SUM(TIV_2016),2) TIV_2016
FROM (
    SELECT TIV_2016,
        COUNT(*) OVER(PARTITION BY TIV_2015) count_tiv_2015,
        COUNT(*) OVER(PARTITION BY LAT,LON) count_lat_lon
    FROM insurance
) t
WHERE count_tiv_2015 > 1 AND count_lat_lon = 1;

上述SQL执行结果
在这里插入图片描述

586 订单最多的客户

按照customer_number对每条记录进行分组，统计每个客户的订单数量，并按照订单数量从大到小进行排序，取出第一个，也就是订单记录最多的用户

SELECT customer_number
FROM (
    SELECT customer_number,COUNT(*) order_count
    FROM Orders
    GROUP BY customer_number
    ORDER BY order_count DESC
    LIMIT 1
) t;

597 好友申请

通过这题了解到，SELECT里面可以再写SELECT，本来一直想的是分开查询，最后怎么把两个查询结果合在一起…

COUNT(expr)函数返回不为空的记录总数，不过当RequestAccepted表中的记录数为0时，两个COUNT函数的结果相除，会是NULL值，所以外面要加上一层IFNULL函数

SELECT 
    ROUND(
        IFNULL(
        (SELECT COUNT(DISTINCT requester_id,accepter_id) FROM RequestAccepted) /
        (SELECT COUNT(DISTINCT sender_id,send_to_id) FROM FriendRequest),0),2
    ) accept_rate;

602 好友申请II

先分别用requester_id 和 accepter_id 分组，统计出每个人拥有的好友数量

因为成为好友是双向的，比如，1向3提出好友申请，3同意了，那么，站在1的角度，1有一个朋友是3，站在3的角度，3有一个朋友是1

最后将用requester_id 和 accepter_id 分组的两个表进行合并，再按照id再次合并，题目保证生成的测试用例保证拥有最多好友数目的只有一个人，那么我们就可以使用ORDER BY + LIMIT的组合，取出好友数最多的那个人的信息

SELECT id,SUM(num) num
FROM (
    SELECT requester_id id,COUNT(requester_id) num
    FROM RequestAccepted
    GROUP BY requester_id

    UNION ALL

    SELECT accepter_id id,COUNT(accepter_id) num
    FROM RequestAccepted
    GROUP BY accepter_id
) t
GROUP BY id
ORDER BY num DESC
Limit 1;

603 连续空余座位

用表进行自连接，不过From Cinema c1, Cinema c2不加任何过滤条件的话，就是笛卡尔积，所以需要加上两个seat_id值相减作为条件，最后在使用DISTINCT去重。

不使用DISTINCT，则会出现，比如3 4 5 连续，3 4，4 3，4 5，5 4，根据过滤条件，4会出现两次。

SELECT DISTINCT c1.seat_id
FROM Cinema c1,Cinema c2
WHERE ABS(c1.seat_id - c2.seat_id) = 1 AND c1.free = 1 AND c2.free = 1
ORDER BY c1.seat_id;

窗口函数 - MySQL-ROW_NUMBER()

with temp as (
    select
        seat_id,
        seat_id - row_number() over() as k
    from cinema where free = 1
)
select seat_id from temp where k in (
	select k from temp group by k having count(*) >= 2
);

607 销售员

SELECT name
FROM SalesPerson
WHERE sales_id NOT IN (
    SELECT DISTINCT sales_id
    FROM Orders
    WHERE com_id = (SELECT com_id
        FROM Company 
        WHERE name = 'RED')
);

608 树节点

使用union

SELECT id,'Root' Type
FROM tree
WHERE p_id IS NULL

union

SELECT id,'Inner' Type
FROM tree
WHERE id IN (
    SELECT DISTINCT p_id
    FROM tree
    WHERE p_id IS NOT NULL
) AND p_id IS NOT NULL

union

SELECT id,'Leaf' Type
FROM tree
WHERE id NOT IN (
    SELECT DISTINCT p_id
    FROM tree
    WHERE p_id IS NOT NULL
) AND p_id IS NOT NULL
ORDER BY id ASC;

CASE … WHEN …

当父节点不为空，并且本身也是父节点的节点，就是内部节点

SELECT id,
    CASE
        WHEN p_id IS NULL
          THEN 'Root'
        WHEN id IN (SELECT DISTINCT p_id FROM tree ) # AND p_id IS NOT NULL
          THEN 'Inner'
        ELSE 'Leaf'
    END AS Type
FROM
    tree
ORDER BY id ASC;

610 判断三角形

这题点击“执行代码”以后，总是出错，但是点击“提交”，却能正常通过…

SELECT x,y,z,(
    CASE WHEN x+y>z AND y+z>x AND x+z>y THEN 'Yes'
        ELSE 'No' 
    END
) triangle
FROM Triangle;

612 平面上的最近距离

SELECT ROUND(
        MIN(SQRT(
            POWER(t1.x-t2.x,2)+power(t1.y-t2.y,2)
        ))
    ,2) shortest 
FROM Point2D t1 CROSS JOIN Point2D t2
WHERE (t1.x,t1.y)!=(t2.x,t2.y);

其实没有必要计算每一个点与其他所有点的距离，因为这些距离可能已经被计算过了。那么该如何避免重复计算呢？

当把一个表与它自己连接以后，可以只计算每个点与X坐标比它大的点坐标的距离。这样可以避免很多重复的计算

SELECT ROUND(
        MIN(SQRT(POWER(t1.x-t2.x,2)+POWER(t1.y-t2.y,2)))
    ,2) shortest
FROM Point2D t1  CROSS JOIN Point2D t2
WHERE (t1.x <= t2.x AND t1.y > t2.y) OR (t1.x <= t2.x AND t1.y < t2.y) OR (t1.x < t2.x AND t1.y = t2.y);

613 直线上的最近距离

point表进行自连接，不能自己和自己连，即可

SELECT MIN(ABS(t1.x - t2.x)) shortest
FROM `point` t1, `point` t2
WHERE t1.x != t2.x
ORDER BY t1.x ASC

614 二级关注者

SELECT DISTINCT follower,num
FROM follow t1
JOIN (
    SELECT followee,COUNT(followee) num
    FROM follow
    GROUP BY followee
) t2 ON t1.follower = t2.followee
ORDER BY follower ASC;

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

在这里插入图片描述
先按照年-月进行分组，求出每一个月公司的平均工资

再按照年月+部门分组，求出每一个月，每一个部门的平均工资，最后，将两个表按照日期进行连接，比较公司平均工资和部门平均工资

SELECT 
    t1.pay_month, 
    department_id, 
    CASE WHEN t1.avg_amount > department_avg_amount THEN 'lower' 
        WHEN t1.avg_amount = department_avg_amount THEN 'same'
        WHEN t1.avg_amount < department_avg_amount THEN 'higher' END comparison
FROM (
    SELECT 
        DATE_FORMAT(pay_date,'%Y-%m') pay_month, 
        SUM(amount)/COUNT(t1.employee_id) avg_amount
    FROM Salary t1
    JOIN employee t2
    ON t1.employee_id  = t2.employee_id
    GROUP BY DATE_FORMAT(pay_date,'%Y-%m')
) t1
JOIN (
    SELECT 
        DATE_FORMAT(pay_date,'%Y-%m') pay_month, 
        t2.department_id,
        SUM(amount)/COUNT(t1.employee_id) department_avg_amount
    FROM Salary t1
    JOIN employee t2
    ON t1.employee_id  = t2.employee_id
    GROUP BY DATE_FORMAT(pay_date,'%Y-%m'),t2.department_id
) t2
ON t1.pay_month = t2.pay_month;

窗口函数
在这里插入图片描述

SELECT DISTINCT *
FROM (
    SELECT 
        DATE_FORMAT(t1.pay_date,'%Y-%m') pay_month,
        t2.department_id,
        CASE
            WHEN AVG(amount) OVER(PARTITION BY t2.department_id,t1.pay_date) < AVG(amount) OVER(PARTITION BY t1.pay_date) THEN 'lower'
            WHEN AVG(amount) OVER(PARTITION BY t2.department_id,t1.pay_date) = AVG(amount) OVER(PARTITION BY t1.pay_date) THEN 'same'
            WHEN AVG(amount) OVER(PARTITION BY t2.department_id,t1.pay_date) > AVG(amount) OVER(PARTITION BY t1.pay_date) THEN 'higher'
        END comparison
    FROM salary t1
    JOIN employee t2
    ON t1.employee_id = t2.employee_id
) temp;

618 学生地理信息报告

这种类型的题之前没有见过，输出的结果就像是把表转了个方向

首先，FROM后面的子表按照continent分组，按照name排序

SELECT *,ROW_NUMBER() OVER(PARTITION BY continent ORDER BY `name`) rn
FROM student

在这里插入图片描述

题目中指明最后的结果：每个学生按照姓名的字母顺序依次排列在对应的大洲的下面，所以，ROW_NUMBER()结果相同的，都在同一行

最后使用CASE WHEN 取出对应的name

SELECT 
    MAX(CASE continent WHEN 'America' THEN `name` ELSE NULL END )America,
    MAX(CASE continent WHEN 'Asia' THEN `name` ELSE NULL END) Asia,
    MAX(CASE continent WHEN 'Europe' THEN `name` ELSE NULL END) Europe
FROM (
	SELECT *,ROW_NUMBER() OVER(PARTITION BY continent ORDER BY `name`) rn
	FROM student
) temp
GROUP BY rn;

在这里插入图片描述

或者把MAX换成MIN都可以，根据rn分组以后的，每一组肯定只有一个数，但是直接输出会报错，只要把每一个组中的值取出来就行，MIN或者MAX都可以

SELECT 
    MIN(CASE continent WHEN 'America' THEN `name` ELSE NULL END )America,
    MIN(CASE continent WHEN 'Asia' THEN `name` ELSE NULL END) Asia,
    MIN(CASE continent WHEN 'Europe' THEN `name` ELSE NULL END) Europe
FROM (
	SELECT *,ROW_NUMBER() OVER(PARTITION BY continent ORDER BY `name`) rn
	FROM student
) temp
GROUP BY rn;

619 只出现一次的最大数字

# Write your MySQL query statement below
SELECT IFNULL(
    (SELECT num FROM MyNumbers
    GROUP BY num
    HAVING COUNT(num) = 1
    ORDER BY num DESC
    LIMIT 1),
    NULL) num;

1045 买下所有产品的客户

SELECT customer_id
FROM Customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (
    SELECT COUNT(product_key) prduct_cnt
    FROM Product
);

1050 合作过至少三次的演员和导演

SELECT actor_id, director_id
FROM ActorDirector
GROUP BY actor_id,director_id
HAVING COUNT(*) >= 3;

1068 产品销售分析

表连接操作

SELECT product_name, year, price
FROM Sales t1
LEFT JOIN Product t2 ON t1.product_id = t2.product_id;

1069 产品销售分析II

SELECT product_id, SUM(quantity) total_quantity
FROM Sales
GROUP BY product_id;

1070 产品销售分析III

SELECT product_id,year first_year,quantity,price
FROM Sales
WHERE (product_id,year) IN (
    SELECT product_id,MIN(year) first_year
    FROM Sales
    GROUP BY product_id
);

1075 项目员工 I

SELECT project_id, ROUND(AVG(experience_years),2) average_years
FROM Project t1
JOIN Employee t2 ON t1.employee_id = t2.employee_id
GROUP BY project_id;

1076 项目员工II

SELECT project_id
FROM Project
GROUP BY project_id
HAVING COUNT(*) >= ALL(
    SELECT COUNT(*)
    FROM Project
    GROUP BY project_id
);

1077 项目员工III

SELECT project_id,t1.employee_id
FROM Project t1
JOIN Employee t2 ON t1.employee_id = t2.employee_id
WHERE (project_id,experience_years) IN (
    SELECT project_id,MAX(experience_years)
    FROM Project t1
    JOIN Employee t2 ON t1.employee_id = t2.employee_id
    GROUP BY project_id
);

1082 销售分析

SELECT seller_id
FROM Sales
GROUP BY seller_id
HAVING SUM(price) >= ALL(
    SELECT SUM(price)
    FROM Sales
    GROUP BY seller_id
);

1083 销售分析II

查找购买了S8但是没有购买iPhone的买家

FROM子句里面的子查询，查找的是购买了S8的买家，WHERE子句里查找的是，没有购买iPhone的买家

SELECT DISTINCT buyer_id
FROM (
    SELECT buyer_id
    FROM Sales t1
    JOIN Product t2 ON t1.product_id = t2.product_id
    WHERE product_name = 'S8'
) t
WHERE buyer_id NOT IN (
    SELECT buyer_id
    FROM Sales t1
    JOIN Product t2 ON t1.product_id = t2.product_id
    WHERE product_name = 'iPhone'
);

SELECT DISTINCT buyer_id
FROM Sales t1
JOIN Product t2 ON t1.product_id = t2.product_id
WHERE product_name = 'S8' AND buyer_id NOT IN (
    SELECT buyer_id
    FROM Sales t1
    JOIN Product t2 ON t1.product_id = t2.product_id
    WHERE product_name = 'iPhone'
);

SELECT buyer_id
FROM Sales t1
JOIN Product t2 ON t1.product_id = t2.product_id
GROUP BY buyer_id
HAVING SUM(IF(product_name='S8',1,0))>=1 
    AND SUM(IF(product_name='iPhone',1,0))=0

1084 销售分析III

SELECT t1.product_id, product_name
FROM Sales t1 
JOIN Product t2 ON t1.product_id = t2.product_id
GROUP BY t1.product_id
HAVING (MAX(sale_date) BETWEEN '2019-01-01' AND '2019-03-31') AND (MIN(sale_date) BETWEEN '2019-01-01' AND '2019-03-31');

SELECT t1.product_id, product_name
FROM Sales t1 
JOIN Product t2 ON t1.product_id = t2.product_id
GROUP BY t1.product_id
HAVING MAX(sale_date) <= '2019-03-31' AND MIN(sale_date) >= '2019-01-01';

1097 游戏玩法分析 V

先按照player_id进行分组，找到每一个player的登陆日期，然后计算出登陆日期后一天的日期next_date，将结果作为一张临时表

将该临时表与Activity表进行左外连接，连接的条件是两张表的player_id的值要是一样的，并且Activity中得有next_date的日期，按照login_date进行分组，最后统计相应的数据即可

WITH temp AS (
	SELECT 
		player_id,
		MIN(event_date) login_date,
		DATE_SUB(MIN(event_date),INTERVAL -1 DAY) next_day
	FROM Activity
	GROUP BY player_id
)

SELECT 
	t1.login_date install_dt,
	COUNT(t1.login_date) installs,
	ROUND(COUNT(t2.event_date)/COUNT(t1.login_date),2) Day1_retention 
FROM temp t1
LEFT JOIN Activity t2
ON t1.player_id = t2.player_id AND t1.next_day = t2.event_date
GROUP BY login_date

1098 小众书籍

SELECT t1.book_id,t1.name
FROM (
    SELECT book_id,name
    FROM Books
    WHERE DATEDIFF('2019-06-23',available_from) > 31 
) t1
LEFT JOIN (
    SELECT book_id,quantity,dispatch_date
    FROM Orders
    WHERE DATEDIFF('2019-06-23',dispatch_date) <= 365
) t2 ON t1.book_id = t2.book_id
GROUP BY t1.book_id
HAVING SUM(IFNULL(t2.quantity,0))<10 ;

SELECT t1.book_id,t1.name
FROM books t1
LEFT JOIN Orders t2
ON t1.book_id = t2.book_id AND DATEDIFF('2019-06-23',dispatch_date)<=365
WHERE DATEDIFF('2019-06-23',available_from) > 31
GROUP BY t1.book_id
HAVING SUM(IFNULL(quantity,0))<10;

1107 每日新用户统计

SELECT min_activity_date login_date, COUNT(min_activity_date) user_count
FROM (
    SELECT user_id, MIN(activity_date) min_activity_date
    FROM Traffic
    WHERE activity = 'login'
    GROUP BY user_id
    HAVING MIN(activity_date) >= '2019-04-01'
) t1
GROUP BY min_activity_date;

SELECT min_activity_date login_date, COUNT(min_activity_date) user_count
FROM (
    SELECT user_id, MIN(activity_date) min_activity_date
    FROM Traffic
    WHERE activity = 'login'
    GROUP BY user_id
) t1
WHERE min_activity_date >= '2019-04-01'
GROUP BY min_activity_date;

1112 每位学生的最高成绩

SELECT student_id, MIN(course_id) course_id, grade
FROM Enrollments
WHERE (student_id,grade) IN (
    SELECT student_id, MAX(grade)
    FROM Enrollments
    GROUP BY student_id
)
GROUP BY student_id
ORDER BY student_id ASC;

1113 报告的记录

SELECT extra report_reason, COUNT(DISTINCT post_id) report_count
FROM Actions
WHERE action_date = '2019-07-04' AND action = 'report' AND extra IS NOT NULL
GROUP BY extra;

1126 查询活跃业务

SELECT t1.business_id
FROM Events t1
JOIN (
    SELECT event_type, AVG(occurences) avg_occurences 
    FROM Events
    GROUP BY event_type
) t2 ON t1.event_type = t2.event_type
GROUP BY t1.business_id
HAVING SUM(t1.occurences > t2.avg_occurences) >= 2

SELECT t1.business_id
FROM Events t1
JOIN (
    SELECT event_type, AVG(occurences) avg_occurences 
    FROM Events
    GROUP BY event_type
) t2 ON t1.event_type = t2.event_type
WHERE t1.occurences > t2.avg_occurences
GROUP BY t1.business_id
HAVING COUNT(*) >= 2;

1127 用户购买平台

首先按照spend_date和user_id分组，根据购买记录确定用户的platform是both、mobile还是desktop，生成子表t1
在这里插入图片描述

其次，将both、mobile、desktop作为单独一个表t2
在这里插入图片描述

t2与t1进行笛卡尔积连接

在这里插入图片描述
最后按照t1.platform和t2.spend_date进行分组，得到结果

在这里插入图片描述

SELECT
	t2.spend_date,
	t1.platform,
	SUM(IF(t1.platform=t2.platform,amount,0)) total_amount,
	COUNT(IF(t1.platform=t2.platform,1,NULL)) total_users
FROM (
	SELECT 'both' platform
	UNION
	SELECT 'mobile' platform
	UNION
	SELECT 'desktop' platform
) t1,
(
	SELECT
		spend_date,
		IF(COUNT(DISTINCT platform)=2,'both',any_value(platform)) platform,
		user_id,
		SUM(amount) amount
	FROM Spending
	GROUP BY spend_date,user_id
) t2
GROUP BY t1.platform,t2.spend_date

1132 报告的记录III

注意：相同的post_id只能算一个

SELECT ROUND(AVG(avg_daily_percent)*100,2) average_daily_percent 
FROM (
    SELECT COUNT(DISTINCT t2.post_id)/COUNT(DISTINCT t1.post_id) avg_daily_percent
    FROM Actions t1
    LEFT JOIN Removals t2 ON t1.post_id = t2.post_id
    WHERE extra = 'spam'
    GROUP BY t1.action_date
) temp;

1141 查询近30天活跃用户

DATEDIFF的结果除了要小于29之外，还要大于等于0，如果没有大于等于0的条件，会查找到2019-07-27之后的日子

SELECT activity_date day,COUNT(DISTINCT user_id) active_users
FROM Activity
WHERE DATEDIFF('2019-07-27',activity_date)<=29 
    AND DATEDIFF('2019-07-27',activity_date)>=0
GROUP BY activity_date;

1142 过去30天的用户活动II

SELECT ROUND(IFNULL(COUNT(DISTINCT session_id)/COUNT(DISTINCT user_id),0),2)average_sessions_per_user
FROM Activity
WHERE DATEDIFF('2019-07-27',activity_date)<=29;

1148 文章浏览I

SELECT DISTINCT viewer_id id
FROM Views t1
WHERE viewer_id = author_id
ORDER BY id ASC;

1149 文章浏览

注意：需要加上DISTINCT，因为，可能存在同一个用户，在不同的天都读了两篇及以上的文章，这样，在最后输出的时候，同一个id会出现多次

SELECT DISTINCT viewer_id id
FROM Views
GROUP BY viewer_id, view_date
HAVING COUNT(DISTINCT article_id) >= 2
ORDER BY id ASC;

1158 市场分析I

SELECT user_id buyer_id, join_date, COUNT(IF(YEAR(order_date)=2019,1,NULL)) orders_in_2019
FROM Users t1
LEFT JOIN Orders t2
ON t1.user_id = t2.buyer_id
GROUP BY t1.user_id

1159 市场分析II

使用ROW_NUMBER()

WITH temp AS (
	SELECT
		seller_id,
		item_brand,
		favorite_brand,
		ROW_NUMBER() OVER(PARTITION BY t1.seller_id ORDER BY order_date) ranking
	FROM Orders t1,Items t2, Users t3
	WHERE t1.seller_id = t3.user_id AND t1.item_id = t2.item_id
)

SELECT t1.user_id,IF(t2.seller_id IS NULL,'no','yes') 2nd_item_fav_brand
FROM (
	SELECT user_id
	FROM Users	
)t1
LEFT JOIN (
	SELECT seller_id
	FROM temp
	WHERE ranking = 2 AND item_brand = favorite_brand
) t2 ON t1.user_id = t2.seller_id

使用NTH_VALUE()

注意，在使用NTH_VALUE时，有可能某一个seller_id卖的物品数大于两个，所以，在进行LEFT JOIN的时候，会出现重复的值，所以要在t2这个子查询中使用DISTINCT

WITH temp AS (
	SELECT
		seller_id,
		favorite_brand,
		NTH_VALUE(item_brand,2) OVER(PARTITION BY t1.seller_id ORDER BY order_date) item_brand
	FROM Orders t1,Items t2, Users t3
	WHERE t1.seller_id = t3.user_id AND t1.item_id = t2.item_id
)

SELECT t1.user_id seller_id ,IF(t2.seller_id IS NULL,'no','yes') 2nd_item_fav_brand
FROM (
	SELECT user_id
	FROM Users	
)t1
LEFT JOIN (
	SELECT DISTINCT seller_id 
	FROM temp
	WHERE item_brand IS NOT NULL AND item_brand = favorite_brand
) t2 ON t1.user_id = t2.seller_id

其他思路：使用自连接，having+sum判断

第一步，先将表自连接，两个Orders表，一个Items表

SELECT *
FROM Orders t1,Orders t2,Items t3
WHERE t1.seller_id = t2.seller_id AND t1.item_id = t3.item_id
ORDER BY t1.order_date,t1.seller_id;

在这里插入图片描述
按照order_id进行分组，统计比当前订单更早的订单数，若为1，说明当前订单是对应用户卖出的第二单

SELECT t1.user_id seller_id,IF(t2.item_brand=t1.favorite_brand,'yes','no') 2nd_item_fav_brand 
FROM Users t1
LEFT JOIN (
	SELECT 
		any_value(t1.seller_id) seller_id,
		any_value(item_brand) item_brand
	FROM Orders t1,Orders t2,Items t3
	WHERE t1.seller_id = t2.seller_id AND t1.item_id = t3.item_id
	GROUP BY t1.order_id
	HAVING SUM(t1.order_date > t2.order_date) = 1
) t2
ON t1.user_id = t2.seller_id

1164 指定日期的产品价格

本题关键点在于找到2019-08-16前所有有改动的产品及其最新价格和没有修改过价格的产品

可以先找到所有的产品，再找到所有2019-08-16前有修改的产品和他们最新的价格，使用left join将两个查询联合。如果产品没有价格，说明没有修改过，设置为10，如果有价格，设置为最新的价格。

找出所有的产品

SELECT DISTINCT product_id
FROM Products;

找出2019-08-16前所有有改动的产品的最新价格

使用max函数找到产品最新修改的时间，使用where查询限制时间小于等于2019-08-16

SELECT product_id, MAX(change_date)
FROM Products
WHERE change_date <= '2019-08-16'
GROUP BY product_id;

使用where子查询，根据prodct_id和change_date找到对应的价格

SELECT product_id, new_price
FROM Products
WHERE (product_id,change_date) IN (
    SELECT product_id, MAX(change_date)
    FROM Products
    WHERE change_date <= '2019-08-16'
    GROUP BY product_id
);

经过上面两步已经找到了所有的产品和已经修改过价格的产品。使用left join得到所有产品的最新价格，如果没有，设置为10

SELECT t1.product_id,IFNULL(t2.price,10) price
FROM (
    SELECT DISTINCT product_id
    FROM Products
) t1
LEFT JOIN (
    SELECT product_id, new_price price
    FROM Products
    WHERE (product_id,change_date) IN (
        SELECT product_id, MAX(change_date)
        FROM Products
        WHERE change_date <= '2019-08-16'
        GROUP BY product_id
    )
) t2 ON t1.product_id = t2.product_id;

1173 即时食物配送

SELECT ROUND((
    SELECT COUNT(*)*100 FROM Delivery WHERE order_date = customer_pref_delivery_date 
) / COUNT(*),2) immediate_percentage 
FROM Delivery;

1174 即时食物配送II

SELECT ROUND(
    SUM(order_date = customer_pref_delivery_date) * 100 / COUNT(DISTINCT customer_id)
    ,2) immediate_percentage 
FROM Delivery
WHERE (customer_id,order_date) IN (
    SELECT customer_id,MIN(order_date) min_order_date
    FROM Delivery
    GROUP BY customer_id
);

1193 每月交易I

SELECT 
	DATE_FORMAT(trans_date,'%Y-%m') `month`, 
	country,
	COUNT(id) trans_count, 
	SUM(state='approved') approved_count,
	SUM(amount) trans_total_amount,
	SUM(IF(state='approved',amount,0)) approved_total_amount
FROM Transactions
GROUP BY `month`,country;

1194 锦标赛优胜者

首先计算出每位选手的总分，然后用ROW_NUMBER()给选手排序，按照group_id分组，按照score降序排列，如果score一样，则player_id小的优先，最后返回每一个组需要唯一的选手

注意在使用UNION时，只能使用UNION ALL，不能使用UNION，因为UNION有去重的作用

WITH temp AS (
	SELECT
		player,
		SUM(score) score
	FROM(
		(
			SELECT first_player player,SUM(first_score) score
			FROM Matches
			GROUP BY first_player
		)
		UNION ALL
		(
			SELECT second_player player,SUM(second_score) score
			FROM Matches
			GROUP BY second_player
		)
	)t1
	GROUP BY player
)

SELECT group_id,player_id
FROM(
	SELECT 
		player_id,
		group_id,
		ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY score DESC ,player_id ASC) rk
	FROM temp
	JOIN Players t2
	ON temp.player = t2.player_id
) t
WHERE rk = 1

1204 最后一个进入电梯的人

需要根据turn排序，并累加weight，找到最后一个使得总和小于等于1000的person_name。

只需要计算出每个人进去后的总和，找到总和小于等于1000的最后一个人即可。

可以使用自连接，将b表中的每一条数据和a表中的每一条数据连接。假设Queue有3条记录，那么自连接后将会有9条数据，分别为(a1 b1) (a1 b2) (a1 b3) (a2 b1) (a2 b2) (a2 b3) (a3 b1) (a3 b2) (a3 b3)

接下来对数据进行处理，使用a表的person_id表示自身，b表中的数据表示为包括自己在内的所有人。使用GROUP BY a.person_id处理每个人的数据。因为要计算每个人的weght加上之前所有人的weight，使用查询条件a.turn>=b.turn找到所有在他之间以及他自己的重量。再使用SUM计算总和并过滤掉大于1000的数据

SELECT q1.person_name
FROM Queue q1
CROSS JOIN Queue q2
WHERE q1.turn >= q2.turn
GROUP BY q1.person_id
HAVING SUM(q2.weight) <= 1000
ORDER BY SUM(q2.weight) DESC
LIMIT 1;

1205 每月交易II

本题难点在于如何处理Chargebacks表，每项退单都对应于之前进行的交易，即使未经批准，说明我们应该使用full outer join构造chargeback这个state，然后再对年月GROUP BY计算各类指标。

union all

SELECT * FROM Transactions
UNION ALL
SELECT 
    t1.trans_id id,
    t2.country,
    'chargeback' state, 
    t2.amount,
    t1.trans_date
FROM Chargebacks t1
LEFT JOIN Transactions t2
ON t1.trans_id = t2.id;

计算指标

按照（month，country）分组

还要忽略所有为零的行，也就是当approved_count ，chargeback_count 都为0时，不输出

WITH cet AS(
    SELECT * FROM Transactions
    UNION ALL
    SELECT 
        t1.trans_id id,
        t2.country,
        'chargeback' state, 
        t2.amount,
        t1.trans_date
    FROM Chargebacks t1
    LEFT JOIN Transactions t2
    ON t1.trans_id = t2.id
)

SELECT 
    DATE_FORMAT(trans_date,'%Y-%m') `month`,
    country,
    SUM(state='approved') approved_count,
    SUM(IF(state='approved',amount,0)) approved_amount,
    SUM(state='chargeback') chargeback_count,
    SUM(IF(state='chargeback',amount,0)) chargeback_amount 
FROM cet
GROUP BY `month`,country
HAVING approved_count OR chargeback_count;
# HAVING approved_amount OR chargeback_amount;

1211 查询结果的质量和占比

SELECT 
    query_name, 
    ROUND(SUM(rating/position)/COUNT(*),2) quality, 
    ROUND(SUM(IF(rating<3,1,0))*100/COUNT(*),2) poor_query_percentage
FROM Queries
GROUP BY query_name;

1212 查询球队积分

SELECT team_id,team_name,SUM(num_points) num_points
FROM (
    # 作为主队
    SELECT 
        t1.team_id,
        t1.team_name,
        SUM(IFNULL(
            CASE WHEN host_goals > guest_goals THEN 3
            WHEN host_goals = guest_goals THEN 1 END
            ,0)) num_points
    FROM Teams t1
    LEFT JOIN Matches t2
    ON t1.team_id = t2.host_team
    GROUP BY t1.team_id

    UNION ALL

    #作为客队
    SELECT 
        t1.team_id,
        t1.team_name,
        SUM(IFNULL(
            CASE WHEN host_goals < guest_goals THEN 3
            WHEN host_goals = guest_goals THEN 1 END
            ,0)) num_points
    FROM Teams t1
    LEFT JOIN Matches t2
    ON t1.team_id = t2.guest_team
    GROUP BY t1.team_id
) t
GROUP BY team_id
ORDER BY num_points DESC,team_id ASC;

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

首先将Succeeded表和Failed表中的数据打标签，标明该条数据来源于哪一张表，最后用UNION将两张表连接起来

SELECT 
	fail_date AS `date`,
	'failed' AS period_state 
FROM Failed
UNION
SELECT 
	success_date,
	'succeeded'    
FROM Succeeded 
ORDER BY `date`

在这里插入图片描述

求解某些记录是否连续就会想到使用ROW_NUMBER，当某几条记录和ROW_NUMBER的差是一样的时候，就说明它们是连续的，但是这也是针对与ROW_NUMBER相减的是数字才行，现在的类型是Date，该怎么办呢？

首先，按照date进行排序，对它们进行标号，然后按照period_state进行分组，再按照date进行排序，再对它们进行一次标号，两次标号进行相减，值一样的，就说明日期是连续的

WITH temp AS(
	SELECT 
		fail_date AS `date`,
		'failed' AS period_state 
	FROM Failed
	UNION
	SELECT 
		success_date,
		'succeeded'    
	FROM Succeeded 
	ORDER BY `date`
)

SELECT
    period_state,
    `date`,
    row_number() over(ORDER BY `date`) date_num,
    row_number() over(PARTITION BY period_state ORDER BY `date`) period_num,
    row_number() over(ORDER BY `date`) - 
    row_number() over(PARTITION BY period_state ORDER BY `date`) AS rnk_diff
FROM temp
WHERE YEAR(`date`) = 2019

在这里插入图片描述

WITH temp AS(
	SELECT 
		fail_date AS `date`,
		'failed' AS period_state 
	FROM Failed
	UNION
	SELECT 
		success_date,
		'succeeded'    
	FROM Succeeded 
	ORDER BY `date`
)


SELECT 
	period_state,
	MIN(`date`) start_date,
	MAX(`date`) end_date
FROM (
	SELECT
		period_state,
		`date`,
		row_number() over(ORDER BY `date`) - 
		row_number() over(PARTITION BY period_state ORDER BY `date`) AS rnk_diff
	FROM temp
	WHERE YEAR(`date`) = 2019
) t
GROUP BY period_state,rnk_diff
ORDER BY start_date ASC;

1241 每个帖子的评论数

SELECT t1.sub_id post_id, COUNT(DISTINCT t2.sub_id) number_of_comments
FROM (
    SELECT DISTINCT sub_id
    FROM Submissions
    WHERE parent_id IS NULL
) t1 
LEFT JOIN Submissions t2
ON t1.sub_id = t2.parent_id
GROUP BY t1.sub_id
ORDER BY post_id ASC;

1251 平均售价

除了使用product_id进行表连接时，还需要保证purchase_date在start_date和end_date之间

SELECT t1.product_id, ROUND(SUM(units*price)/SUM(units),2) average_price
FROM Prices t1
JOIN UnitsSold t2 ON t1.product_id = t2.product_id AND purchase_date BETWEEN start_date AND end_date
GROUP BY product_id;

1264 页面推荐

SELECT DISTINCT page_id recommended_page 
FROM Likes
WHERE page_id NOT IN (
    # id为1的用户喜欢的页面
    SELECT page_id
    FROM Likes
    WHERE user_id = 1
) AND user_id IN (
    # 朋友id
    SELECT user2_id
    FROM Friendship
    WHERE user1_id = 1
    UNION
    SELECT user1_id
    FROM Friendship
    WHERE user2_id = 1
);

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

方案一 UNION ALL + 子查询

本题规定经理之间的间接关系不超过三个经理，那么我们可以分别求出3层的人数，最后汇总到一起。

首先第一层是CEO的直接汇报人，需要把CEO排除掉：

SELECT employee_id
FROM Employees
WHERE manager_id = 1 AND employee_id != 1

第二层汇报人的maager_id就是第一层需要向CEO汇报的人，可以使用子查询，求出第二层的人


SELECT employee_id
FROM Employees
WHERE manager_id IN (
    SELECT employee_id
    FROM Employees
    WHERE manager_id = 1 AND employee_id != 1
)

同样，第三层的汇报人，就是第二步求出来的人

SELECT employee_id
FROM Employees
WHERE manager_id IN (
    SELECT employee_id
    FROM Employees
    WHERE manager_id IN (
        SELECT employee_id
        FROM Employees
        WHERE manager_id = 1 AND employee_id != 1
    ) 
);

至此，就求出来了所有汇报的人，然后只需要用UNION ALL将他们汇总到一起，输出即可。

答案如下

在这里插入图片描述

(
    SELECT employee_id
    FROM Employees
    WHERE manager_id = 1 AND employee_id != 1

    UNION ALL

    SELECT employee_id
    FROM Employees
    WHERE manager_id IN (
        SELECT employee_id
        FROM Employees
        WHERE manager_id = 1 AND employee_id != 1
    )
) 

UNION ALL

SELECT employee_id
FROM Employees
WHERE manager_id IN (
    SELECT employee_id
    FROM Employees
    WHERE manager_id IN (
        SELECT employee_id
        FROM Employees
        WHERE manager_id = 1 AND employee_id != 1
    ) 
);

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

COUNT()里面写 t2.subject_name 和 t3.subject_name的效果还是不一样的

SELECT t1.student_id, t1.student_name, t2.subject_name, COUNT(t3.subject_name) attended_exams
FROM Students t1 CROSS JOIN Subjects t2
LEFT JOIN Examinations t3 ON t1.student_id = t3.student_id AND t2.subject_name = t3.subject_name
GROUP BY t1.student_id,t2.subject_name
ORDER BY  t1.student_id,t2.subject_name;

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

方案一窗口函数

如何知道连续区间的开始和结束数字，首先需要知道这个区间

一个连续区间的数减去某个个规律的数结果应该是一样的

基于这个思路会想到，将这些数按照从小到大的顺序进行排列，那么这个数字减去它在这张表中的排列顺序，如果是一个区间内的数，那么结果应该是一样的。

SELECT
    log_id,
    log_id - row_number() over() diff
FROM Logs

上面SQL的运行结果，可以看到，如果是连续的数字，log_id - row_number()的值是一样的
在这里插入图片描述

SELECT 
    MIN(log_id) start_id,
    MAX(log_id) end_id
FROM (
    SELECT
        log_id,
        log_id - row_number() over() diff
    FROM Logs
) t
GROUP BY diff;

在这里插入图片描述

方案二

第一步，一个连续的区间，start_id -1 和 end_id + 1肯定不在表中

将log_id-1的值肯定不在Logs中，将符合条件的这些值的log_id查询出来就是start_id

SELECT log_id start_id, row_number() over(ORDER BY log_id) rank_id
FROM Logs
WHERE log_id -1 NOT IN (SELECT log_id FROM Logs);

在这里插入图片描述

第二步，同理，end_id+1的值肯定不在Logs中

SELECT log_id end_id, row_number() over(ORDER BY log_id) rank_id
FROM Logs
WHERE log_id + 1 NOT IN (SELECT log_id FROM Logs);

在这里插入图片描述

将第一步的结果作为临时表t1，将第二步的结果作为临时表t2

一个区间有start_id，就一定会有end_id，所以，start_id和end_id的数量应该是相等的
因为start_id和end_id的数量相同，所以可以使用排序函数分别对t1和t2表进行排序 row_number() over(order by log_id) rank_id
两表的连接条件就是rank_id相等

SELECT start_id, end_id
FROM (
    SELECT log_id start_id, row_number() over(ORDER BY log_id) rank_id
    FROM Logs
    WHERE log_id - 1 NOT IN (SELECT log_id FROM Logs)
) t1 JOIN (
    SELECT log_id end_id, row_number() over(ORDER BY log_id) rank_id
    FROM Logs
    WHERE log_id + 1 NOT IN (SELECT log_id FROM Logs)
) t2 ON t1.rank_id = t2.rank_id;

在这里插入图片描述

1294 不同国家的天气类型

SELECT country_name, CASE WHEN AVG(weather_state) <= 15 THEN 'Cold'
                            WHEN AVG(weather_state) >= 25 THEN 'Hot'    
                            ELSE 'Warm' END weather_type
FROM Weather t1
JOIN Countries t2 ON t1.country_id = t2.country_id
WHERE DATE_FORMAT(day,'%Y%m') = '201911'
GROUP BY country_name;

1303 求团队人数

子查询

SELECT employee_id,team_size
FROM Employee t1
JOIN (
    SELECT team_id,COUNT(team_id) team_size
    FROM Employee
    GROUP BY team_id
) t2 ON t1.team_id = t2.team_id;

窗口函数

SELECT employee_id,COUNT(*) OVER(PARTITION BY team_id) team_size
FROM Employee;

1308 不同性别每日分数总计

方案一表连接

SELECT t1.gender, t1.`day`,SUM(t2.score_points) total 
FROM Scores t1
JOIN Scores t2 ON t1.gender = t2.gender AND t1.day >= t2.day
GROUP BY t1.gender,t1.`day`
ORDER BY t1.gender ASC,t1.`day` ASC;

在这里插入图片描述
方案二窗口函数（不太理解）

在窗口的每条记录动态应用聚合函数SUM，可以动态计算在指定的窗口内的累计分数

# Write your MySQL query statement below
SELECT 
    gender, `day`, 
    SUM(score_points) OVER (PARTITION BY gender ORDER BY `day`) total
FROM scores;

1321 餐馆营业额变化增长

为什么要在SELECT中加入DISTINCT？

就所给示例来说，2019-01-10这一天有两个顾客，在进行表连接时，这两条数据连接的最近7天的内容是一样的，外加另一条2019-01-10那条数据，所以在展示时，只要展示一个就可以

为什么HAVING COUNT中加入DISTINCT？

就所给示例来说，2019-01-10这一天有两个顾客，再计算它所连接的另一个表中的记录的时候，会算上另一个2019-01-10那个条数据，COUNT的结果是8，但是，我们想要得到的是最近七天，也就是把同一天的看成一个整体，所以，需要去重

SELECT DISTINCT t1.visited_on, SUM(t2.amount) amount,ROUND(SUM(t2.amount)/7,2)average_amount
FROM Customer t1
JOIN Customer t2
ON DATEDIFF(t1.visited_on,t2.visited_on)<=6 AND DATEDIFF(t1.visited_on,t2.visited_on)>=0
GROUP BY t1.visited_on,t1.customer_id
HAVING COUNT(DISTINCT t2.visited_on) = 7
ORDER BY t1.visited_on;