leetcode_database由易到难记录一（简单）

题目来源链接
分类 - Database
其中会员才能看的题需要注明出处：
来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

细节问题回顾
1.SQL是否对大小写敏感：
关键字、表中的字段函数名均不敏感。

简单题目、解答、相关概念

1.组合两个表
编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：
FirstName, LastName, City, State

select p.FirstName,p.LastName,a.City,a.State 
from Person as p left join Address as a on p.PersonId = a.PersonId

2.第二高的薪水
编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。没有则返回null。

相关概念：
limit子句和offset子句用法：
【MySQL】LIMIT以及LIMIT OFFSET 使用方法介绍

select
    (select distinct
        salary 
        from employee
        order by salary desc
        limit 1 offset 1) 
            as SecondHighestSalary

3.超过经理收入的员工
给定 Employee 表，编写一个SQL查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe是唯一一个收入超过他的经理的员工。
运行错误原因：
输出的表格结构不符合题目要求

select a.name as Employee #此处的as语句将决定输出结果的属性名
from employee as a , employee as b
where a.managerid = b.id 
    and a.salary > b.salary

4.查找重复的电子邮箱
编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
说明：所有电子邮箱都是小写字母。

select Email
from Person
group by Email
having count(Email) >1 #注意SQL语法，计数(计算有多少条数据)使用count

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

select Customers.name as Customers
from Customers
where Customers.Id not in 
(select Customerid from Orders)

6.删除重复的电子邮箱
编写一个SQL查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留Id最小的那个。
注意：
自连接效率较低，待提升

delete p1
from Person as p1,Person as p2
where p1.email = p2.email
    and p1.id > p2.id 

7.上升的温度
给定一个Weather表，编写一个SQL查询，来查找与之前（昨天的）日期相比温度更高的所有日期的Id。
注意：
不能只因为表面的ID与日期之间的关系就不使用日期来比较今天和昨天的差别。
datediff函数的使用方法是如何的？

select w1.id as Id 
from Weather w1, Weather w2
where w1.Temperature > w2.Temperature
    #and w1.id = w2.id + 1
    and datediff(w1.RecordDate,w2.RecordDate) = 1 #w1 是 w2的后一天

8.游戏玩法分析
写一条 SQL 查询语句获取每位玩家第一次登陆平台的日期。

select player_id, min(event_date) as first_login
from Activity
group by player_id 

9.游戏玩法分析 II
请编写一个 SQL 查询，描述每一个玩家首次登陆的设备名称
注意：
where的效率比join慢

select a.player_id, a.device_id 
from Activity as a
where (a.player_id, a.event_date) #注意where后面包含两个以上的字段要加括号
in (
    select player_id, min(event_date) as first_login
    from Activity
    group by player_id)

10.员工奖金
选出所有 bonus < 1000 的员工的 name 及其 bonus。

select e.name as name, b.bonus as bonus 
from Employee as e left join Bonus as b 
on e.empId = b.empId
where b.bonus < 1000 or b.bonus is null 
#注意：没有奖金也算是奖金小于1000

11.寻找用户推荐人
写一个查询语句，返回一个编号列表，列表中编号的推荐人的编号都不是 2。

select customer.name
from customer
where customer.referee_id <> 2 or customer.referee_id is null

12.订单最多的客户
在表orders中找到订单数最多客户对应的customer_number。
(1)数据保证订单数最多的顾客恰好只有一位。
(2)如果有多位顾客订单数并列最多，你能找到他们所有的 customer_number 吗？

（1）
select customer_number
from orders
group by customer_number
order by count(*) desc
limit 1
（2）
select customer_number
from orders
group by customer_number
having count(order_number) =
(select count(order_number)
from orders
group by customer_number
order by count(order_number) desc
limit 1)
#作者：mei-shi-kan-kan-shu
#链接：https://leetcode-cn.com/problems/customer-placing-the-largest-number-of-orders/solution/jie-he-guan-fang-ti-jie-hou-de-jin-jie-da-an-by-me/
#来源：力扣（LeetCode）
#著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

13.大的国家
如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国家。
编写一个SQL查询，输出表中所有大国家的名称、人口和面积。
注意：
where待提速

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

14.超过5名学生的课
有一个courses 表 ，有: student (学生) 和 class (课程)。
请列出所有超过或等于5名学生的课。

select class
from courses
group by class
#having count(student) >= 5 每个学生的课不能被重复计算
having count(distinct(student)) >= 5

