侧边栏壁纸
  • 累计撰写 79 篇文章
  • 累计创建 7 个标签
  • 累计收到 0 条评论

mysql题目

水龙吟
2022-02-07 / 0 评论 / 0 点赞 / 256 阅读 / 10,525 字
温馨提示:
本文最后更新于 2022-02-08,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

join连接条件可写多个
in可以多个字段同时in
a.request_at BETWEEN '2013-10-01' AND '2013-10-03'
DATEDIFF(weather.date, w.date) = 1

count()over(partition by t_rank)

select count(
) over(partition by col1) from tmp_test;
类似于
select count(1) from tmp_test group by col1;
只不过每一行都展示分组的总数

MySQL 中判断奇数的 6 种方法:

mod(x, 2) = 1 ,如果余数是 1 就是奇数。
power(-1, x) = -1 , 如果结果是 -1 就是奇数
x % 2 = 1 ,如果余数是 1 就是奇数。
x & 1 = 1 ,如果是 1 就是奇数
x regexp '[1, 3, 5, 7, 9]$' = 1 如果为 1 就是奇数
x>>1<<1 != x 如果右移一位在左移一位不等于原值,就是奇数

select class from courses group by class having count(distinct student) > 4

1、第二高的薪水

Employee 表:
+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id 是这个表的主键。
表的每一行包含员工的工资信息。

编写一个 SQL 查询,获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水,查询应该返回 null 。

查询结果如下例所示。

 

示例 1:

输入:
Employee 表:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
输出:
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

知识点

LIMIT 3,1 表示跳过3条取一条
LIMIT 1 OFFSET 3 表示跳过3条取一条
SELECT * FROM 表名 LIMIT 3,1;          #从第4条数据开始取数,取1条数据,即只取第四条
SELECT * FROM 表名 LIMIT 1 OFFSET 3;   #从第4条数据开始取数,取1条数据,即只取第四条
SELECT * FROM 表名 LIMIT 3,2;          #从第4条数据开始取数,取2条数据,即取第4条,第5条
SELECT * FROM 表名 LIMIT 2 OFFSET 3;   #从第4条数据开始取数,取2条数据,即取第4条,第5条



IFNULL() 函数用于判断第一个表达式是否为 NULL,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值
IFNULL() 函数语法格式为:
IFNULL(expression, alt_value)

解法

方法一:使用子查询和 LIMIT 子句
算法

将不同的薪资按降序排序,然后使用 LIMIT 子句获得第二高的薪资。
SELECT DISTINCT
    Salary AS SecondHighestSalary
FROM
    Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1

然而,如果没有这样的第二最高工资,这个解决方案将被判断为 “错误答案”,因为本表可能只有一项记录。为了克服这个问题,我们可以将其作为临时表。

MySQL

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
;
方法二:使用 IFNULL 和 LIMIT 子句
解决 “NULL” 问题的另一种方法是使用 “IFNULL” 函数,如下所示。
SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

方法三:小于最大的最大值
SELECT
	max( DISTINCT Salary ) 
FROM
	employee 
WHERE
	Salary < ( SELECT max( DISTINCT Salary ) FROM employee );

编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary)。

+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表,n = 2 时,应返回第二高的薪水 200。如果不存在第 n 高的薪水,那么查询应返回 null。

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+

注意重复的值(distinct、Group by可解决)
CREATE FUNCTION getNthHighestSalary ( N INT ) RETURNS INT BEGIN
DECLARE
s_count INT;
DECLARE
rtnsalary INT;

SET N = N - 1;
SELECT
	IFNULL( salary, NULL ) INTO rtnsalary 
FROM
	employee 
	group by salary
ORDER BY
	salary 
	LIMIT N,1;
	RETURN rtnsalary;

END;

用窗口函数也可
SELECT
DISTINCT salary
FROM
(SELECT
salary, dense_rank() over(ORDER BY salary DESC) AS rnk
FROM
employee) tmp
WHERE rnk = N

超过经理收入的员工

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
	JOIN employee b ON a.ManagerId = b.id
	where a.salary>b.salary

join会把a表和b表关联到的部分拿出来
所以 a.salary>b.salary

部门工资最高的员工

注意有重复第一

表: Employee

+--------------+---------+
| 列名          | 类型    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id是此表的主键列。
departmentId是Department表中ID的外键。
此表的每一行都表示员工的ID、姓名和工资。它还包含他们所在部门的ID。
 

表: Department

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id是此表的主键列。
此表的每一行都表示一个部门的ID及其名称。
 

编写SQL查询以查找每个部门中薪资最高的员工。
按 任意顺序 返回结果表。
查询结果格式如下例所示。

 

