leedcode sql总结1

176.第二高的薪水，没有返回null

ifnull(x，y)，若x不为空则返回x，否则返回y，这道题y=null
limit x，y，找到对应的记录就停止
distinct，过滤关键字

select 
ifnull
(
    (select distinct(salary )
    from employee
    order by salary desc
    limit 1,1),
    null
)   as  SecondHighestSalary

180.连续出现的数字

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

select distinct(t1.num) as ConsecutiveNums
from logs as t1 
inner join logs as t2 on t1.id+1=t2.id and t1.num=t2.num
inner join logs as t3 on t2.id+1=t3.id and t2.num=t3.num

开窗函数

SELECT DISTINCT Num as ConsecutiveNums
FROM(
    SELECT Id,Num,
    LAG(Num,1)OVER(ORDER BY Id) as last_1,
    LAG(Num,2)OVER(ORDER BY Id) as last_2
    FROM Logs
) as consecutivenum
WHERE consecutivenum.Num = consecutivenum.last_1
AND consecutivenum.last_1 = consecutivenum.last_2

196 删除重复的电子邮箱

编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

delete p1
from person p1 join person p2
on p1.email=p2.email and p1.id>p2.id

197. 上升的温度

日期函数
给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。

select w2.id 
from weather w1 ,weather w2
where w2.Temperature>w1.Temperature and   datediff(w2. RecordDate,w1. RecordDate)=1

262. 行程和用户

写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)

select Request_at as Day ,
cast(sum(case when  Status='completed' then 0 else 1 end)/count(Status) as decimal(10,2)) as 'Cancellation Rate'
from
(
    select * from Trips
    where Client_Id not in (select Users_Id from Users
    where banned='Yes') and Driver_Id not in (select Users_Id from Users
    where banned='Yes') and '2013-10-01'<=Request_at and Request_at<='2013-10-03'
) tmp
group by tmp.Request_at

596. 超过5名学生的课

有一个courses 表 ，有: student (学生) 和 class (课程)。

请列出所有超过或等于5名学生的课。可能有学生重修有多条记录（学号不唯一）

select class
from (select distinct * from courses) tmp
group by class
having count(student)>=5

SELECT class
FROM courses
Group By class
HAVING COUNT(DISTINCT(student)) >= 5;

601. 体育馆的人流量（lag,lead/自连接）

窗口函数，计算前面两天后面两天

select id ,visit_date,people
from (
    select *,
    lag(people,1) over(order by id) as l1,
    lag(people,2) over(order by id) as l2,
    lead(people,1) over(order by id) as l3,
    lead(people,2) over(order by id) as l4
    from stadium
) tmp
where (people>=100 and l1>=100 and l2>=100) or (people>=100 and l3>=100 and l4>=100) or (people>=100 and l1>=100 and l3>=100)

自连接
第一步：查询人流量超过 100 的记录，然后将结果与其自身的临时表连接。

select distinct t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
	  (t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1)  -- t1, t2, t3
    or
    (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
    or
    (t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
)
order by t1.id

626. 换座位(case when/ if)

调整id
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

如果学生人数是奇数，则不需要改变最后一个同学的座位。

SELECT (CASE 
            WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
            WHEN MOD(id,2) = 1 THEN id+1
            ElSE id-1
        END) AS id, student
FROM seat
ORDER BY id;

select 
    if(id%2=0,
        id-1,
        if(id=(select count(distinct id) from seat),
            id,
            id+1)) 
    as id,student 
from seat 
order by id;

627. 交换工资（if/case when）

交换男女性别

update  salary
set sex=if(sex='m','f','m')

UPDATE salary 

SET 
   sex = CASE sex 
        WHEN "m" THEN "f" 
        ELSE "m" 
    END;

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

max是配合group by使用，用任意一个聚合函数都可以
（1）SUM() 是为了能够使用GROUP BY根据userid进行分组，因为每一个userid对应的subject="语文"的记录只有一条，所以SUM() 的值就等于对应那一条记录的score的值。

假如userid =‘001’ and subject=‘语文’ 的记录有两条，则此时SUM() 的值将会是这两条记录的和，同理，使用Max()的值将会是这两条记录里面值最大的一个。但是正常情况下，一个user对应一个subject只有一个分数，因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函数都可以达到行转列的效果。

（2）IF(subject=‘语文’,score,0) 作为条件，即对所有subject='语文’的记录的score字段进行SUM()、MAX()、MIN()、AVG()操作，如果score没有值则默认为0。

select 
    `id`,
    max(if(`month` = 'Jan', revenue, null)) as "Jan_Revenue",
    max(if(`month` = 'Feb', revenue, null)) as "Feb_Revenue",
    max(if(`month` = 'Mar', revenue, null)) as "Mar_Revenue",
    max(if(`month` = 'Apr', revenue, null)) as "Apr_Revenue",
    max(if(`month` = 'May', revenue, null)) as "May_Revenue",
    max(if(`month` = 'Jun', revenue, null)) as "Jun_Revenue",
    max(if(`month` = 'Jul', revenue, null)) as "Jul_Revenue",
    max(if(`month` = 'Aug', revenue, null)) as "Aug_Revenue",
    max(if(`month` = 'Sep', revenue, null)) as "Sep_Revenue",
    max(if(`month` = 'Oct', revenue, null)) as "Oct_Revenue",
    max(if(`month` = 'Nov', revenue, null)) as "Nov_Revenue",
    max(if(`month` = 'Dec', revenue, null)) as "Dec_Revenue"
from
    Department
group by `id`;

534. 游戏玩法分析 III（窗口函数）

窗口函数

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

550. 游戏玩法分析 IV

编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的比率，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

select 
cast(count(distinct a2.player_id)/(select count(distinct player_id) from activity )  as decimal(10,2)) as fraction
from activity a1  join 
(select player_id,min(event_date) as md from activity  group by player_id)  as a2
on a1.player_id=a2.player_id and datediff(a1.event_date,a2.md)=1