15.好友申请 I ：总体通过率
写一个查询语句，求出好友申请的通过率，用2位小数表示。通过率由接受好友申请的数目除以申请总数。
注意:
通过的好友申请不一定都在表 friend_request 中。在这种情况下，你只需要统计总的被通过的申请数（不管它们在不在原来的申请中），并将它除以申请总数，得到通过率
一个好友申请发送者有可能会给接受者发几条好友申请，也有可能一个好友申请会被通过好几次。这种情况下，重复的好友申请只统计一次。
如果一个好友申请都没有，通过率为 0.00 。
解释：
总共有 5 个申请，其中 4 个是不重复且被通过的好友申请，所以成功率是 0.80 。
进阶:
你能写一个查询语句得到每个月的通过率吗？
你能求出每一天的累计通过率吗？

16.连续空余座位
写一个查询语句，获取所有空余座位，并将它们按照seat_id排序后返回
注意：
seat_id 字段是一个自增的整数，free 字段是布尔类型（‘1’ 表示空余， ‘0’ 表示已被占据）。
连续空余座位的定义是大于等于 2 个连续空余的座位。
SQL的各种连接Join详解

select distinct c1.seat_id
from cinema c1 join cinema c2 #join 相当于 inner join
where abs(c1.seat_id-c2.seat_id) = 1
    and c1.free = 1 and c2.free = 1
order by c1.seat_id

17.销售员
给定 3 个表： salesperson， company， orders。
输出所有表 salesperson 中，没有向公司 ‘RED’ 销售任何东西的销售员。
注意：
待研究自己的解法与官方题解的差别

#报错一
select s.name as name
from salesperson as s
where s.sales_id not in
(
    select o.sales_id
    from orders as o left join company as c 
    #此处必须用左外连接，不能用join，要保证左边表的内容完整
    on o.com_id = c.com_id
    where o.com_id = 1
)
#报错二
select s.name as name
from salesperson as s
where s.sales_id not in
(
    select o.sales_id
    from orders as o right join company as c #右连接不可行
    on o.com_id = c.com_id
    where c.name = 'RED'
)

#官方给出的答案
SELECT
    s.name
FROM
    salesperson s
WHERE
    s.sales_id NOT IN (SELECT
            o.sales_id
        FROM
            orders o
                LEFT JOIN
            company c ON o.com_id = c.com_id
        WHERE
            c.name = 'RED')

#作者：LeetCode
#链接：https://leetcode-cn.com/problems/sales-person/solution/xiao-#shou-yuan-by-leetcode/
#来源：力扣（LeetCode）
#著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

#通过的答案一
select s.name as name
from salesperson as s
where s.sales_id not in
(
    select o.sales_id
    from orders as o left join company as c #用左外连接
    on o.com_id = c.com_id
    where c.name = 'RED'
)
#通过的答案二
select s.name as name
from salesperson as s
where s.sales_id not in
(
    select o.sales_id
    from orders as o join company as c #内连接
    on o.com_id = c.com_id
    where c.name = 'RED'
)

18.判断三角形
表 table 保存了所有三条线段的三元组 x, y, z ，你能帮Tim写一个查询语句，来判断每个三元组是否可以组成一个三角形
注意：
SQL中的if语句

#疑问是如何创建一个新列并存储对应的值
select *, #注意写逗号
if((x+y>z and x+z>y and y+z>x),"Yes","No") as triangle
from triangle

19.直线上的最近距离
写一个查询语句，找到这些点中最近两个点之间的距离
注意：每个点都与其他点坐标不同，表 table 不会有重复坐标出现。
进阶：如果这些点在 x 轴上从左到右都有一个编号，输出结果时需要输出最近点对的编号呢？

select min(abs(p1.x - p2.x)) as shortest
from point as p1 join point as p2
on p1.x <> p2.x

20.只出现一次的最大数字
写一个SQL查询语句，找到只出现过一次的数字中，最大的一个数字
注意：
如果没有只出现一次的数字，输出null

select max(num) as num 
from
(select num 
from my_numbers
group by num 
having count(*) = 1
) as sub

21.有趣的电影
编写一个SQL查询，找出所有影片描述为非boring(不无聊)的并且id为奇数的影片，结果请按等级rating排列

select *
from cinema
where description <> 'boring' 
    and mod(id,2) = 1
    #cinema.id/2 <> 0# 在SQL中如何表现整除的概念
order by rating desc #降序排列

