力扣 Mysql刷题+Mysql必知必会读后感

力扣 Mysql刷题+Mysql必知必会读后感

力扣 Mysql刷题DAY1+Mysql必知必会读后感
力扣:175-180
Mysql必知必会:1-6章

select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId
;

select salary as ‘SecondHighestSalary’ from Employee
order by salary desc
limit 1,1;

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT salary
FROM
(SELECT
salary, dense_rank() over(ORDER BY salary DESC) AS rnk
FROM
employee) tmp
WHERE rnk = N
);
#不可以增加partition by!!!

select score,dense_rank() over(order by score desc) as ‘rank’ from scores

力扣 Mysql刷题DAY2+Mysql必知必会读后感
力扣:
Mysql必知必会:7-11章
181

select a.name as employee from Employee as a,employee as b
where a.managerid=b.id and a.salary>b.salary
注意id相等在比较大小！顺序很重要结果会不同！
184

select b.name as ‘department’
,a.name as ‘emlpoyee’
,salary from employee as a
inner join department as b
on a.departmentid=b.id
where salary in
(select max(salary) from employee group by DepartmentId)

185

select d.name AS Department,a.name AS Employee,a.Salary
from
(
select *,dense_rank() over(partition by departmentid order by salary desc) as t from Employee
) as a,department as d
WHERE a.departmentid = d.id and t<=3
窗口函数:row_number:123456
dense rank12335 rank:12334

力扣 Mysql刷题D3+Mysql必知必会读后感

Mysql必知必会读后感11-14章

511.游戏玩法分析i
512.ii
524.iii
550.iv