leetcode--SQL练习-按照出题频率

569. 员工薪水中位数-VIP

题目
SQL中的取整函数FLOOR、ROUND、CEIL、TRUNC、SIGN
1 trunc（value,precision）按精度(precision)截取某个数字,不进行舍入操作。
2 round（value,precision）根据给定的精度(precision)输入数值。
3 ceil (value) 产生大于或等于指定值（value）的最小整数。
4 floor（value）与 ceil（）相反，产生小于或等于指定值（value）的最小整数。
5 sign(value) 与绝对值函数ABS（）相反。ABS（）给出的是值的量而不是其符号，sign(value)则给出值的符号而不是量。
详情参考：添加链接描述
题目：
Employee 表包含所有员工。Employee 表有三列：员工Id，公司名和薪水。请编写SQL查询来查找每个公司的薪水中位数。挑战点：你是否可以在不使用任何内置的SQL函数的情况下解决此问题.

# 窗口函数
# Write your MySQL query statement below
select e.Id , e.Company,e.Salary
from(
select Id , Company,Salary,
row_number() over(partition by Company order by Salary asc,Id asc) as rk ,
count(Id) over (partition by Company)as counts
from Employee) e
where e.rk >= e.counts/2 and e.rk <= e.counts/2 + 1 
-- e.rk in (floor((e.counts+1)/2),floor((e.counts+2)/2))

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

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。
题目
题解

-- 公司里前 3 高的薪水意味着有不超过 3 个工资比这些值大。
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 e1.DepartmentId=e2.DepartmentId
     and e2.Salary>e1.Salary
)

# 窗口函数
# Write your MySQL query statement below
select tmp.Department,tmp.Employee,tmp.Salary
from (
select d.Name as Department,e.Name as Employee,e.Salary,
dense_rank() over (partition by e.DepartmentId  order by e.Salary desc) as rk 
from Employee e,Department d 
where e. DepartmentId=d.Id) tmp
where tmp.rk<=3

# 窗口函数
# Write your MySQL query statement below
select d.Name as Department ,e.Name as Employee,e.Salary
from(
    select *,dense_rank() over (partition by  DepartmentId order by Salary desc) as rk 
    from Employee
)e
join Department d 
on e. DepartmentId=d.Id 
where e.rk<=3

1205. 每月交易II-VIP

编写一个 SQL 查询，以查找每个月和每个国家/地区的已批准交易的数量及其总金额、退单的数量及其总金额。
注意：在您的查询中，给定月份和国家，忽略所有为零的行。

# Write your MySQL query statement below
SELECT month ,country ,
       SUM(IF(type = 'approved', 1, 0)) AS approved_count,#注意都是sum
       SUM(IF(type = 'approved', amount, 0)) AS approved_amount ,
       SUM(IF(type = 'chargebacks', 1, 0)) AS chargeback_count,
       SUM(IF(type = 'chargebacks', amount, 0)) AS chargeback_amount
from(
    (select substr(trans_date,1,7) as month,country,amount,'approved' as type
    from Transactions
    where state ='approved')
    union ALL
    (# substr(c.trans_date,1,7)as month 注意是c的时间！
        select  substr(c.trans_date,1,7)as month,country,amount,'Chargebacks ' as type
    from Transactions t,Chargebacks  c 
    where t.id =c.trans_id))tmp
group by tmp.month,tmp.country
HAVING 
  approved_count + chargeback_count > 0

题解：

1. 不同国家在不同月里接受的
   批准退款数目和总额（也就是 Transactions 里面的 approved）
   已经退款数目和总额（也就是 Chargebacks 与 Transactions 连接后的信息）
   因此我们需要用 UNION ALL
2. date_format(c.trans_date, ‘%Y-%m’)

1454. 活跃用户-VIP

题目
写一个 SQL 查询, 找到活跃用户的 id 和 name.活跃用户是指那些至少连续 5 天登录账户的用户.返回的结果表按照 id 排序.

# Write your MySQL query statement below
select *
from Accounts
where id in (
    select distinct l1.id  #注意distinct
    from logins l1
    join logins l2 
    on l1.id=l2.id 
    and datediff(l2.login_date,l1.login_date) between 0 and 4 
    group by l1.id ,l1.login_date 
    having count(distinct l2.login_date)=5 # 注意是distinct 
)
order by id 

601.体育馆的人流量

添加链接描述
X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)。
请编写一个查询语句，找出人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100。

select distinct s. id ,s.visit_date,s. people  from stadium s  # 注意distinct
join (
# 选出连续3天及以上大于100的日期
    select s1.id  
    from stadium s1
    , stadium s2 
    where s2.id-s1.id between 0 and 2 
    group by s1.id 
    having sum(if(s2.people>=100,1,0))>=3
)tmp 
on s.id-tmp.id between 0 and 2
order by s.id 

