mysql练习LeetCode

having 是在分组后对数据进行过滤

where 是在分组前对数据进行过滤

having后面可以使用分组统计函数

where后面不可以

右外连接
右外连接 插入左边一个null数据 是可以的
outer可以省略 （没有内连接的情况下）
全外连接 左右都可以为null
但mysql不支持
进行自然连接时 要有主外键的条件 不然会出各种错误

题目一

编写一个 SQL 查询，查找Person 表中所有重复的电子邮箱。

示例：

±—±--------+
| Id | Email |
±—±--------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
±—±--------+
根据以上输入，你的查询应返回以下结果：

±--------+
| Email |
±--------+
| a@b.com |
±--------+

答案

select email
from email
group by email
having count(email) > 1

题目二

这里有张World 表

±----------------±-----------±-----------±-------------±--------------+
| name | continent | area | population | gdp |
±----------------±-----------±-----------±-------------±--------------+
| Afghanistan | Asia | 652230 | 25500100 | 20343000 |
| Albania | Europe | 28748 | 2831741 | 12960000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000 |
| Andorra | Europe | 468 | 78115 | 3712000 |
| Angola | Africa | 1246700 | 20609294 | 100990000 |
±----------------±-----------±-----------±-------------±--------------+
如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国家。

编写一个SQL查询，输出表中所有大国家的名称、人口和面积。

例如，根据上表，我们应该输出:

±-------------±------------±-------------+
| name | population | area |
±-------------±------------±-------------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
±-------------±------------±-------------+

答案：

select name, population, area 
from world
where area >  3000000 or population > 25000000

题目三

表 point 保存了一些点在 x 轴上的坐标，这些坐标都是整数。

写一个查询语句，找到这些点中最近两个点之间的距离。

| x |

|-----|

| -1 |

| 0 |

| 2 |

最近距离显然是 ‘1’ ，是点 ‘-1’ 和 ‘0’ 之间的距离。所以输出应该如下：

| shortest|

|---------|

| 1 |

注意：每个点都与其他点坐标不同，表 table 不会有重复坐标出现。

进阶：如果这些点在 x 轴上从左到右都有一个编号，输出结果时需要输出最近点对的编号呢？

答案：

select  min(abs(p1.x - p2.x)) as 'shortest' 
from point p1, point p2
where p1.x != p2.x

题目四

某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 LED显示板做电影推荐，上面公布着影评和相关电影描述。

作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。

例如，下表 cinema:

±--------±----------±-------------±----------+

| id | movie | description | rating |

±--------±----------±-------------±----------+

| 1 | War | great 3D | 8.9 |

| 2 | Science | fiction | 8.5 |

| 3 | irish | boring | 6.2 |

| 4 | Ice song | Fantacy | 8.6 |

| 5 | House card| Interesting| 9.1 |

±--------±----------±-------------±----------+

对于上面的例子，则正确的输出是为：

±--------±----------±-------------±----------+

| id | movie | description | rating |

±--------±----------±-------------±----------+

| 5 | House card| Interesting| 9.1 |

| 1 | War | great 3D | 8.9 |

±--------±----------±-------------±----------+

答案：

select * 
from cinema c
where c.id % 2 != 0 and c.description != 'boring'
order by rating desc

题目五

在表 order 中找到订单数最多客户对应的 customer_number 。

数据保证订单数最多的顾客恰好只有一位。

表 orders 定义如下：

| Column | Type |

|-------------------|-----------|

| order_number (PK) | int |

| customer_number | int |

| order_date | date |

| required_date | date |

| shipped_date | date |

| status | char(15) |

| comment | char(200) |

样例输入

| order_number | customer_number | order_date | required_date | shipped_date | status | comment |

|--------------|-----------------|------------|---------------|--------------|--------|---------|

| 1 | 1 | 2017-04-09 | 2017-04-13 | 2017-04-12 | Closed | |

| 2 | 2 | 2017-04-15 | 2017-04-20 | 2017-04-18 | Closed | |

