0%

MySQL leetcode

bing

  1. 组合两个表

    1
    2
    3
    SELECT p.FirstName, p.LastName, a.City, a.State
    FROM Person AS p LEFT JOIN Address AS a
    ON p.PersonId = a.PersonId;
    • 左外连接:左表中满足条件的记录能够都查询到,即使右表中没有与之匹配的行
  2. 第二高的薪水

    1
    2
    3
    4
    5
    SELECT
    (SELECT DISTINCT(Salary)
    FROM Employee
    ORDER BY Salary desc
    LIMIT 1,1) as SecondHighestSalary;
    • 关键字顺序:SELECT -> FROM -> WHERE ->GROUP BY -> HAVING -> ORDER BY -> LIMIT
    • 使用DISTINCT 筛选重复的查询结果
    • 为保证查询结果为空时返回NULL,需要在外边再包装一层SELECT
    • LIMIT 可以进行范围搜索
  3. 第N高的薪水

    1
    2
    3
    4
    5
    6
    7
    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
    set N = N-1;
    RETURN (
    SELECT(SELECT DISTINCT(Salary) FROM Employee ORDER BY Salary DESC LIMIT N, 1)
    );
    END
    • 函数注重返回值,返回值只能是一个值,不能是集合
    • LIMIT进行范围查询
    • 包装SELECT 返回NULL
  4. 分数排名

    1
    2
    3
    4
    5
    SELECT s1.Score,COUNT(DISTINCT(s2.Score)) rank
    FROM Scores s1,Scores s2
    WHERE s1.Score <= s2.Score
    GROUP BY s1.Id -- 此处运行环境不同可能会报错
    ORDER BY s1.Score DESC;
    1
    2
    3
    4
    SELECT s1.Score,
    (SELECT COUNT(DISTINCT(s2.Score)) FROM Scores s2 WHERE s2.Score > s1.Score)+1 AS rank
    FROM Scores s1
    ORDER BY s1.Score DESC;
    • 利用DISTINCT去重分数相等的个数
    • 自连接对同一张表的数据进行比较
  5. 连续出现的数字

    1
    2
    3
    4
    SELECT DISTINCT(l1.Num) AS ConsecutiveNums
    FROM Logs l1, Logs l2, Logs l3
    WHERE l1.Num = l2.Num AND l2.Num = l3.Num
    AND l1.Id = l2.Id-1 AND l2.Id = l3.Id-1;
    • 利用Id 进行三表自连接查询
  6. 超过经理收入的员工

    1
    2
    3
    4
    SELECT e1.Name Employee
    FROM Employee e1,Employee e2
    WHERE e1.ManagerId = e2.Id
    AND e1.Salary > e2.Salary;
  7. 查找重复的电子邮箱

    1
    2
    3
    4
    SELECT Email
    FROM Person
    GROUP BY Email
    HAVING COUNT(*) > 1;
    • GROUP BY 计算分组内记录个数
    1
    2
    3
    SELECT DISTINCT(p1.Email) AS Email
    FROM Person p1, Person p2
    WHERE p1.Id != p2.Id AND p1.Email = p2.Email;
  8. 从不订购的客户

    1
    2
    3
    SELECT Name AS Customers
    FROM Customers
    WHERE Id NOT IN (SELECT DISTINCT(CustomerId) FROM Orders);
  9. 部门工资最高的员工

    1
    2
    3
    4
    5
    -- 当同一部门内最大工资不是一个时,只能查询到一个
    SELECT d.Name AS Department,ANY_VALUE(e.Name) AS Employee,MAX(e.Salary)
    FROM Employee e, Department d
    WHERE e.DepartmentId = d.Id
    GROUP BY Department;
    • MySql 5.7 要求在使用GRUOP BY时,SELECT内的非函数查询必须包含在GROUP BY 中,否则报错sql_mode=only_full_group_by,可以通过使用ANY_VALUE()解决
    1
    2
    3
    4
    5
    6
    SELECT dep.Name AS Department,e.Name AS Employee, e.Salary
    FROM Employee e, (SELECT d.Id, ANY_VALUE(d.Name) AS Name, MAX(e.Salary) AS m
    FROM Employee e, Department d
    WHERE e.DepartmentId = d.Id
    GROUP BY Id) AS dep
    WHERE e.DepartmentId = dep.Id AND e.Salary = dep.m;
  10. 部门工资前三高的员工

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT P2.Name AS Department,P3.Name AS Employee,P3.Salary AS Salary
    FROM Employee AS P3
    INNER JOIN Department AS P2
    ON P2.Id = P3.DepartmentId
    WHERE (
    SELECT COUNT(DISTINCT Salary)
    FROM Employee AS P4
    WHERE P3.DepartmentId = P4.DepartmentId
    AND P4.Salary >= P3.Salary
    ) <= 3
    ORDER BY DepartmentId,Salary DESC;
  11. 删除重复的电子邮箱

    1
    2
    3
    4
    5
    6
    7
    DELETE FROM Person
    WHERE Id NOT IN (
    SELECT Id FROM
    (SELECT MIN(p.Id) AS Id
    FROM Person p
    GROUP BY Email) pe
    );
    • 不嵌套一层SELECT 会报错:You can't specify target table 'Person' for update in FROM clause
  12. 上升的温度

    1
    2
    3
    SELECT w1.Id
    FROM Weather w1, Weather w2
    WHERE DATEDIFF(w1.RecordDate, w2.RecordDate)=1 AND w1.Temperature > w2.Temperature;
    • 使用DATEDIFF`函数求救两个日期的差集
    • DATE类型不能直接加减,比如“2019-04-30”+1会变成“20190431”
  13. 行程和用户

    1
    2
    3
    4
    5
    6
    SELECT t.request_at Day,(
    round(count(if(status != 'completed', status, null)) / count(status), 2)
    ) AS 'Cancellation Rate'
    FROM Users u INNER JOIN Trips t ON u.Users_id = t.Client_Id AND u.banned != 'Yes'
    WHERE t.Request_at >= '2013-10-01' AND t.Request_at <= '2013-10-03'
    GROUP BY t.Request_at;
  14. 大的国家

    1
    2
    3
    SELECT name, population, area
    FROM World
    WHERE (area > 3000000 OR population > 25000000);
    • OR用括号括起来
    1
    2
    3
    4
    5
    6
    7
    SELECT name, population, area
    FROM World
    WHERE area > 3000000
    UNION
    SELECT name, population, area
    FROM World
    WHERE population > 25000000
    • UNION:并集操作,不包括重复行,同时进行默认排序
    • UNION ALL:并集操作,包括重复行,不进行排序
  15. 超过5名学生的课

    1
    2
    3
    4
    SELECT class
    FROM courses
    GROUP BY class
    HAVING COUNT(DISTINCT(student)) >= 5;
    • 考虑重复记录
  16. 体育馆的人流量

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT DISTINCT a.*
    FROM stadium a,stadium b,stadium 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
    (a.id = b.id-1 AND a.id = c.id +1) OR
    (a.id = b.id+1 AND b.id = c.id +1)
    ) ORDER BY a.id;
    • 注意如果只一种情况,会导致最后两个满足条件的记录查询不到
  17. 有趣的电影

    1
    2
    3
    4
    SELECT *
    FROM cinema
    WHERE description != 'boring' AND id%2 = 1
    ORDER BY rating DESC;
  18. 换座位

    1
    2
    3
    4
    5
    6
    7
    SELECT (CASE
    WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
    WHEN MOD(id,2) = 1 THEN id+1
    ElSE id-1
    END) AS id, student
    FROM seat
    ORDER BY id;
  19. 交换工资

    1
    UPDATE salary SET sex = IF(sex='m','f','m');
    1
    2
    UPDATE salary
    SET sex = CHAR ( ASCII(sex) ^ ASCII( 'm' ) ^ ASCII( 'f' ) );