175.组合两个表
思想:左外连接
# Write your MySQL query statement below
# 左外连接查询
# 起别名
-- select tp.FirstName, tp.LastName, ta.City, ta.State
-- from
-- Person tp
-- left join
-- Address ta
-- on
-- tp.PersonId = ta.PersonId;
# 不起别名,直接写
select FirstName, LastName, City, State
from
Person
left join
Address
on
Person.PersonId = Address.PersonId;
176. 第二高的薪水
# 2020.4.18 anan
# 思路:先找到最大的工资,然后从不是最大的工资的人里再找最大的工资的人
select
max(salary) as SecondHighestSalary
from
Employee
where
salary <> (select max(Salary) from Employee);
思想:分页
SELECT
(SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1) AS SecondHighestSalary
作者:LeetCode
链接:https://leetcode-cn.com/problems/second-highest-salary/solution/di-er-gao-de-xin-shui-by-leetcode/
来源:力扣(LeetCode)
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary
作者:LeetCode
链接:https://leetcode-cn.com/problems/second-highest-salary/solution/di-er-gao-de-xin-shui-by-leetcode/
来源:力扣(LeetCode)
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
解决null问题
1.使用临时表 select NULL 返回null
2.使用IFNULL函数
limit n子句表示查询结果返回前n条数据
offset n表示跳过x条语句
limit y offset x 分句表示查询结果跳过 x 条数据,读取前 y 条数据
使用limit和offset,降序排列再返回第二条记录可以得到第二大的值。
题型:如何查找第n高的数据?
181. 超过经理收入的员工
# 2020.4.18 anan
SELECT
t1.Name Employee
From
Employee t1
WHERE
t1.Salary > (SELECT t2.Salary from Employee t2 where t2.Id=t1.ManagerId);
内连接
# 官解:隐式内连接 where 但是他把条件放到一起了
SELECT
a.Name AS 'Employee'
FROM
Employee AS a,
Employee AS b
WHERE
a.ManagerId = b.Id
AND a.Salary > b.Salary
作者:LeetCode
链接:https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/solution/chao-guo-jing-li-shou-ru-de-yuan-gong-by-leetcode/
来源:力扣(LeetCode)
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
# 显示内连接
SELECT
a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
ON a.ManagerId = b.Id
AND a.Salary > b.Salary
作者:LeetCode
链接:https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/solution/chao-guo-jing-li-shou-ru-de-yuan-gong-by-leetcode/
来源:力扣(LeetCode)
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
182. 查找重复的电子邮箱
【解题思路】
1.看到“找重复”的关键字眼,首先要用分组函数(group by),再用聚合函数中的计数函数count()给姓名列计数。
2.分组汇总后,生成了一个如下的表。从这个表里选出计数大于1的姓名,就是重复的姓名。
法1:使用 GROUP BY 和临时表
# 2020.4.18 anan
SELECT
Email
FROM
( -- 子查询作为一个虚拟表 找到每个邮箱的个数
SELECT
Email, count(Email) num
FROM
Person
GROUP BY
Email
) t
WHERE
t.num > 1
法2:使用 GROUP BY 和 HAVING 条件
向 GROUP BY 添加条件的一种更常用的方法是使用 HAVING 子句,该子句更为简单高效。
where 和 having 的区别?
- where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来
- where 后不可以跟聚合函数,having可以进行聚合函数的判断。
select Email
from Person
group by Email
having count(Email) > 1;
题型:如何查找重复数据?
197. 上升的温度
知识点:日期的比较(datediff)
select datediff(year, 开始日期,结束日期); --两日期间隔年
select datediff(quarter, 开始日期,结束日期); --两日期间隔季
select datediff(month, 开始日期,结束日期); --两日期间隔月
select datediff(day, 开始日期,结束日期); --两日期间隔天
select datediff(week, 开始日期,结束日期); --两日期间隔周
select datediff(hour, 开始日期,结束日期); --两日期间隔小时
select datediff(minute, 开始日期,结束日期); --两日期间隔分
select datediff(second, 开始日期,结束日期); --两日期间隔秒
# 2020.4.18 anan
SELECT t1.Id
FROM Weather t1, Weather t2
WHERE
datediff(t1.RecordDate, t2.RecordDate) = 1
AND t1.Temperature > t2.Temperature;
595. 大的国家
条件查询
SELECT name, population, area
FROM World
WHERE
area > 3000000 OR population > 25000000;
596. 超过5名学生的课
group by 和having
知识点:去重复
SELECT class
FROM
(SELECT DISTINCT * FROM courses) t
GROUP BY class
HAVING count(student) >= 5
SELECT
class
FROM
courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5
# select class from courses group by class having count(distinct(student))>=5 这样写也可以
作者:LeetCode
链接:https://leetcode-cn.com/problems/classes-more-than-5-students/solution/chao-guo-5ming-xue-sheng-de-ke-by-leetcode/
来源:力扣(LeetCode)
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
620. 有趣的电影
知识点:奇数判断
SELECT
*
FROM
cinema
WHERE
description <> 'boring'
AND id%2 = 1
ORDER BY
rating DESC
SELECT
*
FROM
cinema
WHERE
description <> 'boring'
AND id%2
ORDER BY
rating DESC
SELECT
*
FROM
cinema
WHERE
description <> 'boring'
AND id&1
ORDER BY
rating DESC
627. 交换工资
方法:使用 UPDATE 和 CASE…WHEN
算法
要想动态地将值设置成列,我们可以在使用 CASE…WHEN… 流程控制语句的同时使用 UPDATE 语句。
MySQL
UPDATE salary
SET
sex = CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END;
作者:LeetCode
链接:https://leetcode-cn.com/problems/swap-salary/solution/jiao-huan-gong-zi-by-leetcode/
来源:力扣(LeetCode)
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
1179. 重新格式化部门表
列拆成行 行合并
SELECT id,
SUM(CASE `month` WHEN 'Jan' THEN revenue END) Jan_Revenue,
SUM(CASE `month` WHEN 'Feb' THEN revenue END) Feb_Revenue,
SUM(CASE `month` WHEN 'Mar' THEN revenue END) Mar_Revenue,
SUM(CASE `month` WHEN 'Apr' THEN revenue END) Apr_Revenue,
SUM(CASE `month` WHEN 'May' THEN revenue END) May_Revenue,
SUM(CASE `month` WHEN 'Jun' THEN revenue END) Jun_Revenue,
SUM(CASE `month` WHEN 'Jul' THEN revenue END) Jul_Revenue,
SUM(CASE `month` WHEN 'Aug' THEN revenue END) Aug_Revenue,
SUM(CASE `month` WHEN 'Sep' THEN revenue END) Sep_Revenue,
SUM(CASE `month` WHEN 'Oct' THEN revenue END) Oct_Revenue,
SUM(CASE `month` WHEN 'Nov' THEN revenue END) Nov_Revenue,
SUM(CASE `month` WHEN 'Dec' THEN revenue END) Dec_Revenue
FROM Department
GROUP BY id;
作者:hu-tool
链接:https://leetcode-cn.com/problems/reformat-department-table/solution/zhong-xin-ge-shi-hua-bu-men-biao-by-hu-tool/
来源:力扣(LeetCode)
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。