22.交换工资
交换所有的f和m值（例如，将所有f值更改为m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。

update salary
set sex = if(sex = "m","f","m")

23.合作过至少三次的演员和导演
写一条SQL查询语句获取合作过至少三次的演员和导演的id对

select actor_id as ACTOR_ID, director_id as DIRECTOR_ID
from ActorDirector
group by actor_id, director_id
having count(timestamp) >= 3

24.产品销售分析
写一条SQL 查询语句获取产品表 Product 中所有的 产品名称 product name 以及 该产品在 Sales 表中相对应的 上市年份 year 和 价格 price
注意：代码提速

select p.product_name as product_name, s.year as year, s.price as price
from Product as p join Sales as s 
on p.product_id = s.product_id

25.产品销售分析 II
编写一个SQL查询，按产品id(product_id)来统计每个产品的销售总量
注意：代码提速

select product_id, sum(quantity) as total_quantity
from Sales
group by product_id

26.项目员工 I
请写一个SQL语句，查询每一个项目中员工的平均工作年限，精确到小数点后两位
SQL中求平均值的函数：avg
精确到小数点后两位

# 使用各种join均出现错误，原因在于忽略了精确度问题
select p.project_id as project_id, round(avg(e.experience_years),2) as average_years
from Project as p join Employee as e 
on p.employee_id = e.employee_id
group by project_id

27.项目员工II
编写一个SQL查询，报告所有雇员最多的项目
注意：代码效率提升

select project_id
from Project
group by project_id
having count(employee_id) >= all(
    select count(employee_id)
    from Project
    group by project_id
)

28.销售分析 I
编写一个 SQL 查询，查询总销售额最高的销售者，如果有并列的，就都展示出来
注意：性能提升

select seller_id 
from Sales
group by seller_id 
having sum(price) >= all(
    select sum(price)
    from Sales
    group by seller_id
)

29.销售分析II
编写一个SQL查询，查询购买了S8手机却没有购买iPhone的买家。注意这里S8和iPhone是Product表中的产品
注意：代码提速

select s.buyer_id
from Sales as s left join Product as p
on s.product_id = p.product_id
group by s.buyer_id
having sum(p.product_name = "S8") > 0 and sum(p.product_name = "iPhone") = 0 #逻辑表达式计数

30.销售分析III
编写一个SQL查询，报告2019年春季才售出的产品。即在2019-01-01至2019-03-31（含）之间

select distinct p.product_id as product_id, p.product_name as product_name
from Product as p left join Sales as s 
on p.product_id = s.product_id
group by s.product_id
having min(s.sale_date) >= "2019-01-01" and max(sale_date) <= "2019-03-31"

31.过去30天的用户活动 II
编写SQL查询以查找截至2019年7月27日（含）的30天内每个用户的平均会话数，四舍五入到小数点后两位。我们要为用户计算的会话是在该时间段内至少进行了一项活动的会话
SQL中的IFNULL的使用

count和sum的区别

select ifnull((round((count(distinct session_id))/(count(distinct user_id)),2)),0) as average_sessions_per_user
from Activity
where datediff("2019-07-27", activity_date) < 30

32.文章浏览 I
请编写一条SQL查询以找出所有浏览过自己文章的作者，结果按照id升序排列
注意：代码提速

select distinct author_id as id 
from Views
where author_id = viewer_id
order by author_id

33.重新格式化部门表
编写一个SQL查询来重新格式化表，使得新的表中有一个部门id列和一些对应每个月的收入(revenue）列

#行转列
select distinct id as id,
sum(if(month = "Jan", revenue, null)) as "Jan_Revenue",
sum(if(month = "Feb", revenue, null)) as "Feb_Revenue",
sum(if(month = "Mar", revenue, null)) as "Mar_Revenue",
sum(if(month = "Apr", revenue, null)) as "Apr_Revenue",
sum(if(month = "May", revenue, null)) as "May_Revenue",
sum(if(month = "Jun", revenue, null)) as "Jun_Revenue",
sum(if(month = "Jul", revenue, null)) as "Jul_Revenue",
sum(if(month = "Aug", revenue, null)) as "Aug_Revenue",
sum(if(month = "Sep", revenue, null)) as "Sep_Revenue",
sum(if(month = "Oct", revenue, null)) as "Oct_Revenue",
sum(if(month = "Nov", revenue, null)) as "Nov_Revenue",
sum(if(month = "Dec", revenue, null)) as "Dec_Revenue"
from Department
group by id