文章目录
一.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;