sql-1 - 簡書

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. 連續(xù)出現的數字

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

?著作權歸作者所有,轉載或內容合作請聯系作者