SQL答题笔记

来源：力扣（LeetCode）

1.编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。例如，根据给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

select a.Score as score,if(a.Score,0,(select count(b.Score)+1 from Scores b where b.score >a.Score)) as Rank from Scores a order by Score Desc;

2.编写一个 SQL 查询，查找所有至少连续出现三次的数字

select disctinct l1.num  as ConsecutiveNums from logs l1, logs l2, logs l3 where l1.ld=l2.ld-1 and l2.id=l3.id-1 and l1.num=l2.num and l2.num-l3.num

3.编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

select distinct a.Email  from Person a ,Person b where a.Email=b.Email and a.id !=b.id

4.某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

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

5

.

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
    ）

6.