--连续三天都大约100，注意distinct
SELECT distinct a.*
FROM stadium as a,stadium as b,stadium as c
where (a.people>=100 and b.people>=100 and c.people>=100)
and (
    (a.id=b.id-1 and b.id=c.id-1) or
    (b.id=a.id-1 and a.id=c.id-1) or
    (b.id=c.id-1 and c.id=a.id-1)
)
order by a.id 

180.连续出现的数字

题目链接
题解

# 模板法 180，601，1454都可用类似思路 --推荐
# Write your MySQL query statement below
select distinct l1.Num as ConsecutiveNums
from Logs l1
join Logs l2 
where l1.Num=l2.Num 
and l2.id -l1.id between 0 and 2 
group by l1.Id,l1.Num 
having count(l2.Id)>=3

题解

# 窗口函数
# Write your MySQL query statement below
select distinct Num as  ConsecutiveNums
from(
    select Num,count(*) as counts
    from(
        select Id,Num,
        (row_number() over (order by id) -row_number() over (partition by Num order by id)) as orde
        from Logs 
    )w
    group by Num,orde
)h
where counts>=3

# Write your MySQL query stateme
--注意distinct
select distinct l1.Num as ConsecutiveNums
from Logs l1,Logs l2,Logs l3
where l1.Id=l2.Id-1
and l2.Id=l3.Id-1
and l1. Num =l2. Num 
and l2. Num =l3. Num 

题解
变量法实现排序窗口函数

#变量法
#①首先遍历一遍整张表，找出每个数字的连续重复次数
#具体方法为：
    #初始化两个变量，一个为pre，记录上一个数字；一个为count，记录上一个数字已经连续出现的次数。
    #然后调用if()函数，如果pre和当前行数字相同，count加1极为连续出现的次数；如果不同，意味着重新开始一个数字，count重新从1开始。
    #最后，将当前的Num数字赋值给pre，开始下一行扫描。
    select 
        Num,    #当前的Num 数字
        if(@pre=Num,@count := @count+1,@count := 1) as nums, #判断 和 计数
        @pre:=Num   #将当前Num赋值给pre
    from Logs as l ,
        (select @pre:= null,@count:=1) as pc #这里需要别名
    #上面这段代码执行结果就是一张三列为Num,count as nums,pre的表。

#②将上面表的结果中，重复次数大于等于3的数字选出，再去重即为连续至少出现三次的数字。
    select 
        distinct Num as ConsecutiveNums 
    from  
        (select Num,
                if(@pre=Num,@count := @count+1,@count := 1) as nums,
                @pre:=Num
            from Logs as l ,
                (select @pre:= null,@count:=1) as pc
        ) as n
    where nums >=3;

#注意：pre初始值最好不要赋值为一个数字，因为不确定赋值的数字是否会出现在测试表中。

175. 组合两个表-简单

题目：
编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：

# 左连接
# Write your MySQL query statement below
select p.FirstName, p.LastName, a.City, a.State
from Person p 
left join  Address a 
on p. PersonId  =a. PersonId  

184. 部门工资最高的员工

题目：
编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。
题解：添加链接描述
因为 Employee 表包含 Salary 和 DepartmentId 字段，我们可以以此在部门内查询最高工资。

SELECT
    DepartmentId, MAX(Salary)
FROM
    Employee
GROUP BY DepartmentId;

# 推荐
SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)

# 窗口函数
select d.Name Department,e.Name Employee,e.Salary
from(select *,dense_rank() over(partition by  DepartmentId order by  Salary  desc )as rk 
from Employee
) e 
join Department d 
on e.DepartmentId =d.Id 
where e.rk=1

176. 第二高的薪水-简单

题目：
编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。如果不存在第二高的薪水，那么查询应返回 null。
题解
注意：
非空的情况

select ifnull(
    (select distinct Salary  #注意distinct
    from Employee
    order by Salary desc 
    limit 1,1),null 
) as SecondHighestSalary

如果没有这样的第二最高工资，这个解决方案将被判断为 “错误答案”，因为本表可能只有一项记录。为了克服这个问题，我们可以将其作为临时表。wei

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary

题解

# 自己写的，注意distinct的使用
# Write your MySQL query statement below
select ifnull((select max(distinct Salary) 
from  Employee
where Salary <>(
    select max(Salary)
from  Employee)),null)
as SecondHighestSalary 

262. 行程和用户

题目
题解链接
题目：
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。
取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)
注意：
两次连接同一个表
‘Cancellation Rate’ 分开的字段用反引号

