力扣关于数据库的题目(12道)

#连接后这两个就是一个表了
select firstName, lastName, city, state
from Person left join Address
on Person.PersonId = Address.PersonId;

select p.firstName, p.lastName, a.city, a.state
from Person as p left join Address as a
on p.PersonId = a.PersonId;

 

 

 

 

#5.5
select  ifnull((select distinct salary 
        from Employee
        order by salary desc
        limit 1 offset 1), null) as SecondHighestSalary;

 

 

#编写一个函数


CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
        select distinct salary 
        #临时表必须命名，窗口函数的使用dense_rank()
        from (select salary, dense_rank() over (order by salary desc) as a from employee) as temp
        where a = N       
  );
END

 

# Write your MySQL query statement below
#命名的时候使用反引号，它是为了区分MYSQL的保留字（就和java中的关键字一样）与普通字符而引入的符号
#加上partition by是指定分组，比如各种学科的时候，这个不需要指定分组啊
select score, dense_rank() over (order by score desc) as `rank`
from Scores;

 

# Write your MySQL query statement below
#在业务层进行筛选
#非要用数据库的话就查3个表
select *
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;

 

# Write your MySQL query statement below
# SELECT
#      a.NAME AS Employee
# FROM Employee AS a JOIN Employee AS b
#      ON a.ManagerId = b.Id
#      AND a.Salary > b.Salary
# ;
#一个表中的查询，换个名字又是一样的新表，用自连接
#左连接返回左边全部，已经两者都有的部分
select a.name as `Employee`
from Employee as a join Employee as b on a.managerId = b.id and a.salary > b.salary;

# Write your MySQL query statement below
#先查找电子邮箱出现的次数, 在查询大于一的个数，这是临时表的方式
select Email
from (select Email, count(Email) as `num`
    from Person
    group by Email) as temp
where num > 1;

#优先顺序: where>group by>having>order by
select Email
from Person
group by Email
having count(Email) > 1;

 

 

# Write your MySQL query statement below
#连接查询五种情况的第一种
select c.Name as Customers 
from Customers as c left join Orders as o on c.Id = o.CustomerId
where o.Id is null

#子查询的方式
select customers.name as 'Customers'
from customers
where customers.id not in
(
    select customerid from orders
);

 

 

# Write your MySQL query statement below

#部门工资最高的员工，不同分组中最高或着最低的问题
#真要是写起来细节很多，要记住前面带上哪个表在.
select temp.Department, temp.Employee, temp.Salary
from (select d.name as `Department`, e.name as `Employee`, e.salary as `Salary`,
            #得按照原两个表中字段值多的一列写，e.department改为d.Department是错误的
            dense_rank() over (partition by e.departmentId order by e.Salary desc) as `dense` 
        from Employee as e join Department as d on e.departmentId = d.id ) as temp
where temp.dense = 1;

 

# Write your MySQL query statement below
select temp.Department, temp.Employee, temp.Salary
from (select d.name as `Department`, e.name as `Employee`, e.salary as `Salary`,
            #得按照原两个表中字段值多的一列写，e.department改为d.Department是错误的
            dense_rank() over (partition by e.departmentId order by e.Salary desc) as `dense` 
        from Employee as e join Department as d on e.departmentId = d.id ) as temp
where temp.dense < 4;

 

# Write your MySQL query statement below
#某种意义上，p2作为临时表了，然后p1中的数据一条一条去对比
delete p1 from Person p1, Person p2 where p1.Email = p2.Email and p1.Id > p2.Id;

 

# Write your MySQL query statement below
# SELECT
#     weather.id AS 'Id'
# FROM
#     weather
#         JOIN
#     weather w ON DATEDIFF(weather.date, w.date) = 1
#         AND weather.Temperature > w.Temperature
# ;

# SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate 结果为1； SELECT DATEDIFF('2008-12-29','2008-12-30') AS DiffDate 结果为-1。
# MySQL 使用 DATEDIFF 来比较两个日期类型的值。
select w.id
from weather as w join weather as wt on 
        datediff(w.recordDate, wt.recordDate) = 1 and w.Temperature > wt.Temperature;