27. 体育馆人流量
需求:请编写一个查询语句,找出人流量的高峰期。高峰期定义,至少连续三行记录中的人流量不少于100。
展示效果:
id | visit_date | people |
---|---|---|
5 | 2017-01-05 | 145 |
6 | 2017-01-06 | 1455 |
7 | 2017-01-07 | 199 |
8 | 2017-01-08 | 188 |
Create table If Not Exists 27_stadium (id int, visit_date DATE NULL, people int);
Truncate table 27_stadium;
insert into 27_stadium (id, visit_date, people) values (1, '2017-01-01', 10);
insert into 27_stadium (id, visit_date, people) values (2, '2017-01-02', 109);
insert into 27_stadium (id, visit_date, people) values (3, '2017-01-03', 150);
insert into 27_stadium (id, visit_date, people) values (4, '2017-01-04', 99);
insert into 27_stadium (id, visit_date, people) values (5, '2017-01-05', 145);
insert into 27_stadium (id, visit_date, people) values (6, '2017-01-06', 1455);
insert into 27_stadium (id, visit_date, people) values (7, '2017-01-07', 199);
insert into 27_stadium (id, visit_date, people) values (8, '2017-01-08', 188);
最终SQL:
SELECT
distinct a.*
FROM
27_stadium as a,
27_stadium as b,
27_stadium as c
where
((a.id = b.id-1 and b.id+1 = c.id) or(a.id-1 = b.id and a.id+1 = c.id) or(a.id-1 = c.id and c.id-1 = b.id))
and
(a.people>=100 and b.people>=100 and c.people>=100)
order by
a.id;
28. 连续空余座位
需求:编写一个 SQL 查询,获取所有空余座位,并将它们按照 seat_id 排序
展示效果:
seat_id |
---|
3 |
4 |
5 |
Create table If Not Exists 28_cinema (seat_id int primary key auto_increment, free bool);
Truncate table 28_cinema;
insert into 28_cinema (seat_id, free) values (1, 1);
insert into 28_cinema (seat_id, free) values (2, 0);
insert into 28_cinema (seat_id, free) values (3, 1);
insert into 28_cinema (seat_id, free) values (4, 1);
insert into 28_cinema (seat_id, free) values (5, 1);
注意:
- seat_id 字段是一个自增的整数,free 字段是布尔类型(‘1’ 表示空余, ‘0’ 表示已被占据)。
- 连续空余座位的定义是大于等于 2 个连续空余的座位。
最终SQL:
select
distinct a.seat_id
from
28_cinema a
join
28_cinema b
on
abs(a.seat_id - b.seat_id) = 1 and a.free = true and b.free = true
order by
a.seat_id;
29. 销售员
需求:输出所有表 salesperson中,没有向公司 ‘RED’ 销售任何东西的销售员。
展示效果:
name |
---|
Amy |
Mark |
Alex |
Create table If Not Exists 29_salesperson (sales_id int, name varchar(255), salary int,commission_rate int, hire_date varchar(255));
Create table If Not Exists 29_company (com_id int, name varchar(255), city varchar(255));
Create table If Not Exists 29_orders (order_id int, order_date varchar(255), com_id int, sales_id int, amount int);
Truncate table 29_salesperson;
insert into 29_salesperson (sales_id, name, salary, commission_rate, hire_date) values (1,'John',100000, 6, '4/1/2006');
insert into 29_salesperson (sales_id, name, salary, commission_rate, hire_date) values (2,'Amy', 12000, 5, '5/1/2010');
insert into 29_salesperson (sales_id, name, salary, commission_rate, hire_date) values (3,'Mark',65000, 12, '12/25/2008');
insert into 29_salesperson (sales_id, name, salary, commission_rate, hire_date) values (4,'Pam', 25000, 25, '1/1/2005');
insert into 29_salesperson (sales_id, name, salary, commission_rate, hire_date) values (5,'Alex', 5000, 10, '2/3/2007');
Truncate table 29_company;
insert into 29_company (com_id, name, city) values (1, 'RED', 'Boston');
insert into 29_company (com_id, name, city) values (2, 'ORANGE', 'New York');
insert into 29_company (com_id, name, city) values (3, 'YELLOW', 'Boston');
insert into 29_company (com_id, name, city) values (4, 'GREEN', 'Austin');
Truncate table 29_orders;
insert into 29_orders (order_id, order_date, com_id, sales_id, amount) values (1, '1/1/2014', 3, 4, 10000);
insert into 29_orders (order_id, order_date, com_id, sales_id, amount) values (2, '2/1/2014', 4, 5, 5000);
insert into 29_orders (order_id, order_date, com_id, sales_id, amount) values (3, '3/1/2014', 1, 1, 50000);
insert into 29_orders (order_id, order_date, com_id, sales_id, amount) values (4, '4/1/2014', 1, 4, 25000);
最终SQL:
SELECT
s.name
FROM
29_salesperson s
WHERE
s.sales_id NOT IN (
SELECT
o.sales_id
FROM
29_orders o
LEFT JOIN
29_company c
ON
o.com_id = c.com_id
WHERE
c.name = 'RED'
);
30. 节点树
需求:写一个查询语句,输出所有节点的编号和节点的类型,并将结果按照节点编号排序。
表 tree,id 是树节点的编号, p_id 是它父节点的 id 。
树中每个节点属于以下三种类型之一:
叶子:如果这个节点没有任何孩子节点。
根:如果这个节点是整棵树的根,即没有父节点。
内部节点:如果这个节点既不是叶子节点也不是根节点。
id | p_id |
---|---|
1 | null |
2 | 1 |
3 | 1 |
4 | 2 |
5 | 2 |
展示效果:
id | Type |
---|---|
1 | Root |
2 | Inner |
3 | Leaf |
4 | Leaf |
5 | Leaf |
解释:
节点 1 是根节点,因为它的父节点是 NULL ,同时它有孩子节点 2 和 3 。
节点 2 是内部节点,因为它有父节点 1 ,也有孩子节点 4 和 5 。
节点 3, 4 和 5 都是叶子节点,因为它们都有父节点同时没有孩子节点。
Create table If Not Exists 30_tree (id int, p_id int);
Truncate table 30_tree;
insert into 30_tree (id, p_id) values (1, null);
insert into 30_tree (id, p_id) values (2, 1);
insert into 30_tree (id, p_id) values (3, 1);
insert into 30_tree (id, p_id) values (4, 2);
insert into 30_tree (id, p_id) values (5, 2);
最终SQL:
-- 方法一:
SELECT
id, 'Root' AS Type
FROM
30_tree
WHERE
p_id IS NULL
UNION
SELECT
id, 'Leaf' AS Type
FROM
30_tree
WHERE
id NOT IN (SELECT
DISTINCT p_id
FROM
30_tree
WHERE
p_id IS NOT NULL)
AND p_id IS NOT NULL
UNION
SELECT
id, 'Inner' AS Type
FROM
30_tree
WHERE
id IN (SELECT
DISTINCT p_id
FROM
30_tree
WHERE
p_id IS NOT NULL)
AND p_id IS NOT NULL
ORDER BY id;
-- 方法二:
SELECT
id AS `Id`,
CASE
WHEN t1.id = (SELECT t2.id FROM 30_tree as t2 WHERE t2.p_id IS NULL) THEN 'Root'
WHEN t1.id IN (SELECT t3.p_id FROM 30_tree t3) THEN 'Inner'
ELSE 'Leaf'
END AS Type
FROM
30_tree t1
ORDER BY `Id`;
-- 方法三:
SELECT
t1.id,
IF(ISNULL(t1.p_id),'Root', IF(t1.id IN (SELECT p_id FROM 30_tree), 'Inner','Leaf')) Type
FROM
30_tree t1
ORDER BY t1.id;
31. 判断是否是三角形
需求:编写一个 SQL 查询,判断三条线段是否能形成一个三角形。
展示效果:
x | y | z | triangle |
---|---|---|---|
13 | 15 | 15 | No |
10 | 20 | 15 | Yes |
Create table If Not Exists 31_triangle (x int, y int, z int);
Truncate table 31_triangle;
insert into 31_triangle (x, y, z) values (13, 15, 30);
insert into 31_triangle (x, y, z) values (10, 20, 15);
最终SQL:
select
x,
y,
z,
if((x + y <= z or x + z <= y or y + z <= x), "No", "Yes") as triangle
from 31_triangle;
32. 平面上的最近距离
需求:写一个查询语句找到两点之间的最近距离,保留 2 位小数。
展示效果:
shortest |
---|
1.00 |
CREATE TABLE If Not Exists 32_point_2d (x INT NOT NULL, y INT NOT NULL);
Truncate table 32_point_2d;
insert into 32_point_2d (x, y) values (-1, -1);
insert into 32_point_2d (x, y) values (0, 0);
insert into 32_point_2d (x, y) values (-1, -2);
最终SQL:
-- 方法一:
SELECT
ROUND(SQRT(MIN((POW(p1.x - p2.x, 2) + POW(p1.y - p2.y, 2)))), 2) AS shortest
FROM
32_point_2d p1
JOIN
32_point_2d p2
ON
p1.x != p2.x OR p1.y != p2.y;
-- 方法二:
SELECT
ROUND(SQRT(MIN((POW(p1.x - p2.x, 2) + POW(p1.y - p2.y, 2)))),2) AS shortest
FROM
32_point_2d p1
JOIN
32_point_2d p2
ON (p1.x <= p2.x AND p1.y < p2.y) OR (p1.x <= p2.x AND p1.y > p2.y) OR (p1.x < p2.x AND p1.y = p2.y);
33. 直线上最近距离
需求:找到这些点中最近两个点之间的距离。
展示效果:
shortest |
---|
1 |
CREATE TABLE If Not Exists 33_point (x INT NOT NULL, UNIQUE INDEX x_UNIQUE (x ASC));
Truncate table 33_point;
insert into 33_point (x) values (-1);
insert into 33_point (x) values (0);
insert into 33_point (x) values (2);
最终SQL:
SELECT
MIN(ABS(p1.x - p2.x)) AS shortest
FROM
33_point p1
JOIN
33_point p2
ON p1.x != p2.x;
34. 二级关注者
需求:对每一个关注者(follower),查询他的关注者数目。
展示效果:
follower | num |
---|---|
B | 2 |
D | 1 |
Create table If Not Exists 34_follow (followee varchar(255), follower varchar(255));
Truncate table 34_follow;
insert into 34_follow (followee, follower) values ('A', 'B');
insert into 34_follow (followee, follower) values ('B', 'C');
insert into 34_follow (followee, follower) values ('B', 'D');
insert into 34_follow (followee, follower) values ('D', 'E');
解释:
以A为主体,A为被关注者,B为被关注者,求出关注B的关注者。这里需要注意,被关注者永远不会被他 / 她自己关注。
将结果按照字典序返回。
最终SQL:
select
followee as 'follower',
count(distinct follower) as num
from
34_follow
where
followee in(select follower from 34_follow)
group by 1
order by 1;
说明:这里的group by 表示使用第一列作为分组依据,order by 同理。
35. 平均工资
需求:写一个查询语句,求出在每一个工资发放日,每个部门的平均工资与公司的平均工资的比较结果 (高 / 低 / 相同)
展示效果:
pay_month | department_id | comparison |
---|---|---|
2017-03 | 1 | higher |
2017-03 | 2 | lower |
2017-02 | 1 | same |
2017-02 | 2 | same |
Create table If Not Exists 35_salary (id int, employee_id int, amount int, pay_date date);
Create table If Not Exists 35_employee (employee_id int, department_id int);
Truncate table 35_salary;
insert into 35_salary (id, employee_id, amount, pay_date) values (1, 1, 9000, '2017/03/31');
insert into 35_salary (id, employee_id, amount, pay_date) values (2, 2, 6000, '2017/03/31');
insert into 35_salary (id, employee_id, amount, pay_date) values (3, 3, 10000, '2017/03/31');
insert into 35_salary (id, employee_id, amount, pay_date) values (4, 1, 7000, '2017/02/28');
insert into 35_salary (id, employee_id, amount, pay_date) values (5, 2, 6000, '2017/02/28');
insert into 35_salary (id, employee_id, amount, pay_date) values (6, 3, 8000, '2017/02/28');
Truncate table 35_employee;
insert into 35_employee (employee_id, department_id) values (1, 1);
insert into 35_employee (employee_id, department_id) values (2, 2);
insert into 35_employee (employee_id, department_id) values (3, 2);
解释:
在三月,公司的平均工资是 (9000+6000+10000)/3 = 8333.33…
由于部门 1 里只有一个 employee_id 为 1 的员工,所以部门 1 的平均工资就是此人的工资 9000 。因为 9000 > 8333.33 ,所以比较结果是 ‘higher’。
第二个部门的平均工资为 employee_id 为 2 和 3 两个人的平均工资,为 (6000+10000)/2=8000 。因为 8000 < 8333.33 ,所以比较结果是 ‘lower’ 。
在二月用同样的公式求平均工资并比较,比较结果为 ‘same’ ,因为部门 1 和部门 2 的平均工资与公司的平均工资相同,都是 7000 。
最终SQL:
select
department_salary.pay_month,
department_id,
case
when department_avg>company_avg then 'higher'
when department_avg<company_avg then 'lower'
else 'same'
end as comparison
from
(select
department_id,
avg(amount) as department_avg,
date_format(pay_date, '%Y-%m') as pay_month
from
35_salary as s1
join
35_employee as e1
on
s1.employee_id = e1.employee_id
group by
department_id, pay_month
) as department_salary
join
(select
avg(amount) as company_avg,
date_format(pay_date, '%Y-%m') as pay_month
from
35_salary
group by
date_format(pay_date, '%Y-%m')
) as company_salary
on
department_salary.pay_month = company_salary.pay_month;
36. 学生地理信息报告
需求:写一个查询语句实现对大洲(continent)列的 透视表 操作,使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲(America)、亚洲(Asia)和欧洲(Europe)。数据保证来自美洲的学生不少于来自亚洲或者欧洲的学生。
展示效果:
America | Asia | Europe |
---|---|---|
Jack | Xi | Pascal |
Jane |
Create table If Not Exists 36_student (name varchar(50), continent varchar(7));
Truncate table 36_student;
insert into 36_student (name, continent) values ('Jane', 'America');
insert into 36_student (name, continent) values ('Pascal', 'Europe');
insert into 36_student (name, continent) values ('Xi', 'Asia');
insert into 36_student (name, continent) values ('Jack', 'America');
最终SQL:
SELECT
MAX(if(A.continent = 'America',A.NAME,NULL)) AS `America`,
MAX(if(A.continent = 'Asia',A.NAME,NULL)) AS `Asia`,
MAX(if(A.continent = 'Europe',A.NAME,NULL)) AS `Europe`
FROM
(SELECT
S1.continent,
S1.NAME,
S1.row_id,
COUNT(*) AS `trank`
FROM
(SELECT
S.*,
@row_id:=(@row_id + 1) AS `row_id`
FROM
36_student AS S,
(SELECT @row_id:=0) AS T
) AS S1
JOIN
(SELECT
S.*,
@n_row_id:=(@n_row_id + 1) AS `n_row_id`
FROM
36_student AS S,
(SELECT @n_row_id:=0) AS T
) AS S2
ON
(S1.continent = S2.continent AND (S1.NAME > S2.NAME OR (S1.NAME = S2.NAME AND S1.row_id >= S2.n_row_id)))
group BY
S1.continent,S1.NAME,S1.row_id
order BY
S1.continent,S1.NAME
) AS A
GROUP BY
A.trank;
37. 只出现一次的最大数字
需求:编写一个 SQL 查询,找到只出现过一次的数字中,最大的一个数字。如果没有只出现一次的数字,输出 null 。
展示效果:
num |
---|
6 |
Create table If Not Exists 37_my_numbers (num int);
Truncate table 37_my_numbers;
insert into 37_my_numbers (num) values (8);
insert into 37_my_numbers (num) values (8);
insert into 37_my_numbers (num) values (3);
insert into 37_my_numbers (num) values (3);
insert into 37_my_numbers (num) values (1);
insert into 37_my_numbers (num) values (4);
insert into 37_my_numbers (num) values (5);
insert into 37_my_numbers (num) values (6);
最终SQL:
select
ifnull((SELECT
num
FROM
37_my_numbers
group by
num
having
count(*) = 1
order by
num desc
limit 1), null) as num;
38. 有趣的电影
需求:编写一个 SQL 查询,找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片,结果请按等级 rating 排列
展示效果:
id | movie | description | rating |
---|---|---|---|
5 | House card | Interesting | 9.1 |
1 | War | great 3D | 8.9 |
Create table If Not Exists 38_cinema (id int, movie varchar(255), description varchar(255), rating float(2, 1));
Truncate table 38_cinema;
insert into 38_cinema (id, movie, description, rating) values (1, 'War', 'great 3D', 8.9);
insert into 38_cinema (id, movie, description, rating) values (2, 'Science', 'fiction', 8.5);
insert into 38_cinema (id, movie, description, rating) values (3, 'irish', 'boring', 6.2);
insert into 38_cinema (id, movie, description, rating) values (4, 'Ice song', 'Fantacy', 8.6);
insert into 38_cinema (id, movie, description, rating) values (5, 'House card', 'Interesting', 9.1);
最终SQL:
select
id,
movie,
description,
rating
from
38_cinema
where
mod(id, 2) = 1 and description != 'boring'
order by
rating DESC;
说明:mod 函数求余数
39. 换座位
需求:编写一个 SQL 查询,小美是一所中学的信息科技老师,她有一张 seat 座位表,平时用来储存学生名字和与他们相对应的座位 id。
展示效果:
id | student |
---|---|
1 | Doris |
2 | Abbot |
3 | Green |
4 | Emerson |
5 | Jeames |
Create table If Not Exists 39_seat(id int, student varchar(255));
Truncate table 39_seat;
insert into 39_seat (id, student) values (1, 'Abbot');
insert into 39_seat (id, student) values (2, 'Doris');
insert into 39_seat (id, student) values (3, 'Emerson');
insert into 39_seat (id, student) values (4, 'Green');
insert into 39_seat (id, student) values (5, 'Jeames');
最终SQL:
-- 方法一
select
a.id,
ifnull(b.student,a.student) as student
from
39_seat as a
left join
39_seat as b
on
(a.id%2=1 && a.id=b.id-1) || (a.id%2=0 && a.id=b.id+1)
order by
a.id;
-- 方法二
select
if(id%2=0,id-1,if(id=cnt,id,id+1)) as id,
student
from
(select
count(*) as cnt
from
39_seat
)as a,
39_seat
order by id;
-- 方法三
select
b.id,
a.student
from
39_seat as a,
39_seat as b,
(select
count(*) as cnt
from
39_seat
) as c
where
b.id=1^(a.id-1)+1 || (c.cnt%2 && b.id=c.cnt && a.id=c.cnt);