| 3 | 3 | 2017-04-16 | 2017-04-25 | 2017-04-20 | Closed | |

| 4 | 3 | 2017-04-18 | 2017-04-28 | 2017-04-25 | Closed | |

样例输出

| customer_number |

|-----------------|

| 3 |

解释

customer_number 为 ‘3’ 的顾客有两个订单，比顾客 ‘1’ 或者 ‘2’ 都要多，因为他们只有一个订单

所以结果是该顾客的 customer_number ，也就是 3 。

答案：

select customer_number
from orders
group by customer_number
order by customer_number desc limit 1

题目六

表1: Person

±------------±--------+

| 列名 | 类型 |

±------------±--------+

| PersonId | int |

| FirstName | varchar |

| LastName | varchar |

±------------±--------+

PersonId 是上表主键

表2: Address

±------------±--------+

| 列名 | 类型 |

±------------±--------+

| AddressId | int |

| PersonId | int |

| City | varchar |

| State | varchar |

±------------±--------+

AddressId 是上表主键

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

FirstName, LastName, City, State

答案：

SELECT p.firstname, p.lastname, ad.city, ad.state
from person p, address ad
where p.personId = ad.PersonId

题目七

给定一个 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。

注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。

例如：

| id | name | sex | salary |

|----|------|-----|--------|

| 1 | A | m | 2500 |

| 2 | B | f | 1500 |

| 3 | C | m | 5500 |

| 4 | D | f | 500 |

运行你所编写的更新语句之后，将会得到以下表:

| id | name | sex | salary |

|----|------|-----|--------|

| 1 | A | f | 2500 |

| 2 | B | m | 1500 |

| 3 | C | f | 5500 |

| 4 | D | m | 500 |

答案：

update salary 
	set sex = case sex
	when 'm' then 'f'
	else 'm'
end;



select * from salary

题目八

给定表 customer ，里面保存了所有客户信息和他们的推荐人。

±-----±-----±----------+

| id | name | referee_id|

±-----±-----±----------+

| 1 | Will | NULL |

| 2 | Jane | NULL |

| 3 | Alex | 2 |

| 4 | Bill | NULL |

| 5 | Zack | 1 |

| 6 | Mark | 2 |

±-----±-----±----------+

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

对于上面的示例数据，结果为：

±-----+

| name |

±-----+

| Will |

| Jane |

| Bill |

| Zack |

±-----+

答案：

select name 
from customers
where referee_id != 2 or referee_id is NULL

题目九

ActorDirector 表：

±------------±--------+

| Column Name | Type |

±------------±--------+

| actor_id | int |

| director_id | int |

| timestamp | int |

±------------±--------+

timestamp 是这张表的主键.

写一条SQL查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)

示例：

ActorDirector 表：

±------------±------------±------------+

| actor_id | director_id | timestamp |

±------------±------------±------------+

| 1 | 1 | 0 |

| 1 | 1 | 1 |

| 1 | 1 | 2 |

| 1 | 2 | 3 |

| 1 | 2 | 4 |

| 2 | 1 | 5 |

| 2 | 1 | 6 |

±------------±------------±------------+

Result 表：

±------------±------------+

| actor_id | director_id |

±------------±------------+

| 1 | 1 |

±------------±------------+

唯一的 id 对是 (1, 1)，他们恰好合作了 3 次。

答案：

select actor_id, director_id
from actordirector
group by actor_id and director_id
having count(*) >= 3

题目十

选出所有 bonus < 1000 的员工的 name 及其 bonus。

Employee 表单

±------±-------±----------±-------+

| empId | name | supervisor| salary |

±------±-------±----------±-------+

| 1 | John | 3 | 1000 |

| 2 | Dan | 3 | 2000 |

| 3 | Brad | null | 4000 |

| 4 | Thomas | 3 | 4000 |

±------±-------±----------±-------+

empId 是这张表单的主关键字

Bonus 表单

±------±------+

| empId | bonus |

±------±------+

