Leetcode-SQL

1、Rank scores

• 对上表中的成绩由高到低排序，并列出排名。当两个人获得相同分数时，取并列名次，且名词中无断档。

  SELECT  Score,
  DENSE_RANK() OVER(ORDER BY Score DESC) AS 'Rank'
  FROM Scores;

  窗口函数（OLAP）

• 将表以窗口为单位进行分割，并在其中进行排序的函数。

• 语法

  <窗口函数> over(
  PARTITION BY [column name1]
  ORDER BY [column name2])

  • PARTITION BY
    • 设定排序对象的范围（横向）
  • ORDER BY
    • 指定排序方式（纵向）

• 排名函数

  • RANK()

    • 按照某字段的排序结果添加排名，但它是跳跃的、间断的排名，例如两个并列第一名后，下一个是第三名。

      SELECT Score,rank() over (ORDER BY Score desc) as 'Rank' FROM score;

  • DENSE_RANK()

    • 当有相同的分数时，它们的排名结果是并列的，例如，1,2,2,3

      SELECT Score,dense_rank() over (ORDER BY Score desc) as 'Rank' FROM score;

  • ROW_NUMBER()

    • 将某字段按照顺序依次添加行号

      SELECT Score,row_number() over(ORDER BY Score desc) as 'Rank' FROM score；

• 聚合函数

  • SUM\AVG\COUNT\MAX\MIN

    SELECT SUM(score) over(ORDER BY id,c_id) AS current_sum;

2、找出连续出现三次及以上的数字

SELECT DISTINCT a.Num AS ConsecutiveNums 
From Logs as a
INNER JOIN Logs AS b ON a.Id+1 = b.Id
INNER JOIN Logs AS c on a.Id+2 = c.Id
WHERE a.Num = b.Num
AND b.Num = c.Num;

3、Employees earning more than their Managers

• 自连接

  SELECT a.Name AS 'Employee'
  FROM Employee AS a,Employee AS b
  WHERE a.ManagerId = b.Id
  AND a.Salary > b.Salary;

  4、Duplicate Email

• 查询重复的邮箱

  SELECT Email FROM Person
  GROUP BY Email
  HAVING COUNT(Id) >1;

  • 子查询 ```sql SELECT Email FROM ( SELECT Email,COUNT(id) AS num FROM Person GROUP BY Email

)WHERE num >1;

   - 自连结
```sql
SELECT DISTINCT a.Email 
FROM Person AS a
JOIN Person AS b
ON a.Email = b.Email AND a.Id<>b.Id;

5、Customers Who Never Order

select Name as 'Customer' from Customers 
left join Orders 
on Customers.Id = Orders.CustomerId
where Customers.Id is NULL;

select name as 'Customer' from Customers
where Id not in
(select CustomerId from Orders);

6、Department Highest Salary

select b.Name as 'Department', a.Name as 'Employee',a.salary as 'Salary' from Employee as a 
inner join Department as b on a.DepartmentId = b.Id
inner join
(select DepartmentId,MAX(Salary) as Salary from Employee 
 group by SepartmentId) as c
 on a.DepartmentId = c.DepartmentId
 where a.Salary = c.Salary;

SELECT b.Name AS Department, a.Name AS Employee, a.Salary AS 'Salary' FROM Employee AS a
INNER JOIN Department AS b
ON a.DepartmentId = b.Id
WHERE a.Salary IN
 (SELECT MAX(Salary) FROM Employee AS c
  WHERE a.DepartmentId = c.DepartmentId
  GROUP BY c.DepartmentId);

7、Delete Duplicate Emails

delete p1 from Person p1,Person p2
where p1.email=p2.email and p1.id>p2.id;

8、Rising Temperature

select a.id from Weather as a
join Weather as b
on a.recordDate = ADDDATE(b.recordDate,INTERVAL 1 DAY) -- DATE_ADD/DATE_SUB/SUBDATE
WHERE a.temperature>b.temperature;

9、Game Play Analysis I

select player_id,event_date as first_login from Activity 
group by player_id
order by event_date limit 0,1;