力扣SQL刷题8

1212. 查询球队积分

题型：读题
解答：先考虑做主方时，再考虑客方时

SELECT t.team_id, t.team_name, IFNULL(score,0) num_points
FROM
(
    SELECT team_id, SUM(score) score
    FROM (
        SELECT host_team team_id, 
        SUM(CASE
        WHEN host_goals>guest_goals THEN 3
        WHEN host_goals<guest_goals THEN 0
        ELSE 1
        END) score
        FROM matches
        GROUP BY host_team
        UNION ALL
        SELECT guest_team team_id, 
        SUM(CASE
        WHEN host_goals>guest_goals THEN 0
        WHEN host_goals<guest_goals THEN 3
        ELSE 1
        END) score
        FROM matches
        GROUP BY guest_team
    ) b
    GROUP BY team_id
) a
RIGHT JOIN teams t ON t.team_id=a.team_id
ORDER BY num_points DESC, t.team_id

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

题型：看题，嵌套
解题：幸好题目限定了不超过三次，三次自连接就可以了

select e1.employee_id 
from Employees e1 , Employees e2 ,Employees e3 
where e1.manager_id = e2.employee_id and  e2.manager_id = e3.employee_id
and e3.manager_id =1 and e1.employee_id !=1

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

题型：看题目
解答：和求连续N天思路差不多。
先排序 — 日期减去排序序号 — 对减出来的差做分组 — 最大值和最小值即我们所求

select min(log_id) start_id,max(log_id) end_id
from
(select log_id ,rank()over(order by log_id asc) as rk
from Logs) a
group by (log_id-rk)

注意点：select 。。。from （select。。。） a
一定要取一个别名a，不然会报错

1321. 餐馆营业额变化增长–重要

题型：读题
解答：1，先按照每天的累计好，2，再计算7天累计的

SELECT DISTINCT visited_on,
       sum_amount AS amount, 
       ROUND(sum_amount/7, 2) AS average_amount
-- 需要先求出和后除以7，且要加最后的where条件，所以又嵌套了一次
FROM (
    SELECT visited_on, 
       SUM(amount) OVER ( ORDER BY visited_on ROWS 6 PRECEDING ) AS sum_amount
    -- 以下是先计算出每天的金额总量
    FROM (
        SELECT visited_on, 
            SUM(amount) AS amount
        FROM Customer
        GROUP BY visited_on
         ) TT
     ) LL
-- 最后手动只要覆盖完整7天的数据
WHERE DATEDIFF(visited_on, (SELECT MIN(visited_on) FROM Customer)) >= 6
#输出结果的日期要离最早的那天，至少隔了7天
#DATEDIFF(visited_on, MIN(visited_on) ) >= 6 注意这样不行

窗口函数知识点补充

窗口函数写法：

[你要的操作] OVER ( PARTITION BY  <用于分组的列名>
                    ORDER BY <按序叠加的列名> 
                    ROWS <窗口滑动的数据范围> )

滑动数据范围：

当前行 - current row
之前的行 - preceding
之后的行 - following
无界限 - unbounded
表示从前面的起点 - unbounded preceding
表示到后面的终点 - unbounded following

举例：

取当前行和前五行：ROWS between 5 preceding and current row --共6行
取当前行和后五行：ROWS between current row and 5 following --共6行
取前五行和后五行：ROWS between 5 preceding and 5 folowing --共11行