| 2 | 500 |

| 4 | 2000 |

±------±------+

empId 是这张表单的主关键字

输出示例：

±------±------+

| name | bonus |

±------±------+

| John | null |

| Dan | 500 |

| Brad | null |

±------±------+

答案：

注意使用左连接  这样两个表就能统计起来 然后再加条件 使用ifnull
select name,bonus
from employee e left join bonus b
on e.empid = b.empid

John 	null
Dan 	500
Brad 	null
Thomas 	2000


select name,bonus
from employee e left join bonus b
on e.empid = b.empid
where ifnull(bonus,0)<1000
或者不用函数
select name,bonus
from employee e left join bonus b
on e.empid = b.empid
where bonus<1000 or bonus is null


IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

题目十一

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

±—±------±-------±----------+

| Id | Name | Salary | ManagerId |

±—±------±-------±----------+

| 1 | Joe | 70000 | 3 |

| 2 | Henry | 80000 | 4 |

| 3 | Sam | 60000 | NULL |

| 4 | Max | 90000 | NULL |

±—±------±-------±----------+

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

±---------+

| Employee |

±---------+

| Joe |

±---------+

答案：

内连接 INNER JOIN 与 JOIN 是相同的。

INNER JOIN 关键字在表中存在至少一个匹配时返回行 如果两个表中的行没有匹配就不会列出

贼重要

[inner join 与 left join 之间的区别]

	left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 

　　right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
　　
　　inner join(等值连接) 只返回两个表中联结字段相等的行

答案：
SELECT e.Name AS Employee              -- 建表头
FROM Employee e INNER JOIN Employee m  -- 员工表-经理表
ON e.ManagerID = m.Id                  -- 员工的经理ID = 经理表的ID
AND e.Salary > m.Salary                -- 员工工资 > 经理工资



select e.name as Employee
from employee e inner join employee m
on e.managerid = m.id
and e.salary > m.salary


select e.name as Employee
from employee e inner join employee m
on e.managerid = m.id
where e.salary > m.salary

题目十二

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

Customers 表：

±—±------+

| Id | Name |

±—±------+

| 1 | Joe |

| 2 | Henry |

| 3 | Sam |

| 4 | Max |

±—±------+

Orders 表：

±—±-----------+

| Id | CustomerId |

±—±-----------+

| 1 | 3 |

| 2 | 1 |

±—±-----------+

例如给定上述表格，你的查询应返回：

±----------+

| Customers |

±----------+

| Henry |

| Max |

±----------+

答案：

使用LEFT JOIN的方法，最终对应的CustomerId为空的用户，即为没有购买的用户 

select c.name as Customers                -- 建表头
from customers1 c						-- 来源表						
left join orders1 o     -- 左连接，以左表所有顾客信息 对应符合条件右边的顾客信息 
on c.id = o.customerid       -- 关联依据
where o.customerid is null      -- 筛选

另外一种 not in 剔除不符合条件的

select c1.name as Customers                -- 建表头
from customers1  c1                        -- 来源表
where id not in(                           -- 外层筛选
	select c2.id                           -- 提取内层筛选的Id
	from customers1 c2, orders1 o2         -- 来源表
	where c2.id = o2.customerid            -- 内层筛选，找出有购买的
	)

题目十三

一个小学生 Tim 的作业是判断三条线段是否能形成一个三角形。

然而，这个作业非常繁重，因为有几百组线段需要判断。

假设表 table 保存了所有三条线段的三元组 x, y, z ，你能帮 Tim 写一个查询语句，来判断每个三元组是否可以组成一个三角形吗？

| x | y | z |

|----|----|----|

| 13 | 15 | 30 |

| 10 | 20 | 15 |

对于如上样例数据，你的查询语句应该返回如下结果：

| x | y | z | triangle |

|----|----|----|----------|

| 13 | 15 | 30 | No |

| 10 | 20 | 15 | Yes |

答案:

select *, if(x + y > z and x + z > y and y + z > x, 'Yes', 'No') as triangle
from table1

