Leetcode-sql-one

FirstName, LastName, City, State

select FirstName, LastName, City, State
from Person
left join Address on   Person.PersonID=Address.PersonID;  -- left join 会保留左表的所记录，右表中不存在的字段则表示NULL

select ifnull((select distinct Salary   -- 某些员工的薪水可能相同，去重功能
               from Employee
               order by Salary desc   -- 降序排列
               limit 1 offset 1), null)   -- limit 1 offset 1 等价于limit 2, 1
        as SecondHighestSalary

select (select distinct Salary   -- ()里面的select语句是建立临时表，解决只有一条记录的问题
        from Employee
        order by Salary desc
        limit 1 offset 1)
  as SecondHighestSalary

select max(Salary) SecondHighestSalary
from Employee
where Salary < (select max(Salary) from Employee);   -- where语句是先去掉最高的，再从剩下的当中寻求最高的，即第二高

select a.Name Employee
from Employee a
join Employee b on a.ManagerID=b.Id
where a.Salary > b.Salary;

select a.Name Employee
from
    Employee a,
    Employee b
where a.ManagerID=b.Id
and a.Salary > b.Salary

-- 自己的答案
select distinct(Email)   -- 去重
from Person a,
     Person b
 where a.Email=b.Email   -- 过滤条件
 and a.id != b.id;

select Email
from Person
group by Email  -- 分组
having count(Email) > 1;  -- 过滤

select distinct(p1.Email)
from Person a
join Person b on a.Email=b.Email
and a.id != b.id;   -- 和自己的类似，将where条件换成了join连接

select Email
from (
  select Email, count(Email) as num
  from Person
  group by Email
) as statistic   -- 建立临时表，必须要有
where num > 1;

-- 自己答案
select Name as 'Customers'
from Customers
where Customers.Id not in
(select CustomerId
from Orders
join Customers on Orders.CustomerId=Customers.Id);

-- 官方答案
select customers.name as 'Customers'
from customers
where customers.id not in (
      select customerid from orders
)

-- 根据SQL连接来解决：查找不在某个表中的数据，在a中，但是不在b中

select a.Name as Customers
from Customers a
left join Orders as b
on a.Id=b.CustomerId
where b.CustomerId is null;   -- b中是空的；不能用 =null