sql-1

175. 组合两个表

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

176. 第二高的薪水

当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量,例如
select* from article LIMIT 1,3 就是跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据
当 limit后面跟一个参数的时候，该参数表示要取的数据的数量
例如 select* from article LIMIT 3  表示直接取前三条数据，类似sqlserver里的top语法。
当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 。
例如select * from article LIMIT 3 OFFSET 1 表示跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据
SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

177. 第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
            salary
      FROM 
            employee
      GROUP BY 
            salary
      ORDER BY 
            salary DESC
      LIMIT N, 1
  );
END

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET n = N-1;
  RETURN (     
  SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT n,1
  );
END
 
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (     
  SELECT  IF(count<N,NULL,min) 
  FROM
    (SELECT MIN(Salary) AS min, COUNT(1) AS count
    FROM
      (SELECT DISTINCT Salary
      FROM Employee ORDER BY Salary DESC LIMIT N) AS a
    ) as b
  );
END

178. 分数排名

select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as 'Rank'
from Scores a
order by a.Score DESC

# Write your MySQL query statement below
select Score,
dense_rank() over(Order By Score desc) 'rank'
FROM Scores;

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级

image.png

180. 连续出现的数字

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
;

181. 超过经理收入的员工

SELECT
     a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
     ON a.ManagerId = b.Id
     AND a.Salary > b.Salary
;

182. 查找重复的电子邮箱

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

183. 从不订购的客户

select c.Name as Customers from (select b.Id,a.Name from Customers a left join Orders b on a.Id=b.CustomerId)  c where c.Id is null

select customers.name as 'Customers'
from customers
where customers.id not in
(
    select customerid from orders
);

184. 部门工资最高的员工

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
    )
;

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

select b.Name as Department ,a.Name as Employee ,a.Salary  from (select Id,Name,Salary,DepartmentId, dense_rank() over (partition by DepartmentId order by Salary desc) as ranking from Employee) a join Department b on a.DepartmentId =b.Id where a.ranking <=3