题目十四

描述

给定 3 个表： salesperson， company， orders。

输出所有表 salesperson 中，没有向公司 ‘RED’ 销售任何东西的销售员。

解释

输入

表： salesperson

±---------±-----±-------±----------------±----------+

| sales_id | name | salary | commission_rate | hire_date |

±---------±-----±-------±----------------±----------+

| 1 | John | 100000 | 6 | 4/1/2006 |

| 2 | Amy | 120000 | 5 | 5/1/2010 |

| 3 | Mark | 65000 | 12 | 12/25/2008|

| 4 | Pam | 25000 | 25 | 1/1/2005 |

| 5 | Alex | 50000 | 10 | 2/3/2007 |

±---------±-----±-------±----------------±----------+

表 salesperson 存储了所有销售员的信息。每个销售员都有一个销售员编号 sales_id 和他的名字 name 。

表： company

±--------±-------±-----------+

| com_id | name | city |

±--------±-------±-----------+

| 1 | RED | Boston |

| 2 | ORANGE | New York |

| 3 | YELLOW | Boston |

| 4 | GREEN | Austin |

±--------±-------±-----------+

表 company 存储了所有公司的信息。每个公司都有一个公司编号 com_id 和它的名字 name 。

表： orders

±---------±-----------±--------±---------±-------+

| order_id | order_date | com_id | sales_id | amount |

±---------±-----------±--------±---------±-------+

| 1 | 1/1/2014 | 3 | 4 | 100000 |

| 2 | 2/1/2014 | 4 | 5 | 5000 |

| 3 | 3/1/2014 | 1 | 1 | 50000 |

| 4 | 4/1/2014 | 1 | 4 | 25000 |

±---------±---------±--------±---------±-------+

表 orders 存储了所有的销售数据，包括销售员编号 sales_id 和公司编号 com_id 。

输出

±-----+

| name |

±-----+

| Amy |

| Mark |

| Alex |

±-----+

解释

根据表 orders 中的订单 ‘3’ 和 ‘4’ ，容易看出只有 ‘John’ 和 ‘Pam’ 两个销售员曾经向公司 ‘RED’ 销售过。

所以我们需要输出表 salesperson 中所有其他人的名字。

答案：

独立完成的 多重的查询嵌套

select s1.name 
from salesperson s1    
where s1.sales_id not in (    //找所有的员工信息 剔除给RED公司销售过的员工即可
	select s2.sales_id 
	from salesperson s2, company c, orders2 o
	where s2.sales_id = o.sales_id and c.com_id = o.com_id
	and o.com_id = (select c3.com_id from company c3   //找名字为RED的公司编号 跟订单表对应
	                  where c3.name = 'RED')

)

题目十五

编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

±—±-----------------+

| Id | Email |

±—±-----------------+

| 1 | john@example.com |

| 2 |john@example.com |

| 3 | john@example.com |

±—±-----------------+

Id 是这个表的主键。

例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

±—±-----------------+

| Id | Email |

±—±-----------------+

| 1 | john@example.com |

| 2 | bob@example.com |

±—±-----------------+

答案：

delete e1
from email e1, email e2
where e1.email = e2.email and e1.id > e2.id

题目十六

给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。

±--------±-----------------±-----------------+

| Id(INT) | RecordDate(DATE) | Temperature(INT) |

±--------±-----------------±-----------------+

| 1 | 2015-01-01 | 10 |

| 2 | 2015-01-02 | 25 |

| 3 | 2015-01-03 | 20 |

| 4 | 2015-01-04 | 30 |

±--------±-----------------±-----------------+

例如，根据上述给定的 Weather 表格，返回如下 Id:

±—+

| Id |

±—+

| 2 |

| 4 |

±—+

答案：

select w1.id 
from weather w1, weather w2
where  w1.id - w2.id = 1 and w1.temperature > w2.temperature

题目十七

表 my_numbers 的 num 字段包含很多数字，其中包括很多重复的数字。