示例 1:

输入:
Employee 表:
+----+-------+--------+--------------+
| 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 |
+----+-------+
输出:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
| IT         | Max      | 90000  |
+------------+----------+--------+
解释:Max 和 Jim 在 IT 部门的工资都是最高的,Henry 在销售部的工资最高。


SELECT
	Department,
	Employee,
	salary 
FROM
	(
	SELECT
		a.NAME AS Employee,
		a.salary,
		b.NAME AS Department,

        dense_rank() over(partition by b.Name order by a.Salary desc) as rn
        frOm Employee a
		JOIN Department b ON a.departmentId = b.id 
	) AS tmp 
WHERE
	rn = 1


dense_rank() 和rank()如果有重复的会并列



解法二
先找出部门最大的值
select departmentid,max(salary) from employee GROUP BY departmentid
然后双重属性 in

SELECT
	a.NAME AS Employee,
	a.salary,
	b.NAME AS Department 
FROM
	employee a
	JOIN Department b ON a.departmentId = b.id
	where (departmentid,salary) in (select departmentid,max(salary) from employee GROUP BY departmentid)

部门工资前三

select Department,Employee,Salary from (
SELECT
b.name as Department ,
	a.name as Employee ,
	a.Salary,
	dense_rank()over (partition by a.departmentid order by a.salary desc) rn
FROM
	employee a
	JOIN Department b ON a.departmentid = b.id )tmp 
	where rn < 4

找到比前一天高的温度

关键在于日期的比较
MySQL 使用 DATEDIFF 计算两日期差
表 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
    weather.id AS 'Id'
FROM
    weather
        JOIN
    weather w ON DATEDIFF(weather.date, w.date) = 1
        AND weather.Temperature > w.Temperature


行程与用户

表: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 |
+----------+--------+--------+
输出:
+------------+-------------------+
| 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



一开始
SELECT
	a.request_at AS `Day`,
	round( SUM( IF ( a.STATUS = 'completed', 0, 1 ))/ COUNT( a.STATUS ), 2 ) AS `Cancellation Rate` 
FROM
	trips a
	JOIN users b ON a.client_id = b.users_id
	JOIN users c ON a.driver_id = c.users_id 
WHERE
	a.request_at BETWEEN '2013-10-01' 
	AND '2013-10-03' 
	AND b.banned = 'No' 
	AND c.banned = 'No' 
GROUP BY
	a.request_at

一直没有数据,后边发现AND b.banned = 'No' 	
AND c.banned = 'No' 两个and连一起这样是不会有数据的


改
SELECT
	a.request_at AS `Day`,
	round( SUM( IF ( a.STATUS = 'completed', 0, 1 ))/ COUNT( a.STATUS ), 2 ) AS `Cancellation Rate` 
FROM
	trips a
	JOIN users b ON ( a.client_id = b.users_id AND b.banned = 'No' )
	JOIN users c ON ( a.driver_id = c.users_id AND c.banned = 'No' ) 
WHERE
	a.request_at BETWEEN '2013-10-01' 
	AND '2013-10-03' 
GROUP BY
	a.request_at



超过5名学生的课

有一个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    |
+---------+
 

提示:

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

select class from courses  group by class having count(1) > 4
一开始这样写,没有考虑重复学生

select class from courses  group by class having count(distinct student) > 4


体育馆的人流量

表: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 连续的记录。

select id,visit_date,people
from(
    select *,count(*)over(partition by t_rank) t2_rank
    from(
        select *,id-row_number()over(order by id) t_rank
        from stadium  
        where people>99
        )t
    )t2
where t2.t2_rank>2
order by id;

换座位

小美是一所中学的信息科技老师,她有一张 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  |
+---------+---------+
注意:

如果学生人数是奇数,则不需要改变最后一个同学的座位。



对于所有座位 id 是奇数的学生,修改其 id 为 id+1,如果最后一个座位 id 也是奇数,则最后一个座位 id 不修改。对于所有座位 id 是偶数的学生,修改其 id 为 id-1


SELECT
    (CASE
        WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
        WHEN MOD(id, 2) != 0 AND counts = id THEN id
        ELSE id - 1
    END) AS id,
    student
FROM
    seat,
    (SELECT
        COUNT(*) AS counts
    FROM
        seat) AS seat_counts
ORDER BY id ASC;

请你编写一个 SQL 查询来交换所有的 'f' 和 'm' (即,将所有 'f' 变为 'm' ,反之亦然),仅使用 单个 update 语句 ,且不产生中间临时表。




UPDATE salary
SET
    sex = CASE sex
        WHEN 'm' THEN 'f'
        ELSE 'm'
    END;



0

评论区