leetcode数据库

一.175. 组合两个表

底下两种，左图Full Join

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

1.为什么这里用on，和用where的区别

（1）on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
（2）where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
就是说，如果里面的值为空，on仍会显示，但where会把它过滤掉。

二.176. 第二高的薪水

select DISTINCT Salary As SecondHighestSalary 
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;

但这种情况下，如果第二个高的内容为空，会报错。所以用一个临时表解决。

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary;

1.DISTINCT

Distinct支持单列、多列的去重方式。单列去重的方式简明易懂，即相同值只保留1个。 多列的去重则是根据指定的去重的列信息来进行，即只有所有指定的列信息都相同，才会被认为是重复的信息。

2.LIMIT 、OFFSET

limit 与 offset：从下标0开始
offset X 是跳过X个数据
limit Y 是选取Y个数据
limit X,Y 中X表示跳过X个数据，读取Y个数据

三.181. 超过经理收入的员工

select a.Name As 'Employee'
from 
Employee as a,
Employee as b
where 
a.ManagerId=b.Id and a.Salary>b.Salary;

四.182. 查找重复的电子邮箱

方法一：构造临时表
临时表在断开数据库链接时自动销毁。

select Email from
(
    select Email,count(Email)as num 
    from Person
    Group By Email
)as newTable

where num>1;

方法二：
用Group By+having函数

select Email from Person 
Group By Email
having count(Email)>1;

五.183. 从不订购的客户

用not in 来写

select Customers.Name as 'Customers' From Customers
where Customers.id not in
(
    select customerid from Orders
);

但看各位大佬的讨论，说not in不利于在SQL数据优化，继而给出了一种链接表的方法：

select a.Name as Customers
from Customers as a left join Orders as b
on a.Id=b.CustomerId
where b.CustomerId is null;

六.197. 上升的温度

SELECT b.Id
FROM Weather as a,Weather as b
WHERE a.Temperature < b.Temperature and DATEDIFF(a.RecordDate,b.RecordDate) = -1;

1.DATADIFF函数

计算两者的日期差
DATEDIFF(‘2007-12-31’,‘2007-12-30’); # 1
DATEDIFF(‘2010-12-30’,‘2010-12-31’); # -1

七.196. 删除重复的电子邮箱

Delete p1
from Person p1,Person p2
Where
p1.Email=p2.Email and p1.Id>p2.Id

八.620. 有趣的电影

select *
from cinema 
where description!='boring' and Mod(id,2)=1
order by rating DESC

1.MOD()函数

取余是用函数mod(numer1,number2)，其返回的值为其余数值

九.596. 超过5名学生的课

注意最后数数的时候要用Distinct去重

select class from courses
group by class
having count(Distinct student)>=5;

十.627. 交换工资

用update-set 和if函数进行编写

Update salary Set sex=If(sex='m','f','m')

Update-Set

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

十一.595. 大的国家

方法一：常规解法

select name,population,area from World
where area>3000000 or population>25000000;

方法二：用Union

select name,population,area from World
where area>3000000 
Union
select name,population,area from World
where population>25000000 ;

Union()

union去重并排序，union all直接返回合并的结果，不去重也不排序；
union all比union性能好；

十二.1179. 重新格式化部门表

改变表的结构

select id,
sum(case month when 'Jan' then revenue end) as Jan_Revenue,
sum(case month when 'Feb' then revenue end) as Feb_Revenue,
sum(case month when 'Mar' then revenue end) as Mar_Revenue,
sum(case month when 'Apr' then revenue end) as Apr_Revenue,
sum(case month when 'May' then revenue end) as May_Revenue,
sum(case month when 'Jun' then revenue end) as Jun_Revenue,
sum(case month when 'Jul' then revenue end) as Jul_Revenue,
sum(case month when 'Aug' then revenue end) as Aug_Revenue,
sum(case month when 'Sep' then revenue end) as Sep_Revenue,
sum(case month when 'Oct' then revenue end) as Oct_Revenue,
sum(case month when 'Nov' then revenue end) as Nov_Revenue,
sum(case month when 'Dec' then revenue end) as Dec_Revenue
from Department
group by id;