• 力扣sql


    175.组合两个表
    编写一个SQL查询来报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中,则报告为空 null 。
    https://leetcode.cn/problems/combine-two-tables/solution/zu-he-liang-ge-biao-by-leetcode/

    # Write your MySQL query statement below
    select FirstName, LastName, City, State
    from Person left join Address
    on Person.PersonId = Address.PersonId
    ;
    
    • 1
    • 2
    • 3
    • 4
    • 5

    176.第二高的薪水
    编写一个 SQL 查询,获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水,查询应该返回 null 。
    https://leetcode.cn/problems/second-highest-salary/solution/di-er-gao-de-xin-shui-by-leetcode/

    # Write your MySQL query statement below
    select max(Salary) SecondHighestSalary 
    from Employee
    where Salary < (select max(Salary) from Employee)
    
    • 1
    • 2
    • 3
    • 4

    177.第N高的薪水
    https://leetcode.cn/problems/nth-highest-salary/solution/mysql-zi-ding-yi-bian-liang-by-luanz/
    编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资,查询应该报告为 null 。

    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
      );
    END
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    178.分数排名
    编写 SQL 查询对分数进行排序。排名按以下规则计算:
    分数应按从高到低排列。
    如果两个分数相等,那么两个分数的排名应该相同。
    在排名相同的分数后,排名数应该是下一个连续的整数。换句话说,排名之间不应该有空缺的数字。
    按 score 降序返回结果表。
    https://leetcode.cn/problems/rank-scores/solution/tu-jie-sqlmian-shi-ti-jing-dian-pai-ming-wen-ti-by/

    # Write your MySQL query statement below
    SELECT Score, 
        DENSE_RANK() OVER(ORDER BY Score DESC) AS 'Rank'
    FROM Scores
    
    • 1
    • 2
    • 3
    • 4

    180.连续出现的数字
    https://leetcode.cn/problems/consecutive-numbers/solution/lian-xu-chu-xian-de-shu-zi-by-leetcode/
    编写一个 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
    ;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    181.超过经理收入的员工
    编写一个SQL查询来查找收入比经理高的员工。
    https://leetcode.cn/problems/employees-earning-more-than-their-managers/solution/chao-guo-jing-li-shou-ru-de-yuan-gong-by-leetcode/

    # 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
    ;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    182.查找重复的电子邮箱
    编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。
    https://leetcode.cn/problems/duplicate-emails/solution/cha-zhao-zhong-fu-de-dian-zi-you-xiang-by-leetcode/

    # Write your MySQL query statement below
    select Email from
    (
      select Email, count(Email) as num
      from Person
      group by Email
    ) as statistic
    where num > 1
    ;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    183.从不订购的客户
    某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
    https://leetcode.cn/problems/customers-who-never-order/solution/cong-bu-ding-gou-de-ke-hu-by-leetcode/

    # Write your MySQL query statement below
    select customers.name as 'Customers'
    from customers
    where customers.id not in
    (
        select customerid from orders
    );
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    184.部门工资最高的员工
    编写SQL查询以查找每个部门中薪资最高的员工。
    https://leetcode.cn/problems/department-highest-salary/solution/bu-men-gong-zi-zui-gao-de-yuan-gong-by-leetcode/

    # Write your MySQL query statement below
    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
    	)
    ;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    185.部门工资前三高的所有员工
    公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。
    编写一个SQL查询,找出每个部门中 收入高的员工 。
    https://leetcode.cn/problems/department-top-three-salaries/solution/bu-men-gong-zi-qian-san-gao-de-yuan-gong-by-leetco/

    # Write your MySQL query statement below
    SELECT
        d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
    FROM
        Employee e1
            JOIN
        Department d ON e1.DepartmentId = d.Id
    WHERE
        3 > (SELECT
                COUNT(DISTINCT e2.Salary)
            FROM
                Employee e2
            WHERE
                e2.Salary > e1.Salary
                    AND e1.DepartmentId = e2.DepartmentId
            )
    ;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    196.删除重复的电子邮箱
    编写一个 SQL 删除语句来 删除 所有重复的电子邮件,只保留一个id最小的唯一电子邮件。
    https://leetcode.cn/problems/delete-duplicate-emails/solution/shan-chu-zhong-fu-de-dian-zi-you-xiang-by-leetcode/

    # Please write a DELETE statement and DO NOT write a SELECT statement.
    # Write your MySQL query statement below
    DELETE p1 FROM Person p1,
        Person p2
    WHERE
        p1.Email = p2.Email AND p1.Id > p2.Id
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    197.上升的温度
    编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 id 。
    https://leetcode.cn/problems/rising-temperature/solution/shang-sheng-de-wen-du-by-leetcode/

    # Write your MySQL query statement below
    SELECT w2.Id
    FROM Weather w1, Weather w2
    WHERE DATEDIFF(w2.RecordDate, w1.RecordDate) = 1
    AND w1.Temperature < w2.Temperature
    
    • 1
    • 2
    • 3
    • 4
    • 5
  • 相关阅读:
    Unity anchoredPosition转localPosition
    javaee实验,SSM整合开发综合实例
    MindSpore官方RandomChoiceWithMask算子用例报错
    怎么从零开始运行github / 现成的项目
    C++&QT-day5
    JavaWeb---Servlet
    Oauth2系列8:何谓JWT令牌?
    归并排序(Merge Sort)
    【数据结构】——单链表(增删查改)
    外汇天眼:活久见!假冒平台见多了,假冒监管机构你见过吗?
  • 原文地址:https://blog.csdn.net/cx_cs/article/details/126435845