数据库练习

leetcoder免费题,一皆copy于领扣or菜鸟教程

175. 组合两个表

因为表 Address 中的 personId 是表 Person 的外关键字，所以我们可以连接这两个表来获取一个人的地址信息。

考虑到可能不是每个人都有地址信息，我们应该使用 outer join 而不是默认的 inner join。

1）左联结（left join），联结结果保留左表的全部数据

2）右联结（right join），联结结果保留右表的全部数据

3）内联结（inner join），取两表的公共数据

如果有的学生没有选修课程，那么他就不会出现在“成绩”表里，所以“成绩”表没有包含“所有学生”。

所以要以“学生”表进行左联结，保留左边表（学生表）里的全部数据。

SELECT FirstName, LastName, City, State
FROM Person left join Address
ON Person.PersonId = Address.PersonId;

176. 第二高的薪水

https://www.runoob.com/mysql/mysql-func-ifnull.html

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

IFNULL() 函数语法格式为：IFNULL(expression, alt_value)

limit N # 返回 N 条记录
offset M # 跳过 M 条记录，M 默认为 0
limit M,N # 相当于 limit N offset M，从第 M 条记录开始，返回 N 条记录

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

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

177. 第N高的薪水

同176

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N = N - 1;
    RETURN (
-- SELECT IFNULL(
            (SELECT DISTINCT Salary
            FROM Employee
            ORDER BY Salary DESC
            LIMIT N, 1)
-- , NULL)
    );
END

178. 分数排名

https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649247566&idx=1&sn=f9c7018c299498673b38221db2ecd5cd&chksm=835fc77eb4284e68b7528fd7f75eedb8868a6740704af8559f8a5cbdd2867a49ffa21bf4e531&token=426730634&lang=zh_CN#rd

我的 623ms

SELECT a.Score AS Score,
    (SELECT COUNT(DISTINCT b.Score)+1
    FROM Scores b WHERE b.Score > a.Score
    ) AS Rank
FROM Scores a ORDER BY Score DESC;

非常快的 176ms

思路是这样的，因为这题要求的是同一个分数排名相同，所以先要计算出不同分数各自的排名，然后将成绩表与这个排名表连接就可以知道每个成绩对应的排名了。
计算不同分数的排名的话就是先select distinct选出不同的分数，然后自连接，通过聚合函数算出有多少大于等于自己的分数，就可以计算出一个排名表了。
然后考虑到会有相同的分数，所以我们拿原表也就是scores表和上面求出的表join一下，就可以的到答案了。
作者：duo-15
链接：https://leetcode-cn.com/problems/rank-scores/solution/joinhe-zi-lian-jie-by-duo-15/
来源：力扣（LeetCode）
select Scores.Score, tmp.Rank
from
Scores
left join
(
    select t.Score, count(t.Score)as Rank
    from (
        select a.Score as Score
        from
        (select distinct Score from Scores)as a,
        (select distinct Score from Scores)as b
        where
        a.Score <= b.Score
    )as t
    group by t.Score
)as tmp
on Scores.Score = tmp.Score
order by tmp.Rank asc;

180. 连续出现的数字

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

select distinct a.Num as ConsecutiveNums
from Logs as a, Logs as b, Logs as c
where a.Id = b.Id - 1 and b.Id = c.Id - 1
and a.Num = b.Num and b.Num = c.Num

181. 超过经理收入的员工

SELECT a.name AS Employee
FROM Employee a, Employee b
WHERE a.ManagerId IS NOT NULL
AND a.ManagerId = b.Id AND a.Salary > b.Salary;

182. 查找重复的电子邮箱

优先顺序: where>group by>having>order by

select Email
from (
    select Email, count(Email) as cnt
    from Person
    Group by Email
) as statistic 
where cnt > 1;

select Email
from Person
Group by Email
having count(Email) > 1;

183. 从不订购的客户

select Name as Customers 
from Customers 
where Id not in 
(
    select CustomerId from Orders
);

184. 部门工资最高的员工

select Department.Name as Department, Employee.Name as Employee, Employee.Salary as Salary
from Department, Employee,
(
    select distinct DepartmentId, max(Salary) as Salary
    from Employee
    group by DepartmentId
) as maxtop
where maxtop.DepartmentId = Employee.DepartmentId
and Employee.DepartmentId = Department.id
and Employee.Salary = maxtop.Salary;

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

我们先找出公司里前 3 高的薪水，意思是不超过三个值比这些值大

select Department.Name as Department, a.Name as Employee, a.Salary as Salary
from Employee a, Department
where (a.DepartmentId = Department.Id) 
and 3 > (
        select count(distinct Salary)
        from Employee b
        where b.Salary > a.Salary && b.DepartmentId = a.DepartmentId
    ) order by Department.Id, Salary desc;

196. 删除重复的电子邮箱

我们要建立 临时表 不然

You can’t specify target table ‘Person’ for update in FROM clause

Delete from Person 
where id in(
    select Id from
    (
        select p1.id as Id
        from Person p1, Person p2
        where p1.Email = p2.Email and p1.Id > p2.Id
    ) as tmp
);
题解
DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

197. 上升的温度

DATEDIFF(weather.date, w.date) = 1中，weather.date是起始日期，w.date是截止日期

这都什么冷知识啊 百度一下不就好了

select w1.Id
from Weather w1, Weather w2
where (
    DATEDIFF(w1.recordDate, w2.recordDate) = 1
    and
    w1.Temperature > w2.Temperature
);

262. 行程和用户

我肯定补这题,今天做不动了 (2020.3.9 : 17点36分)

如果你看到 我10几天都没有做,当我死了吧

595. 大的国家

select name, population, area
from World
where area >= 3000000 or population >= 25000000

596. 超过5名学生的课

select tmp.class
from (
    select count(distinct student) as cnt, class
    from courses
    group by class
) as tmp
where cnt > 4;

601. 体育馆的人流量

我无力吐槽 看不懂题解这数据上个1000千不把服务器炸了

620. 有趣的电影

select id, movie, description, rating
from cinema
where mod(id, 2) = 1 and description != 'boring'
order by rating desc;

626. 换座位

case 语法

case sex
    when '1' then '男'
    when '2' then '女'
    else '其他' end
    
select (case
when mod(id, 2) = 0 then id - 1
when id = (select max(id) from seat) then id else id + 1
end)as id, student
from seat order by id asc;

627. 交换工资

update salary
set sex = case 
when sex = 'm' then 'f' else 'm'
end

case
when mod(id, 2) = 0 then id - 1
when id = (select max(id) from seat) then id else id + 1
end)as id, student
from seat order by id asc;

627. 交换工资

update salary
set sex = case 
when sex = 'm' then 'f' else 'm'
end