leetcode刷题MySQL 3.6

1.查找电影院所有连续可用的座位。
返回按 seat_id 升序排序 的结果表。
测试用例的生成使得两个以上的座位连续可用。
结果表格式如下所示。

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
);

ps:with as 用法？ with 临时表名字 as(临时表的样子) select最终的样子

2.请你重构 Products 表，查询每个产品在不同商店的价格，使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售，则不输出这一行。
输出结果表中的 顺序不作要求 。查询输出格式请参考下面示例。

SELECT product_id, 'store1' store, store1 price FROM products WHERE store1 IS NOT NULL
UNION
SELECT product_id, 'store2' store, store2 price FROM products WHERE store2 IS NOT NULL
UNION
SELECT product_id, 'store3' store, store3 price FROM products WHERE store3 IS NOT NULL;

ps:行转列，我加单引号’store1’的意思是,我把store1这个字符串加到store（新建的列）里面去，不加单引号就是store1原来这一列的值变成price

3.找到 Point 表中任意两点之间的最短距离。
返回结果格式如下例所示。

select abs(x - lag(x, 1) over (order by x)) as shortest
from Point
order by shortest
limit 1 offset 1

ps:abs求绝对值，offset在这里的含义是去掉null，因为所有的limit1都是null，offset跳过

4编写解决方案，找到所有 丢失信息 的雇员 id。当满足下面一个条件时，就被认为是雇员的信息丢失：
雇员的 姓名 丢失了，或者
雇员的 薪水信息 丢失了
返回这些雇员的 id employee_id ， 从小到大排序 。

select a.employee_id
from (select e.employee_id from Employees e union select s.employee_id from Salaries s) as a
         left join Employees e on e.employee_id = a.employee_id
         left join Salaries s on s.employee_id = a.employee_id
where name is null
   or salary is null
order by a.employee_id

ps:null 判断是使用 is null 而不是 = null

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

select distinct page_id recommended_page
from Likes l
where
      page_id not in (select page_id from Likes l where user_id = 1)
  and user_id in (select user2_id user_id
                  from Friendship f
                  where user1_id = 1
                  union
                  select user1_id user_id
                  from Friendship f
                  where user2_id = 1)

ps：page_id not in (select page_id from Likes l where user_id = 1)这个最重要就是我就不要page = 88 的不管他在id等于1那里还是id等于6那里

6.树中的每个节点可以是以下三种类型之一：
“Leaf”：节点是叶子节点。
“Root”：节点是树的根节点。
“lnner”：节点既不是叶子节点也不是根节点。
编写一个解决方案来报告树中每个节点的类型。
以 任意顺序 返回结果表。
结果格式如下所示。

select id,
       case
           when p_id is null then 'Root'
           when id in (select distinct p_id from tree) then 'Inner'
           else 'Leaf'
           end as type
from Tree

ps：p_id显示的是每个id父亲的id，存在就是有父亲，null它本身就是父亲

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

select player_id,event_date,
sum(games_played)over(partition by player_id order by event_date) 
 games_played_so_far 
from Activity  

ps：可算让我抓到一个这么标准的窗口函数了

8.编写解决方案，找出每一个球员赢得大满贯比赛的次数。结果不包含没有赢得比赛的球员的ID 。
结果集 无顺序要求 。
结果的格式，如下所示。

SELECT p.player_id, p.player_name, SUM(p.player_id = c.Wimbledon) + SUM(p.player_id = c.Fr_open) + SUM(p.player_id = c.US_open) + SUM(p.player_id = c.Au_open) grand_slams_count 
FROM Championships c, Players p
GROUP BY p.player_id
HAVING grand_slams_count > 0

法二：行转列

# Write your MySQL query statement below
select 
    p.player_id,p.player_name,count(*) as grand_slams_count 
from
    players as p inner join 
    (select wimbledon from championships
    union all
    select fr_open from championships
    union all 
    select us_open from championships
    union all
    select au_open from championships) as t
on 
    p.player_id = t.wimbledon
group by 
    p.player_id

ps：行专列 union all转换