# Write your MySQL query statement below
select t.Request_at as  Day ,round(
    sum(if( t.Status ='completed',0,1))/count(t.Status),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 t.Request_at between '2013-10-01' and '2013-10-03'
group by t.Request_at

178 分数排名

题目
题目：
如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
重要提示：
对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 Rank
题解

select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a
order by a.Score DESC

# Write your MySQL query statement below 窗口函数
select Score,
dense_rank() over(order by Score desc) as   `Rank`    
from Scores
-- order by Score desc 不加也可以运行

1179 重新格式化部门表

题目
题目：
行列的字段互换，类似于成绩单
编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。

# Write your MySQL query statement below
select id,
max(case when month='Jan' then revenue else NULL end )'Jan_Revenue',
max(case when month='Feb' then revenue else NULL end )'Feb_Revenue',
max(case when month='Mar' then revenue else NULL end )'Mar_Revenue',
max(case when month='Apr' then revenue else NULL end )'Apr_Revenue',
max(case when month='May' then revenue else NULL end )'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 order by id

550 游戏玩法分析 IV -VIP

题目
题解
题目：
编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的分数，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。
思路：
首先我们可以把问题拆分，先求出所有玩家第一次登陆的数据，然后把这个作为临时表，然后我们把所有数据和他们第一次登陆的数据进行比较，如果日期相差1天，即为符合条件的玩家，用这部分的玩家数量除以所有玩家的数量，然后保留两位小数就可以得出结果了；

# Write your MySQL query statement below
select round(
    sum(if(datediff(a1.event_date,a2.first_time)=1,1,0))/count(distinct a2.player_id),2) as fraction 
from Activity a1,
 (select player_id,min(event_date) first_time
from Activity
group by player_id) a2 
where a1.player_id=a2.player_id

579 查询员工的累计薪水 -VIP

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

1. 请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）。
2. 结果请按 Id 升序，然后按 Month 降序显示。

select Id,Month,
sum(Salary) over(partition by Id order by Month rows between 2 preceding and current row ) Salary
from (
    select *,
    row_number() over (partition by Id order by Month desc) as rk 
    from Employee 
) tmp
where rk >1
order by Id ,Month desc

windows子句

1097 游戏玩法分析 V -VIP

1141 查询近30天活跃用户数 -VIP

题目
题目：
请写SQL查询出截至 2019-07-27（包含2019-07-27），近 30天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。
返回字段，日期，当日用户数
注意：
截至 2019-07-27（包含2019-07-27），近 30天的每日活跃用户数如何表示？
datediff(‘2019-07-27’,activity_date)<30

# Write your MySQL query statement below
select 
activity_date as `day`,
count(distinct user_id ) as active_users
from Activity
where datediff('2019-07-27',activity_date)<30
group by activity_date

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

177 第N高的薪水

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

1407 排名靠前的旅行者 -VIP

181. 超过经理收入的员工

力扣链接：添加链接描述

# Write your MySQL query statement below
--注意'Employee'字段名与表名一致时，加双引号
select a.Name  as 'Employee'
from Employee a,Employee b
where a.ManagerId=b.Id
and a.Salary>b.Salary

618 学生地理信息报告 -VIP

626.换座位

添加链接描述

--相邻的交换，最后一个单的就自身不换
select (case
when id%2!=0 and counts!=id then id+1
when id%2!=0 and counts=id then id 
else id-1 end
) id ,student
from seat ,(select count(*) counts from seat) as seat_counts 
order by id 

1179. 重新格式化部门表

力扣链接添加链接描述

--注意sum取最大值
SELECT id,
SUM(CASE `month` WHEN 'Jan' THEN revenue END) Jan_Revenue,
SUM(CASE `month` WHEN 'Feb' THEN revenue END) Feb_Revenue,
SUM(CASE `month` WHEN 'Mar' THEN revenue END) Mar_Revenue,
SUM(CASE `month` WHEN 'Apr' THEN revenue END) Apr_Revenue,
SUM(CASE `month` WHEN 'May' THEN revenue END) May_Revenue,
SUM(CASE `month` WHEN 'Jun' THEN revenue END) Jun_Revenue,
SUM(CASE `month` WHEN 'Jul' THEN revenue END) Jul_Revenue,
SUM(CASE `month` WHEN 'Aug' THEN revenue END) Aug_Revenue,
SUM(CASE `month` WHEN 'Sep' THEN revenue END) Sep_Revenue,
SUM(CASE `month` WHEN 'Oct' THEN revenue END) Oct_Revenue,
SUM(CASE `month` WHEN 'Nov' THEN revenue END) Nov_Revenue,
SUM(CASE `month` WHEN 'Dec' THEN revenue END) Dec_Revenue
FROM Department
GROUP BY id;

196. 删除重复的电子邮箱

题目
题解：添加链接描述

# 自连接
delete p1 
from
Person  p1,Person p2 
where p1.Email=p2.Email
and p1.Id>p2.Id

delete from Person 
where Id not in (
select id from(
    select min(Id) as id # 注意别名
from Person p2
group by Email) tmp  # 注意别名
)