3.📅

[177. 第N高的薪水]

考察内容：创建函数！

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    declare m INT;
    set m=N-1; 
  RETURN (
      # Write your MySQL query statement below.
      select ifnull((select distinct Salary from Employee as E order by Salary desc limit m,1),null)
  );
END
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set n = N-1;
  RETURN (
      # Write your MySQL query statement below.
      select ifnull(
      (
      select distinct Salary getNthHighestSalary
      from Employee
      order by Salary desc
      limit n, 1
      ), null)
  );
END
#top Answer
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
   SET N = N-1;
  RETURN (
      # Write your MySQL query statement below.
       SELECT 
            salary
      FROM 
            employee
      GROUP BY 
            salary
      ORDER BY 
            salary DESC
      LIMIT N, 1
  );
END

[178. 分数排名]

如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
知识点考察：窗口函数 over？

# Write your MySQL query statement below
select Score, dense_rank() over(order by Score desc) as 'Rank' 
from Scores
order by Score desc;
#可以理解
select distinct a.Num as ConsecutiveNums
from Logs as a,Logs as b,Logs as c
where a.Num=b.Num and b.Num=c.Num and a.id=b.id-1 and b.id=c.id-1;

3.31

[181 连续出现的数字] ```sql
Write your MySQL query statement below
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

top answer

SELECT DISTINCT l1.Num AS ConsecutiveNums FROM Logs AS l1 INNER JOIN Logs AS l2 ON l1.Id + 1 = l2.Id AND l1.Num = l2.Num INNER JOIN Logs AS l3 ON l1.Id + 2 = l3.Id AND l1.Num = l3.Num


- [184 部门工资最高的员工]
解题思路：group by + in!!!
```sql
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 SELECT
    d.name AS 'Department',
    e.name AS 'Employee',
    Salary
FROM (select *,rank_over()(partition by departmentid ,order by salary desc
                           )as 'rank' from employee) e left join 
                           department d
                           on e.departmentid = d.id and e.rank = 1
# 窗口函数——答案
select t2.Name Department, t1.Name Employee, t1.Salary Salary from 
(select *, rank() over(partition by DepartmentId order by Salary desc) as 'rk' from Employee) t1, Department t2 
where t1.rk = 1 and t1.DepartmentId = t2.Id;

534 游戏玩法三

解题思路：窗口函数 sum()over(partition by order by)

# Write your MySQL query statement below
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 游戏玩法四

解题思路：计算两个日期距离的天数 datediff(a,b) = 1——a是晚，b是早


