文章目录
175 组合两个表
表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
这题的难度是简单,就是考了一个表的连接,答:
# Write your MySQL query statement below
select FirstName, LastName, City, State
from
Person as p
left join
Address as a
on
p.personid = a.personid;
176 第二高的薪水
编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
答:
用了ifnull( )这个函数
格式:IFNULL(expression_1,expression_2)
如果expression_1
不为null,则infull函数返回expression_1
;否则返回expression_2
# Write your MySQL query statement below
select IFNULL(
(select distinct salary
from
employee
order by salary desc limit 1,1),
null) as SecondHighestSalary;
177 第N高的薪水
编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary)。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表,n = 2 时,应返回第二高的薪水 200。如果不存在第 n 高的薪水,那么查询应返回 null。
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
这里用到了sql的自定义函数
create funetion 函数名(参数)
return 返回值数据类型
[with {Encryption | Schemabinding }]
[as]
begin
SQL语句(必须有return 变量或值)
End
create funetion SumRes(@sco nvarchar(20))
returns real //返回值类型
as
begin
//例子
declare @sum real //变量
declare @code varchar(11) //变量
set @code = @sco + '%' //赋值
select @sum = sum(result) from LearnResult where scode like @code //查询结果赋值
return @sum
end
//引用/使用函数
select 用户名.函数名 as 字段别名
select dbo.AvgResult('sooo2') as result
本题的思路和上一题差不多,也就是将它设计为函数,用M替代2
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare M int default 0;
set M=N-1;
RETURN (
select IFNULL(
(select distinct salary
from
employee
order by salary desc limit M,1),
null)
);
END
178 分数排名
编写一个 SQL 查询来实现分数排名。
如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
例如,根据上述给定的 Scores 表,你的查询应该返回(按分数从高到低排列):
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
重要提示:对于 MySQL 解决方案,如果要转义用作列名的保留字,可以在关键字之前和之后使用撇号。例如 Rank
题解
select score as Score, dense_rank() over (order by score desc) as 'Rank'
from scores;
SQL中的排序函数
- rank函数
RANK ( ) OVER
( [ PARTITION BY <partition_column> ] ORDER BY <order_by_column> )
- partition by 指定一个列或多个列作为排名的分区依据
- order by 指定一个列或者多个列对每个区的输出数据进行排序
有两人有相同分数,相同排名N,下一人排名跳过重复人数2,排名N+2
-
dense_rank()
相同的分数有相同的排名N,下一人排名N+1 -
row_number()
依次递增,相同分数不同排名 -
ntile(N)
将分数均分为N个等级,依据排名划分等级
如果不能被平均分配到不同个组的时候,比如参数导致有不能被整除的时候。当发生这种情况是那么将不能被整除的行按序放到每一个组内。
分数 | rank | dence_rank | row_number | ntile(4) |
---|---|---|---|---|
50 | 1 | 1 | 1 | 1 |
60 | 2 | 2 | 2 | 1 |
60 | 2 | 2 | 3 | 1 |
70 | 4 | 3 | 4 | 2 |
80 | 5 | 4 | 5 | 2 |
90 | 6 | 5 | 6 | 3 |
90 | 6 | 5 | 7 | 3 |
90 | 6 | 5 | 8 | 4 |
100 | 9 | 6 | 9 | 4 |
180 连续出现的数字
思路,一种是多表连接,但这种方法在连续出现多次的要求下复杂度会变得很多。
select distinct t1.num as ConsecutiveNums
from logs t1 join logs t2
on (t1.id + 1 = t2.id and t1.num = t2.num)
join logs t3 on (t2.id + 1 = t3.id and t2.num = t3.num);
另一种,这种其实我没有想太明白,看一下做法是。
SELECT DISTINCT Num FROM (
SELECT Num,COUNT(1) as SerialCount FROM
(SELECT Id,Num,
row_number() over(order by id) -
ROW_NUMBER() over(partition by Num order by Id) as SerialNumberSubGroup
FROM ContinueNumber) as Sub
GROUP BY Num,SerialNumberSubGroup HAVING COUNT(1) >= 3) as Result
核心思想是,如果一个num是连续出现的,那么他出现的id-它出现的次数是一个定值x
- 假设某树第k次出现时id=i
- 第一种情况,该数连续出现,下一次它出现的id=i+1,为第k+1次出现
- 第二种情况,该数不连续出现,下一次它出现id=i+m(m != 1),为第k+1次出现
- 因此,在连续出现的情况下,一定有 i+n - k+n = … = i - k = x
select id, num, row_number() over( order by id) as nid, row_number() over (partition by num order by id)as prank from continuousnum;
+------+------+-----+-------+
| id | num | nid | prank |
+------+------+-----+-------+
| 1 | 1 | 1 | 1 |
| 2 | 1 | 2 | 2 |
| 3 | 1 | 3 | 3 |
| 5 | 1 | 5 | 4 |
| 4 | 2 | 4 | 1 |
| 6 | 2 | 6 | 2 |
| 7 | 2 | 7 | 3 |
| 8 | 3 | 8 | 1 |
| 9 | 3 | 9 | 2 |
| 10 | 3 | 10 | 3 |
+------+------+-----+-------+
select t.id, t.num, t.nid-t.prank as diff from(
select id, num, row_number() over( order by id) as nid, row_number() over (partition by num order by id)as prank from continuousnum) t;
+------+------+------+
| id | num | diff |
+------+------+------+
| 1 | 1 | 0 |
| 2 | 1 | 0 |
| 3 | 1 | 0 |
| 5 | 1 | 1 |
| 4 | 2 | 3 |
| 6 | 2 | 4 |
| 7 | 2 | 4 |
| 8 | 3 | 7 |
| 9 | 3 | 7 |
| 10 | 3 | 7 |
+------+------+------+
10 rows in set (0.00 sec)
再得到上面的表之后,我们要做的是根据num和diff进行分组,找到出现次数超过三次的num
select distinct num from(
select t2.num, count(*) from(
select t.id, t.num, t.nid-t.prank as diff from(
select id, num, row_number() over( order by id) as nid, row_number() over (partition by num order by id)as prank from continuousnum) t) t2 group by t2.num,diff having count(*) >= 3) as t3;
181 超过经理收入的员工
简单题
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 |
+----------+
select a.name as 'Employee' from employee a
left join employee b
on a.managerid = b.id
where a.salary > b.salary;
182 查找重复的电子邮箱
编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。
示例:
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
根据以上输入,你的查询应返回以下结果:
+---------+
| Email |
+---------+
| a@b.com |
+---------+
说明:所有电子邮箱都是小写字母。
select distinct a.Email from
person a
join
person b
on aEmail = b.Email and a.id != b.id;
183 从不订购的客户
某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
这题写的简单,但我频频摔跤。
Customers 表:
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders 表:
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
例如给定上述表格,你的查询应返回:
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
测试用例中,有order是空的表,有重名的人
select distinct c.name as Customers
from
customers c
join
orders o
on c.id not in (select distinct customerid from orders);
这种题把distinct搞掉就有点离谱
184 部门工资最高的员工
Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询,找出每个部门工资最高的员工。对于上述表,您的 SQL 查询应返回以下行(行的顺序无关紧要)。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
解释:
Max 和 Jim 在 IT 部门的工资都是最高的,Henry 在销售部的工资最高。
按之前我的写法都是
select d.name as Department, e.name as Employee, e.Salary
from
employee e
join
department d
on e.departmentId = d.id
join
(select departmentid, max(salary) as maxsalary from employee group by departmentid) t
on e.salary = t.maxsalary and e.departmentid = t.departmentid;
另一种写法其实可以是
SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM
Employee
JOIN
Department ON Employee.DepartmentId = Department.Id
WHERE
(Employee.DepartmentId , Salary) IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
;
185 部门工资前三的所有员工
Employee 表包含所有员工信息,每个员工有其对应的工号 Id,姓名 Name,工资 Salary 和部门编号 DepartmentId 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询,找出每个部门获得前三高工资的所有员工。例如,根据上述给定的表,查询结果应返回:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
用dense_rank()函数来做
select d.name as Department, t.name as Employee, t.Salary from
(select name, salary, departmentid, dense_rank() over (partition by departmentid order by salary desc) as deptrank from employee) t
join
department d
on t.departmentid = d.id
where t.deptrank <= 3;
不用函数的话,公司前三意味着有不超过3个工资比这些值大
所以我们做一个自连接,算出每个工资有多少更大的值
//
SELECT
d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
Employee e1
JOIN
Department d ON e1.DepartmentId = d.Id
WHERE
3 > (SELECT
COUNT(DISTINCT e2.Salary)
FROM
Employee e2
WHERE
e2.Salary > e1.Salary
AND e1.DepartmentId = e2.DepartmentId
)
;
select d.name as 'Department', e1.name as 'Employee', e1.Salary
from employee e1
where 3 > (
select count( distinct e2.salary) from
employee e2
where e2.salary >= e1.salary and e2.departmentid = e1.departmentid);
196 删除重复的电子邮箱
编写一个 SQL 查询,来删除 Person 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id 是这个表的主键。
例如,在运行你的查询语句之后,上面的 Person 表应返回以下几行:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
提示:
执行 SQL 之后,输出是整个 Person 表。
使用 delete 语句。
delete p1 FROM Person p1,Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id;
197 上升的温度
表 Weather
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| recordDate | date |
| temperature | int |
+---------------+---------+
id 是这个表的主键
该表包含特定日期的温度信息
编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 id 。
返回结果 不要求顺序 。
查询结果格式如下例:
Weather
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+----+------------+-------------+
Result table:
+----+
| id |
+----+
| 2 |
| 4 |
+----+
2015-01-02 的温度比前一天高(10 -> 25)
2015-01-04 的温度比前一天高(20 -> 30)
题解
select w1.id from Weather w1, Weather w2
where DATEDIFF(w1.recordDate, w2.recordDate) = 1 and w1.Temperature > w2.Temperature;
datediff和timestampdiff
datediff(date1, date2)
得到的结果是date1与date2相差的天数。
如果date1比date2大,结果为正;如果date1比date2小,结果为负。
timestampdiff(时间类型, time1, time2)
得到的结果是time1与time2相差的时间类型。
time1大于time2,结果为负,time1小于time2,结果为正。
262 行程和用户
表:Trips
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| Id | int |
| Client_Id | int |
| Driver_Id | int |
| City_Id | int |
| Status | enum |
| Request_at | date |
+-------------+----------+
Id 是这张表的主键。
这张表中存所有出租车的行程信息。每段行程有唯一 Id ,其中 Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。
Status 是一个表示行程状态的枚举类型,枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。
表:Users
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| Users_Id | int |
| Banned | enum |
| Role | enum |
+-------------+----------+
Users_Id 是这张表的主键。
这张表中存所有用户,每个用户都有一个唯一的 Users_Id ,Role 是一个表示用户身份的枚举类型,枚举成员为 (‘client’, ‘driver’, ‘partner’) 。
Banned 是一个表示用户是否被禁止的枚举类型,枚举成员为 (‘Yes’, ‘No’) 。
写一段 SQL 语句查出 “2013-10-01” 至 “2013-10-03” 期间非禁止用户(乘客和司机都必须未被禁止)的取消率。非禁止用户即 Banned 为 No 的用户,禁止用户即 Banned 为 Yes 的用户。
取消率 的计算方式如下:(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。
返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。
查询结果格式如下例所示:
Trips 表:
+----+-----------+-----------+---------+---------------------+------------+
| Id | Client_Id | Driver_Id | City_Id | Status | Request_at |
+----+-----------+-----------+---------+---------------------+------------+
| 1 | 1 | 10 | 1 | completed | 2013-10-01 |
| 2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 |
| 3 | 3 | 12 | 6 | completed | 2013-10-01 |
| 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 |
| 5 | 1 | 10 | 1 | completed | 2013-10-02 |
| 6 | 2 | 11 | 6 | completed | 2013-10-02 |
| 7 | 3 | 12 | 6 | completed | 2013-10-02 |
| 8 | 2 | 12 | 12 | completed | 2013-10-03 |
| 9 | 3 | 10 | 12 | completed | 2013-10-03 |
| 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 |
+----+-----------+-----------+---------+---------------------+------------+
Users 表:
+----------+--------+--------+
| Users_Id | Banned | Role |
+----------+--------+--------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
+----------+--------+--------+
Result 表:
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
2013-10-01:
- 共有 4 条请求,其中 2 条取消。
- 然而,Id=2 的请求是由禁止用户(User_Id=2)发出的,所以计算时应当忽略它。
- 因此,总共有 3 条非禁止请求参与计算,其中 1 条取消。
- 取消率为 (1 / 3) = 0.33
2013-10-02:
- 共有 3 条请求,其中 0 条取消。
- 然而,Id=6 的请求是由禁止用户发出的,所以计算时应当忽略它。
- 因此,总共有 2 条非禁止请求参与计算,其中 0 条取消。
- 取消率为 (0 / 2) = 0.00
2013-10-03:
- 共有 3 条请求,其中 1 条取消。
- 然而,Id=8 的请求是由禁止用户发出的,所以计算时应当忽略它。
- 因此,总共有 2 条非禁止请求参与计算,其中 1 条取消。
- 取消率为 (1 / 2) = 0.50
思路,先筛选出那些没有被banner的订单,然后再用分组函数
select t.Request_at as Day,
Round(sum(if(t.STATUS = 'completed',0,1))/count(t.STATUS),2) as 'Cancellation Rate'
from
(select * from Trips where
Client_Id in (select Users_Id from Users where Banned != 'Yes')
and
Driver_Id in (select Users_Id from Users where Banned != 'Yes')
) as t
group by t.Request_at;
提交,错了
输入:
{"headers": {"Trips": ["Id", "Client_Id", "Driver_Id", "City_Id", "Status", "Request_at"], "Users": ["Users_Id", "Banned", "Role"]}, "rows": {"Trips": [["1", "1", "10", "1", "cancelled_by_client", "2013-10-04"]], "Users": [["1", "No", "client"], ["10", "No", "driver"]]}}
输出:
{"headers": ["Day", "Cancellation Rate"], "values": [["2013-10-04", 1.00]]}
预期结果:
{"headers":["Day","Cancellation Rate"],"values":[]}
仔细看题目才发现原来还有个时间范围!查出 “2013-10-01” 至 “2013-10-03” 期间
select t.Request_at as Day,
Round(sum(if(t.STATUS = 'completed',0,1))/count(t.STATUS),2) as 'Cancellation Rate'
from
(select * from Trips where
Client_Id in (select Users_Id from Users where Banned != 'Yes')
and
Driver_Id in (select Users_Id from Users where Banned != 'Yes')
and
request_at BETWEEN '2013-10-01' AND '2013-10-03'
) as t
group by t.Request_at;
看见一种很厉害的做法,把Round(sum(if(t.STATUS = 'completed',0,1))/count(t.STATUS),2)
用round(avg(Status!='completed'), 2)
来计算。
601 体育馆的人流量
表:Stadium
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| visit_date | date |
| people | int |
+---------------+---------+
visit_date 是表的主键
每日人流量信息被记录在这三列信息中:序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录,日期随着 id 的增加而增加
编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。
返回按 visit_date 升序排列的结果表。
查询结果格式如下所示。
Stadium table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
Result table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
id 为 5、6、7、8 的四行 id 连续,并且每行都有 >= 100 的人数记录。
请注意,即使第 7 行和第 8 行的 visit_date 不是连续的,输出也应当包含第 8 行,因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行,因为至少需要三条 id 连续的记录。
连续问题,仍然考虑id-row_number的排名会是一个定值,如下所示,我们需要的是5,6,7,8这四条数据,我们可以发现他们的diff为2,出现了四次。
select *, row_number() over (order by id),(id - row_number() over (order by id) ) as diff from stadium where people >= 100;
+------+------------+--------+---------------------------------+------+
| id | visit_date | people | row_number() over (order by id) | diff |
+------+------------+--------+---------------------------------+------+
| 2 | 2017-01-02 | 109 | 1 | 1 |
| 3 | 2017-01-03 | 150 | 2 | 1 |
| 5 | 2017-01-05 | 145 | 3 | 2 |
| 6 | 2017-01-06 | 1455 | 4 | 2 |
| 7 | 2017-01-07 | 199 | 5 | 2 |
| 8 | 2017-01-09 | 188 | 6 | 2 |
+------+------------+--------+---------------------------------+------+
6 rows in set (0.00 sec)
下一步需要找出diff值出现了三次及以上的
select diff from t group by diff having count(diff) > 2;
组装
with t
as
( select *,(id - row_number() over (order by id) ) as diff from stadium where people >= 100 )
select id, visit_date, people from t
where diff in (select diff from t group by diff having count(diff) >2);
+------+------------+--------+
| id | visit_date | people |
+------+------------+--------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+--------+
4 rows in set (0.00 sec)
626 换座位
小美是一所中学的信息科技老师,她有一张 seat 座位表,平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢?
示例:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如数据输入的是上表,则输出结果如下:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
注意:
如果学生人数是奇数,则不需要改变最后一个同学的座位。
题解
select
(case
when mod(id,2) = 0 then id -1
when mod(id,2) <> 0 and id <> (select count(id) from seat) then id + 1
else id end) as id,
student from seat order by id;
627 变更性别
给定一个 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 = if(sex='m','f','m');
有一个离谱的答案
update salary set sex = char(ascii('m') + ascii('f') - ascii(sex));
1179 重新格式化部门
部门表 Department:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| revenue | int |
| month | varchar |
+---------------+---------+
(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份(month)可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。
编写一个 SQL 查询来重新格式化表,使得新的表中有一个部门 id 列和一些对应 每个月 的收入(revenue)列。
查询结果格式如下面的示例所示:
Department 表:
+------+---------+-------+
| id | revenue | month |
+------+---------+-------+
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 3 | 10000 | Feb |
| 1 | 7000 | Feb |
| 1 | 6000 | Mar |
+------+---------+-------+
查询得到的结果表:
+------+-------------+-------------+-------------+-----+-------------+
| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1 | 8000 | 7000 | 6000 | ... | null |
| 2 | 9000 | null | null | ... | null |
| 3 | null | 10000 | null | ... | null |
+------+-------------+-------------+-------------+-----+-------------+
注意,结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。
# Write your MySQL query statement below
select id,
sum(case when month ='Jan' then revenue end) as 'Jan_Revenue',
sum(case when month ='Feb' then revenue end) as 'Feb_Revenue',
sum(case when month ='Mar' then revenue end) as 'Mar_Revenue',
sum(case when month ='Apr' then revenue end) as 'Apr_Revenue',
sum(case when month ='May' then revenue end) as 'May_Revenue',
sum(case when month ='Jun' then revenue end) as 'Jun_Revenue',
sum(case when month ='Jul' then revenue end) as 'Jul_Revenue',
sum(case when month ='Aug' then revenue end) as 'Aug_Revenue',
sum(case when month ='Sep' then revenue end) as 'Sep_Revenue',
sum(case when month ='Oct' then revenue end) as 'Oct_Revenue',
sum(case when month ='Nov' then revenue end) as 'Nov_Revenue',
sum(case when month ='Dec' then revenue end) as 'Dec_Revenue'
from department
group by id
order by id;