你能写一个 SQL 查询语句，找到只出现过一次的数字中，最大的一个数字吗？

±–+

|num|

±–+

| 8 |

| 8 |

| 3 |

| 3 |

| 1 |

| 4 |

| 5 |

| 6 |

对于上面给出的样例数据，你的查询语句应该返回如下结果：

±–+

|num|

±–+

| 6 |

答案：

select max(num)
from my_numbers
where num in(select num
from my_numbers
group by num
having count(num) = 1)

题目十八

有一个courses 表 ，有: student (学生) 和 class (课程)。

请列出所有超过或等于5名学生的课。

例如,表:

±--------±-----------+

| student | class |

±--------±-----------+

| A | Math |

| B | English |

| C | Math |

| D | Biology |

| E | Math |

| F | Computer |

| G | Math |

| H | Math |

| I | Math |

±--------±-----------+

应该输出:

±--------+

| class |

±--------+

| Math |

±--------+

Note:

学生在每个课中不应被重复计算。

答案:

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

题目十九

在 Facebook 或者 Twitter 这样的社交应用中，人们经常会发好友申请也会收到其他人的好友申请。现在给如下两个表：

表： friend_request

| sender_id | send_to_id |request_date|

|-----------|------------|------------|

| 1 | 2 | 2016_06-01 |

| 1 | 3 | 2016_06-01 |

| 1 | 4 | 2016_06-01 |

| 2 | 3 | 2016_06-02 |

| 3 | 4 | 2016-06-09 |

表： request_accepted

| requester_id | accepter_id |accept_date |

|--------------|-------------|------------|

| 1 | 2 | 2016_06-03 |

| 1 | 3 | 2016-06-08 |

| 2 | 3 | 2016-06-08 |

| 3 | 4 | 2016-06-09 |

| 3 | 4 | 2016-06-10 |

写一个查询语句，求出好友申请的通过率，用 2 位小数表示。通过率由接受好友申请的数目除以申请总数。

对于上面的样例数据，你的查询语句应该返回如下结果。

|accept_rate|

|-----------|

| 0.80|

注意:

通过的好友申请不一定都在表 friend_request 中。在这种情况下，你只需要统计总的被通过的申请数（不管它们在不在原来的申请中），并将它除以申请总数，得到通过率

一个好友申请发送者有可能会给接受者发几条好友申请，也有可能一个好友申请会被通过好几次。这种情况下，重复的好友申请只统计一次。

如果一个好友申请都没有，通过率为 0.00 。

解释： 总共有 5 个申请，其中 4 个是不重复且被通过的好友申请，所以成功率是 0.80 。

答案：

select 

FORMAT(
(
select count(*) from
(select distinct requester_id, accepter_id from request_accepted)
r)
/
(
select count(*) from 
(select distinct sender_id, send_to_id from friend_request)
f
)  ,2)
as accept_rate


-- 注意是保留两位小数FORMAT(X,D) 


或者也可以加group by
select 

FORMAT(
(
select count(*) from
(select distinct requester_id, accepter_id
from request_accepted
GROUP BY requester_id, accepter_id)
r)
/
(
select count(*) from 
(select distinct sender_id, send_to_id from friend_request
GROUP BY sender_id, send_to_id)
f
)  ,2)
as accept_rate

结果一样

分析

1.就是先查出不重复的好友申请或接受
(select distinct requester_id, accepter_id from request_accepted)
(select distinct sender_id, send_to_id from friend_request)

2.然后外面再套一层 select count (*) from (上面的内容)
这样就能求出数量

3.在最外面再套一层 select  两个数量相除 使用除号 /  再写个别名 这样不就好了


4.最外面那一层 不是两个数据相除嘛 但是这两个数据查出来的 必须起个别名 不然会报Every derived table must have its own alias 错误
（
在做多表查询，或者查询的时候产生新的表的时候会出现这个错误：Every derived table must have its own alias（每一个派生出来的表都必须有一个自己的别名）
）