SELECT
    ROUND(COUNT(DISTINCT a.player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM
    Activity AS a
    INNER JOIN (
        SELECT
            player_id, MIN(event_date) AS first_login
        FROM
            Activity
        GROUP BY 
            player_id
    ) AS b
    ON a.player_id = b.player_id AND DATEDIFF(a.event_date, b.first_login) = 1;
 # 使用round函数
  select round((
    select
        count(a1.player_id) cnt 
    from 
        Activity a1,
        (
            select 
                player_id,
                min(event_date) event_date
            from Activity
            group by player_id
        ) a2
    where 
        a1.player_id = a2.player_id and
        datediff(a1.event_date, a2.event_date) = 1 
) / count(distinct a.player_id), 2) fraction
from Activity a
select 
round(
    (select count(*) from Activity a 
    inner join 
    (select player_id,min(event_date) first_date from Activity group by player_id) as b
    on a.player_id=b.player_id
    where datediff(a.event_date,b.first_date)=1)
    /
    (select count(distinct player_id) from Activity),
    2) 
as fraction

570 至少有5名直接下属

解题思路：group by ;having count() ;in

select name  from employee 
where id in 
(select managerid  from employee group by managerid having count(managerid) >=5 )
# top Answer ——添加别名可以提升检索速度？
SELECT e1.Name
FROM employee e1
WHERE e1.Id IN(
    SELECT managerid 
    FROM employee 
    GROUP BY managerid 
    HAVING COUNT(ManagerId) >= 5)
# 使用内连接 inner join
select e1.name as Name from Employee as e1 
left join Employee as e2 
on e1.Id = e2. ManagerId 
group by e1.Id having count(e2.ManagerId) >= 5

574 当选者

解题思路：（不存在并列）使用group by+order by desc limit1

select name from candidate
where id =    #注意该处不可以使用 in
(select candidateid from vote group by candidateid  
 # group by 不可以抛弃
order by count(candidateid) desc limit 1)
# top Answer
select name
from vote
join candidate on vote.candidateid = candidate.id
group by candidateid
order by count(candidateid) desc
limit 1

578 查询回答率最高的问题

tip:组内频率

# Write your MySQL query statement below
select question_id as survey_log from survey_log
group by question_id 
order by count(answer_id) desc limit 1
# top Answer
# Write your MySQL query statement below
select question_id survey_log
from survey_log
group by question_id
order by avg(answer_id is not null) desc
limit 1

580 统计各专业学生数量

解题思路：group by 执行顺序优先于 select + left join

# Write your MySQL query statement below
select dept_name ,count(student_id) as student_number from department d left join student s
on d.dept_id = s.dept_id
group by d.dept_id 
order by count(student_id) desc
# top Answer

626 交换座位

解题思路：交换数据
判断奇偶数 mod(id,2) = 1 奇数 = 0 为偶数
使用方法： case when then;if ;异或 1^1 = 0,0^1 = 1

# Write your MySQL query statement below
select 
        (case 
        when mod(id,2) = 1 and id != (select max(id)from seat)then id+1 
     # 该位置只能使用 select语句 不可以使用count（distinct ID） or max(id)
        when mod(id,2) = 0 then id-1
      else  id end ) as id ,student 
from seat 
order by id asc
# Write your MySQL query statement below
select
    (case
        when mod(id, 2) = 1 and id != (select max(id) from seat) then id + 1
        when mod(id, 2) = 0 then id - 1
    else id end) as id,
    student
from seat
order by id asc
作者：jun-mo-xiao-21
链接：https://leetcode-cn.com/problems/exchange-seats/solution/cha-xun-zi-ju-zhong-zhi-jie-shi-yong-case-whenlai-/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

1270 向公司CEO回报工作的所有员工

解题思路：总共三层，选出经理的层级就OK了
主要还是学会递归


#top Answer
# Write your MySQL query statement below
select e1.employee_id
from Employees e1 left join Employees e2
on e1.manager_id = e2.employee_id
left join Employees e3
on e2.manager_id = e3.employee_id
where e3.manager_id = 1 and e1.employee_id != 1

1501 可以放心投资的国家

解题思路：将phone_number 前缀提取-left join对应国家
到calls 表中联结两次，算时间！- 求均值
难点：提取phone_number 前缀：
country_code = LEFT(phone_number, 3)

# 我的答案——借鉴 一位老哥的思路 ——耗时1052 ms
select c.name Country from country c 
right join (select id,left(phone_number,3) as c_d from person) tmp 
on c.country_code = tmp.c_d
right join calls ca 
on (ca.caller_id = tmp.id or ca.callee_id = tmp.id) 
#join条件语句可以使用or！
group by c.country_code
having avg(duration) > (select avg(duration) from calls)
#top Answer ——耗时 731ms
# Write your MySQL query statement below
SELECT c.name AS country
FROM Calls, Person, Country c
WHERE (caller_id = id OR callee_id = id) AND country_code = LEFT(phone_number, 3)
GROUP BY country_code
HAVING AVG(duration) > (SELECT AVG(duration) FROM Calls);

1098 小众书籍

解题思路 DATEDIFF(‘条件日期’，’变化日期’) 判定条件
注意：售卖1年的条件放在 left join on后面
如果放在where后面，1年中部分一本没卖的书籍直接被过滤了
注意left join和普通join 在on的后面跟条件的区别 left on+条件，只改变右边的值，无法改变左表的值，左表值仍然保留，没有where 的直接过滤作用 inner join 后的on + 条件，可以直接过滤条件等同于where的作用

1、join on 和 where 选择不同，on是连接时，where是连接后筛选 2、dispatch_date条件位置的放置很关键，它必须放在on后面，如果where后面，则会将没有购买的信息过滤掉 3、如果将available_from放到on后面，则对left join 无影响，无法按条件去除信息

select b.book_id,name from books b
left join orders o on b.book_id = o.book_id 
and DATEDIFF('2019-06-23',dispatch_date) <=365
where DATEDIFF('2019-05-23',available_from) > 30
group by b.book_id
having ifnull(sum(quantity),0) < 10

MysqL 学习

1—Medium

3.📅

3.31

Write your MySQL query statement below

top answer