专门做配电箱的网站,河南第一火电建设公司网站,权重高的博客网站,全国大型网站建设目录
197、上升的温度
577、员工奖金
586、订单最多的客户 596、超过5名学生的课 610、判断三角形 620、有趣的电影
181、超过经理收入的员工 1179、重新格式化部门表#xff08;行转列#xff09; 1280、学生参加各科测试的次数
1068、产品销售分析I 1075、项目员工I
…目录
197、上升的温度
577、员工奖金
586、订单最多的客户 596、超过5名学生的课 610、判断三角形 620、有趣的电影
181、超过经理收入的员工 1179、重新格式化部门表行转列 1280、学生参加各科测试的次数
1068、产品销售分析I 1075、项目员工I
1084、销售分析III
1327、列出指定时间段内所有的下单产品
1378、使用唯一标识码替换员工ID
1517、查找拥有有效邮箱的用户 1661、每台机器的进程平均运行时间
1683、无效的推文 1693、每天的领导和合伙人 1731、每位经理的下属员工数量 1741、查找每个员工花费的总时间 1789、员工的直属部门
1795、每个产品在不同商店的价格列转行
1873、计算特殊奖金 1890、2020年最后一次登录 参考文章http://t.csdnimg.cn/1kGVX 题目来源力扣 题库 - 力扣 (LeetCode) 全球极客挚爱的技术成长平台 题型 行转列一般使用 IF 或 CASE WHEN 语句 GROUP BY 聚合函数。列转行一般使用 UNION 多个查询 的方法 197、上升的温度 表 Weather ------------------------
| Column Name | Type |
------------------------
| id | int |
| recordDate | date |
| temperature | int |
------------------------
id 是该表具有唯一值的列。
没有具有相同 recordDate 的不同行。
该表包含特定日期的温度信息编写解决方案找出与之前昨天的日期相比温度更高的所有日期的 id 。 返回结果 无顺序要求 。 结果格式如下例子所示。 示例 1 输入
Weather 表
-----------------------------
| id | recordDate | Temperature |
-----------------------------
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
-----------------------------
输出
----
| id |
----
| 2 |
| 4 |
----
解释
2015-01-02 的温度比前一天高10 - 25
2015-01-04 的温度比前一天高20 - 30 DATEDIFF()函数是前一个日期到后一个日期的时间此处是相差1天即为昨天 SELECT w_next.id
FROM Weather w,Weather w_next
WHERE DATEDIFF(w_next.recordDate,w.recordDate) 1
AND w_next.Temperature w.Temperature;
577、员工奖金 表Employee ----------------------
| Column Name | Type |
----------------------
| empId | int |
| name | varchar |
| supervisor | int |
| salary | int |
----------------------
empId 是该表中具有唯一值的列。
该表的每一行都表示员工的姓名和 id以及他们的工资和经理的 id。表Bonus -------------------
| Column Name | Type |
-------------------
| empId | int |
| bonus | int |
-------------------
empId 是该表具有唯一值的列。
empId 是 Employee 表中 empId 的外键(reference 列)。
该表的每一行都包含一个员工的 id 和他们各自的奖金。编写解决方案报告每个奖金 少于 1000 的员工的姓名和奖金数额。 以 任意顺序 返回结果表。 结果格式如下所示。 示例 1 输入
Employee table:
-----------------------------------
| empId | name | supervisor | salary |
-----------------------------------
| 3 | Brad | null | 4000 |
| 1 | John | 3 | 1000 |
| 2 | Dan | 3 | 2000 |
| 4 | Thomas | 3 | 4000 |
-----------------------------------
Bonus table:
--------------
| empId | bonus |
--------------
| 2 | 500 |
| 4 | 2000 |
--------------
输出
-------------
| name | bonus |
-------------
| Brad | null |
| John | null |
| Dan | 500 |
------------- 分析 LEFT JOIN 左连接返回左表的所有行而不仅仅是与右表匹配的行 与右表匹配的条件为ID相等而左表再加一个选择条件奖金小于1000否则左表全部行都输出会包括奖金大于1000的数据 SELECT e.name, b.bonus
FROM Employee eLEFT JOIN Bonus b ON e.empId b.empId
WHERE b.bonus 1000 OR b.bonus IS NULL;586、订单最多的客户 表: Orders ---------------------------
| Column Name | Type |
---------------------------
| order_number | int |
| customer_number | int |
---------------------------
在 SQL 中Order_number是该表的主键。
此表包含关于订单ID和客户ID的信息。查找下了 最多订单 的客户的 customer_number 。 测试用例生成后 恰好有一个客户 比任何其他客户下了更多的订单。 查询结果格式如下所示。 示例 1: 输入:
Orders 表:
-------------------------------
| order_number | customer_number |
-------------------------------
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 3 |
-------------------------------
输出:
-----------------
| customer_number |
-----------------
| 3 |
-----------------
解释:
customer_number 为 3 的顾客有两个订单比顾客 1 或者 2 都要多因为他们只有一个订单。
所以结果是该顾客的 customer_number 也就是 3 。 分析 按customer_number分组统计订单数量再降序排序 只返回最上面一行的customer_number 即为订单数量最多的customer_number SELECT customer_number
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1;596、超过5名学生的课 表: Courses ----------------------
| Column Name | Type |
----------------------
| student | varchar |
| class | varchar |
----------------------
在 SQL 中(student, class)是该表的主键列。
该表的每一行表示学生的名字和他们注册的班级。查询 至少有5个学生 的所有班级。 以 任意顺序 返回结果表。 查询结果格式如下所示。 示例 1: 输入:
Courses table:
-------------------
| student | class |
-------------------
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
-------------------
输出:
---------
| class |
---------
| Math |
---------
解释:
-数学课有6个学生所以我们包括它。
-英语课有1名学生所以我们不包括它。
-生物课有1名学生所以我们不包括它。
-计算机课有1个学生所以我们不包括它。 分析 按class分组统计学生数量使用HAVING选择要输出的学生数量大于等于5的组并输出该组的class # Write your MySQL query statement below
SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(*) 5; 610、判断三角形 表: Triangle -------------------
| Column Name | Type |
-------------------
| x | int |
| y | int |
| z | int |
-------------------
在 SQL 中(x, y, z)是该表的主键列。
该表的每一行包含三个线段的长度。对每三个线段报告它们是否可以形成一个三角形。 以 任意顺序 返回结果表。 查询结果格式如下所示。 示例 1: 输入:
Triangle 表:
------------
| x | y | z |
------------
| 13 | 15 | 30 |
| 10 | 20 | 15 |
------------
输出:
----------------------
| x | y | z | triangle |
----------------------
| 13 | 15 | 30 | No |
| 10 | 20 | 15 | Yes |
---------------------- 分析 任意两边长度的和大于第三边任意两边之差小于第三边即可形成三角形把加法的某一项移到等号另一边就可以推出两边之差小于第三边所以只需要写加法即可使用条件判断函数IF() 函数如果xyz AND xzy AND yzx 为 true就输出“Yes”否则“No” SELECT x,y,z,IF(xyz AND xzy AND yzx,Yes,No) AS triangle
FROM Triangle; 620、有趣的电影 表cinema --------------------------
| Column Name | Type |
--------------------------
| id | int |
| movie | varchar |
| description | varchar |
| rating | float |
--------------------------
id 是该表的主键(具有唯一值的列)。
每行包含有关电影名称、类型和评级的信息。
评级为 [0,10] 范围内的小数点后 2 位浮点数。编写解决方案找出所有影片描述为 非 boring (不无聊) 的并且 id 为奇数 的影片。 返回结果按 rating 降序排列。 结果格式如下示例。 示例 1 输入
---------------------------------------------
| 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 |
---------------------------------------------
解释
我们有三部电影它们的 id 是奇数:1、3 和 5。id 3 的电影是 boring 的所以我们不把它包括在答案中。 分析 首先筛选出id为奇数并且使用 LIKE 模糊查询 description不等于boring的记录然后按照rating降序排列结果 SELECT *
FROM cinema
WHERE (id%2)1 AND description NOT LIKE boring
ORDER BY rating DESC;
181、超过经理收入的员工 表Employee ----------------------
| Column Name | Type |
----------------------
| id | int |
| name | varchar |
| salary | int |
| managerId | int |
----------------------
id 是该表的主键具有唯一值的列。
该表的每一行都表示雇员的ID、姓名、工资和经理的ID。编写解决方案找出收入比经理高的员工。 以 任意顺序 返回结果表。 结果格式如下所示。 示例 1: 输入:
Employee 表:
------------------------------
| id | name | salary | managerId |
------------------------------
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | Null |
| 4 | Max | 90000 | Null |
------------------------------
输出:
----------
| Employee |
----------
| Joe |
----------
解释: Joe 是唯一挣得比经理多的雇员。 分析 自连接同样是Employee表假装成两个分别名为 e 和 m 的表分别代表员工和经理连接两个表的关键点在于员工表的经理ID 经理表的ID SELECT e.name AS Employee
FROM Employee e,Employee m
WHERE e.managerId m.id AND e.salary m.salary 1179、重新格式化部门表行转列 表 Department ------------------------
| Column Name | Type |
------------------------
| id | int |
| revenue | int |
| month | varchar |
------------------------
在 SQL 中(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份month可以取下列值 [Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec]。重新格式化表格使得 每个月 都有一个部门 id 列和一个收入列。 以 任意顺序 返回结果表。 结果格式如以下示例所示。 示例 1 输入
Department table:
----------------------
| 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 列用于各个月份。 分析 先对id进行分组再处理组内的数据然后使用CASE()条件判断函数如果month Jan就返回revenue否则返回NULLid分组后又提取到了某个 id 内的 Jan 月份的revenue数据但可能 Jan 月份内有不止一个revenue数据所以用MAX() 函数确保每个部门在每个月份的收入值只会出现一次 SELECT id,MAX(CASE WHEN month Jan THEN revenue ELSE NULL END) AS Jan_Revenue,MAX(CASE WHEN month Feb THEN revenue ELSE NULL END) AS Feb_Revenue,MAX(CASE WHEN month Mar THEN revenue ELSE NULL END) AS Mar_Revenue,MAX(CASE WHEN month Apr THEN revenue ELSE NULL END) AS Apr_Revenue,MAX(CASE WHEN month May THEN revenue ELSE NULL END) AS May_Revenue,MAX(CASE WHEN month Jun THEN revenue ELSE NULL END) AS Jun_Revenue,MAX(CASE WHEN month Jul THEN revenue ELSE NULL END) AS Jul_Revenue,MAX(CASE WHEN month Aug THEN revenue ELSE NULL END) AS Aug_Revenue,MAX(CASE WHEN month Sep THEN revenue ELSE NULL END) AS Sep_Revenue,MAX(CASE WHEN month Oct THEN revenue ELSE NULL END) AS Oct_Revenue,MAX(CASE WHEN month Nov THEN revenue ELSE NULL END) AS Nov_Revenue,MAX(CASE WHEN month Dec THEN revenue ELSE NULL END) AS Dec_Revenue
FROM Department
GROUP BY id;1280、学生参加各科测试的次数 学生表: Students ------------------------
| Column Name | Type |
------------------------
| student_id | int |
| student_name | varchar |
------------------------
在 SQL 中主键为 student_id学生ID。
该表内的每一行都记录有学校一名学生的信息。科目表: Subjects -----------------------
| Column Name | Type |
-----------------------
| subject_name | varchar |
-----------------------
在 SQL 中主键为 subject_name科目名称。
每一行记录学校的一门科目名称。考试表: Examinations -----------------------
| Column Name | Type |
-----------------------
| student_id | int |
| subject_name | varchar |
-----------------------
这个表可能包含重复数据换句话说在 SQL 中这个表没有主键。
学生表里的一个学生修读科目表里的每一门科目。
这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。查询出每个学生参加每一门科目测试的次数结果按 student_id 和 subject_name 排序。 查询结构格式如下所示。 示例 1 输入
Students table:
--------------------------
| student_id | student_name |
--------------------------
| 1 | Alice |
| 2 | Bob |
| 13 | John |
| 6 | Alex |
--------------------------
Subjects table:
--------------
| subject_name |
--------------
| Math |
| Physics |
| Programming |
--------------
Examinations table:
--------------------------
| student_id | subject_name |
--------------------------
| 1 | Math |
| 1 | Physics |
| 1 | Programming |
| 2 | Programming |
| 1 | Physics |
| 1 | Math |
| 13 | Math |
| 13 | Programming |
| 13 | Physics |
| 2 | Math |
| 1 | Math |
--------------------------
输出
--------------------------------------------------------
| student_id | student_name | subject_name | attended_exams |
--------------------------------------------------------
| 1 | Alice | Math | 3 |
| 1 | Alice | Physics | 2 |
| 1 | Alice | Programming | 1 |
| 2 | Bob | Math | 1 |
| 2 | Bob | Physics | 0 |
| 2 | Bob | Programming | 1 |
| 6 | Alex | Math | 0 |
| 6 | Alex | Physics | 0 |
| 6 | Alex | Programming | 0 |
| 13 | John | Math | 1 |
| 13 | John | Physics | 1 |
| 13 | John | Programming | 1 |
--------------------------------------------------------
解释
结果表需包含所有学生和所有科目即便测试次数为0
Alice 参加了 3 次数学测试, 2 次物理测试以及 1 次编程测试
Bob 参加了 1 次数学测试, 1 次编程测试没有参加物理测试
Alex 啥测试都没参加
John 参加了数学、物理、编程测试各 1 次。 分析 使用 CROSS JOIN 笛卡尔积可以获取所有可能的学生和科目的组合将考试表与学生和科目的组合进行左连接即会输出考试表的所有行包括符合 ON 后面的连接条件的行GROUP BY配合COUNT进行每个ID的学生的每个科目的测试数量统计把结果先按ID排序再按名字排序 SELECT sd.student_id, sd.student_name, sj.subject_name, COUNT(e.student_id) AS attended_exams
FROM Students sd
CROSS JOIN Subjects sj
LEFT JOIN Examinations e ON sd.student_id e.student_id AND sj.subject_name e.subject_name
GROUP BY sd.student_id, sj.subject_name
ORDER BY sd.student_id, sj.subject_name;1068、产品销售分析I 销售表 Sales --------------------
| Column Name | Type |
--------------------
| sale_id | int |
| product_id | int |
| year | int |
| quantity | int |
| price | int |
--------------------
(sale_id, year) 是销售表 Sales 的主键具有唯一值的列的组合。
product_id 是关联到产品表 Product 的外键reference 列。
该表的每一行显示 product_id 在某一年的销售情况。
注意: price 表示每单位价格。产品表 Product -----------------------
| Column Name | Type |
-----------------------
| product_id | int |
| product_name | varchar |
-----------------------
product_id 是表的主键具有唯一值的列。
该表的每一行表示每种产品的产品名称。编写解决方案以获取 Sales 表中所有 sale_id 对应的 product_name 以及该产品的所有 year 和 price 。 返回结果表 无顺序要求 。 结果格式示例如下。 示例 1 输入
Sales 表
--------------------------------------------
| sale_id | product_id | year | quantity | price |
--------------------------------------------
| 1 | 100 | 2008 | 10 | 5000 |
| 2 | 100 | 2009 | 12 | 5000 |
| 7 | 200 | 2011 | 15 | 9000 |
--------------------------------------------
Product 表
--------------------------
| product_id | product_name |
--------------------------
| 100 | Nokia |
| 200 | Apple |
| 300 | Samsung |
--------------------------
输出
----------------------------
| product_name | year | price |
----------------------------
| Nokia | 2008 | 5000 |
| Nokia | 2009 | 5000 |
| Apple | 2011 | 9000 |
---------------------------- 分析 两张表做关联s 表的外键关联 p 表的主键第一行中要查询的表项要标明是哪个表的 SELECT p.product_name,s.year,s.price
FROM Sales s,Product p
WHERE s.product_idp.product_id 1075、项目员工I 项目表 Project ----------------------
| Column Name | Type |
----------------------
| project_id | int |
| employee_id | int |
----------------------
主键为 (project_id, employee_id)。
employee_id 是员工表 Employee 表的外键。
这张表的每一行表示 employee_id 的员工正在 project_id 的项目上工作。员工表 Employee ---------------------------
| Column Name | Type |
---------------------------
| employee_id | int |
| name | varchar |
| experience_years | int |
---------------------------
主键是 employee_id。数据保证 experience_years 非空。
这张表的每一行包含一个员工的信息。 请写一个 SQL 语句查询每一个项目中员工的 平均 工作年限精确到小数点后两位。 以 任意 顺序返回结果表。 查询结果的格式如下。 示例 1: 输入
Project 表
--------------------------
| project_id | employee_id |
--------------------------
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
--------------------------Employee 表
---------------------------------------
| employee_id | name | experience_years |
---------------------------------------
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 1 |
| 4 | Doe | 2 |
---------------------------------------输出
----------------------------
| project_id | average_years |
----------------------------
| 1 | 2.00 |
| 2 | 2.50 |
----------------------------
解释第一个项目中员工的平均工作年限是 (3 2 1) / 3 2.00第二个项目中员工的平均工作年限是 (3 2) / 2 2.50 分析 使用ROUND()函数 SELECT ROUND(AVG(amount), 2) FROM sales; 这将返回amount列的平均值并将结果四舍五入到小数点后两位。 SELECT p.project_id,ROUND(AVG(e.experience_years),2) AS average_years
FROM Project p,Employee e
WHERE p.employee_id e.employee_id
GROUP BY p.project_id
1084、销售分析III 表 Product -----------------------
| Column Name | Type |
-----------------------
| product_id | int |
| product_name | varchar |
| unit_price | int |
-----------------------
product_id 是该表的主键具有唯一值的列。
该表的每一行显示每个产品的名称和价格。表Sales ----------------------
| Column Name | Type |
----------------------
| seller_id | int |
| product_id | int |
| buyer_id | int |
| sale_date | date |
| quantity | int |
| price | int |
------ ---------------
这个表可能有重复的行。
product_id 是 Product 表的外键reference 列。
该表的每一行包含关于一个销售的一些信息。编写解决方案报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31含之间出售的商品。 以 任意顺序 返回结果表。 结果格式如下所示。 示例 1: 输入
Product table:
--------------------------------------
| product_id | product_name | unit_price |
--------------------------------------
| 1 | S8 | 1000 |
| 2 | G4 | 800 |
| 3 | iPhone | 1400 |
--------------------------------------
Sales table:
--------------------------------------------------------------
| seller_id | product_id | buyer_id | sale_date | quantity | price |
--------------------------------------------------------------
| 1 | 1 | 1 | 2019-01-21 | 2 | 2000 |
| 1 | 2 | 2 | 2019-02-17 | 1 | 800 |
| 2 | 2 | 3 | 2019-06-02 | 1 | 800 |
| 3 | 3 | 4 | 2019-05-13 | 2 | 2800 |
--------------------------------------------------------------
输出
---------------------------
| product_id | product_name |
---------------------------
| 1 | S8 |
---------------------------
解释:
id 为 1 的产品仅在 2019 年春季销售。
id 为 2 的产品在 2019 年春季销售但也在 2019 年春季之后销售。
id 为 3 的产品在 2019 年春季之后销售。
我们只返回 id 为 1 的产品因为它是 2019 年春季才销售的产品。 分析 使用一个子查询子查询的结果为不符合要求的product_id。 然后在外查询时如果遍历到的product_id在子查询范围的结果内就舍弃 但是可能有多个相同的product_id都符合要求所以要加DISTINCT关键字去重 另外外查询的product_id要用 s.product_id 如果使用 p.product_id 它可能还未出售也就是不在 Sales 表中但也会符合要求导致出错 SELECT DISTINCT p.product_id, p.product_name
FROM Product p,Sales s
WHERE p.product_id s.product_idAND s.product_id NOT IN(SELECT product_idFROM SalesWHERE sale_date 2019-01-01 OR sale_date 2019-03-31
)
1327、列出指定时间段内所有的下单产品 表: Products ---------------------------
| Column Name | Type |
---------------------------
| product_id | int |
| product_name | varchar |
| product_category | varchar |
---------------------------
product_id 是该表主键(具有唯一值的列)。
该表包含该公司产品的数据。表: Orders ------------------------
| Column Name | Type |
------------------------
| product_id | int |
| order_date | date |
| unit | int |
------------------------
该表可能包含重复行。
product_id 是表单 Products 的外键reference 列。
unit 是在日期 order_date 内下单产品的数目。写一个解决方案要求获取在 2020 年 2 月份下单的数量不少于 100 的产品的名字和数目。 返回结果表单的 顺序无要求 。 查询结果的格式如下。 示例 1: 输入
Products 表:
------------------------------------------------------
| product_id | product_name | product_category |
------------------------------------------------------
| 1 | Leetcode Solutions | Book |
| 2 | Jewels of Stringology | Book |
| 3 | HP | Laptop |
| 4 | Lenovo | Laptop |
| 5 | Leetcode Kit | T-shirt |
------------------------------------------------------
Orders 表:
--------------------------------------
| product_id | order_date | unit |
--------------------------------------
| 1 | 2020-02-05 | 60 |
| 1 | 2020-02-10 | 70 |
| 2 | 2020-01-18 | 30 |
| 2 | 2020-02-11 | 80 |
| 3 | 2020-02-17 | 2 |
| 3 | 2020-02-24 | 3 |
| 4 | 2020-03-01 | 20 |
| 4 | 2020-03-04 | 30 |
| 4 | 2020-03-04 | 60 |
| 5 | 2020-02-25 | 50 |
| 5 | 2020-02-27 | 50 |
| 5 | 2020-03-01 | 50 |
--------------------------------------
输出
-----------------------------
| product_name | unit |
-----------------------------
| Leetcode Solutions | 130 |
| Leetcode Kit | 100 |
-----------------------------
解释
2020 年 2 月份下单 product_id 1 的产品的数目总和为 (60 70) 130 。
2020 年 2 月份下单 product_id 2 的产品的数目总和为 80 。
2020 年 2 月份下单 product_id 3 的产品的数目总和为 (2 3) 5 。
2020 年 2 月份 product_id 4 的产品并没有下单。
2020 年 2 月份下单 product_id 5 的产品的数目总和为 (50 50) 100 。 分析 我们使用了Products表和Orders表并使用JOIN关键字将它们连接在一起。通过p.product_id o.product_id指定了两个表之间的连接条件 这个查询中使用的 JOIN 关键字指定的连接是内连接。内连接会返回两个表中满足连接条件的行 2020年2月份有29天因为2020年是闰年闰年二月份有29天。 使用HAVING子句筛选满足特定条件的分组 SELECT p.product_name, SUM(o.unit) AS unit
FROM Products p
JOIN Orders o ON p.product_id o.product_id
WHERE o.order_date BETWEEN 2020-02-01 AND 2020-02-29
GROUP BY o.product_id
HAVING unit 100;1378、使用唯一标识码替换员工ID Employees 表 ------------------------
| Column Name | Type |
------------------------
| id | int |
| name | varchar |
------------------------
在 SQL 中id 是这张表的主键。
这张表的每一行分别代表了某公司其中一位员工的名字和 ID 。EmployeeUNI 表 ------------------------
| Column Name | Type |
------------------------
| id | int |
| unique_id | int |
------------------------
在 SQL 中(id, unique_id) 是这张表的主键。
这张表的每一行包含了该公司某位员工的 ID 和他的唯一标识码unique ID。展示每位用户的 唯一标识码unique ID 如果某位员工没有唯一标识码使用 null 填充即可。 你可以以 任意 顺序返回结果表。 返回结果的格式如下例所示。 示例 1 输入
Employees 表:
--------------
| id | name |
--------------
| 1 | Alice |
| 7 | Bob |
| 11 | Meir |
| 90 | Winston |
| 3 | Jonathan |
--------------
EmployeeUNI 表:
---------------
| id | unique_id |
---------------
| 3 | 1 |
| 11 | 2 |
| 90 | 3 |
---------------
输出
---------------------
| unique_id | name |
---------------------
| null | Alice |
| null | Bob |
| 2 | Meir |
| 3 | Winston |
| 1 | Jonathan |
---------------------
解释
Alice and Bob 没有唯一标识码, 因此我们使用 null 替代。
Meir 的唯一标识码是 2 。
Winston 的唯一标识码是 3 。
Jonathan 唯一标识码是 1 。 分析 使用左连接输出表 Employee 中 name 的全部行包括符合两个表连接条件的行 SELECT u.unique_id,e.name
FROM Employees e LEFT JOIN EmployeeUNI u ON e.id u.id
1517、查找拥有有效邮箱的用户 表: Users ------------------------
| Column Name | Type |
------------------------
| user_id | int |
| name | varchar |
| mail | varchar |
------------------------
user_id 是该表的主键具有唯一值的列。
该表包含了网站已注册用户的信息。有一些电子邮件是无效的。编写一个解决方案以查找具有有效电子邮件的用户。 一个有效的电子邮件具有前缀名称和域其中 前缀 名称是一个字符串可以包含字母大写或小写数字下划线 _ 点 . 和/或破折号 - 。前缀名称 必须 以字母开头。域 为 leetcode.com 。 以任何顺序返回结果表。 结果的格式如以下示例所示 示例 1 输入
Users 表:
---------------------------------------------
| user_id | name | mail |
---------------------------------------------
| 1 | Winston | winstonleetcode.com |
| 2 | Jonathan | jonathanisgreat |
| 3 | Annabelle | bella-leetcode.com |
| 4 | Sally | sally.comeleetcode.com |
| 5 | Marwan | quarz#2020leetcode.com |
| 6 | David | david69gmail.com |
| 7 | Shapiro | .shapoleetcode.com |
---------------------------------------------
输出
---------------------------------------------
| user_id | name | mail |
---------------------------------------------
| 1 | Winston | winstonleetcode.com |
| 3 | Annabelle | bella-leetcode.com |
| 4 | Sally | sally.comeleetcode.com |
---------------------------------------------
解释
用户 2 的电子邮件没有域。
用户 5 的电子邮件带有不允许的 # 符号。
用户 6 的电子邮件没有 leetcode 域。
用户 7 的电子邮件以点开头。 分析 使用正则表达式进行字符串匹配^[a-z][a-zA-Z0-9_.-]*leetcode[.]com$ ^代表匹配字符串的开始$代表匹配字符串的结束 [a-zA-Z]表示以小写或大写字母开头[a-zA-Z0-9_.-]*表示可以有零个或多个小写字母、大写字母、数字、下划线、点、横杠leetcode[.]com表示以 leetcode.com 结尾点要用方括号包起来或者使用转义字符 \ ^[a-zA-Z][a-zA-Z0-9_.-]*leetcode\\.com$ 正则表达式正则表达式30分钟入门教程 (deerchao.cn) SELECT *
FROM Users u
WHERE mail REGEXP ^[a-zA-Z][a-zA-Z0-9_.-]*leetcode[.]com$ 1661、每台机器的进程平均运行时间 表: Activity -------------------------
| Column Name | Type |
-------------------------
| machine_id | int |
| process_id | int |
| activity_type | enum |
| timestamp | float |
-------------------------
该表展示了一家工厂网站的用户活动。
(machine_id, process_id, activity_type) 是当前表的主键具有唯一值的列的组合。
machine_id 是一台机器的ID号。
process_id 是运行在各机器上的进程ID号。
activity_type 是枚举类型 (start, end)。
timestamp 是浮点类型,代表当前时间(以秒为单位)。
start 代表该进程在这台机器上的开始运行时间戳 , end 代表该进程在这台机器上的终止运行时间戳。
同一台机器同一个进程都有一对开始时间戳和结束时间戳而且开始时间戳永远在结束时间戳前面。现在有一个工厂网站由几台机器运行每台机器上运行着 相同数量的进程 。编写解决方案计算每台机器各自完成一个进程任务的平均耗时。 完成一个进程任务的时间指进程的end 时间戳 减去 start 时间戳。平均耗时通过计算每台机器上所有进程任务的总耗费时间除以机器上的总进程数量获得。 结果表必须包含machine_id机器ID 和对应的 average time平均耗时 别名 processing_time且四舍五入保留3位小数。 以 任意顺序 返回表。 具体参考例子如下。 示例 1: 输入
Activity table:
--------------------------------------------------
| machine_id | process_id | activity_type | timestamp |
--------------------------------------------------
| 0 | 0 | start | 0.712 |
| 0 | 0 | end | 1.520 |
| 0 | 1 | start | 3.140 |
| 0 | 1 | end | 4.120 |
| 1 | 0 | start | 0.550 |
| 1 | 0 | end | 1.550 |
| 1 | 1 | start | 0.430 |
| 1 | 1 | end | 1.420 |
| 2 | 0 | start | 4.100 |
| 2 | 0 | end | 4.512 |
| 2 | 1 | start | 2.500 |
| 2 | 1 | end | 5.000 |
--------------------------------------------------
输出
-----------------------------
| machine_id | processing_time |
-----------------------------
| 0 | 0.894 |
| 1 | 0.995 |
| 2 | 1.456 |
-----------------------------
解释
一共有3台机器,每台机器运行着两个进程.
机器 0 的平均耗时: ((1.520 - 0.712) (4.120 - 3.140)) / 2 0.894
机器 1 的平均耗时: ((1.550 - 0.550) (1.420 - 0.430)) / 2 0.995
机器 2 的平均耗时: ((4.512 - 4.100) (5.000 - 2.500)) / 2 1.456 分析 该题主要是计算平均耗时由外向内讲解 ROUND( ,3)计算结果保留3位小数COUNT(DISTINCT process_id)和下面的GROUP BY 呼应计算分组的进程数DISTINCT去重SUM()计算分组的总耗时 CASE如果activity_type end求和时就加上当前的 timestamp否则就减去也配合GROUP BY 使用SUM() / COUNT()取平均值 SELECT machine_id, ROUND(SUM(CASE WHEN activity_type end THEN timestamp ELSE -timestamp END)/COUNT(DISTINCT process_id), 3) AS processing_time
FROM Activity
GROUP BY machine_id
1683、无效的推文 表Tweets -------------------------
| Column Name | Type |
-------------------------
| tweet_id | int |
| content | varchar |
-------------------------
在 SQL 中tweet_id 是这个表的主键。
这个表包含某社交媒体 App 中所有的推文。查询所有无效推文的编号ID。当推文内容中的字符数严格大于 15 时该推文是无效的。 以任意顺序返回结果表。 查询结果格式如下所示 示例 1 输入
Tweets 表
--------------------------------------------
| tweet_id | content |
--------------------------------------------
| 1 | Vote for Biden |
| 2 | Let us make America great again! |
--------------------------------------------输出
----------
| tweet_id |
----------
| 2 |
----------
解释
推文 1 的长度 length 14。该推文是有效的。
推文 2 的长度 length 32。该推文是无效的。 分析 使用字符串函数CHAR_LENGTH()返回字符串的长度 SELECT tweet_id
FROM Tweets
WHERE CHAR_LENGTH(content) 15 1693、每天的领导和合伙人 表DailySales ----------------------
| Column Name | Type |
----------------------
| date_id | date |
| make_name | varchar |
| lead_id | int |
| partner_id | int |
----------------------
该表没有主键(具有唯一值的列)。它可能包含重复项。
该表包含日期、产品的名称以及售给的领导和合伙人的编号。
名称只包含小写英文字母。对于每一个 date_id 和 make_name找出 不同 的 lead_id 以及 不同 的 partner_id 的数量。 按 任意顺序 返回结果表。 返回结果格式如下示例所示。 示例 1: 输入
DailySales 表
-------------------------------------------
| date_id | make_name | lead_id | partner_id |
-------------------------------------------
| 2020-12-8 | toyota | 0 | 1 |
| 2020-12-8 | toyota | 1 | 0 |
| 2020-12-8 | toyota | 1 | 2 |
| 2020-12-7 | toyota | 0 | 2 |
| 2020-12-7 | toyota | 0 | 1 |
| 2020-12-8 | honda | 1 | 2 |
| 2020-12-8 | honda | 2 | 1 |
| 2020-12-7 | honda | 0 | 1 |
| 2020-12-7 | honda | 1 | 2 |
| 2020-12-7 | honda | 2 | 1 |
-------------------------------------------
输出
-----------------------------------------------------
| date_id | make_name | unique_leads | unique_partners |
-----------------------------------------------------
| 2020-12-8 | toyota | 2 | 3 |
| 2020-12-7 | toyota | 1 | 2 |
| 2020-12-8 | honda | 2 | 2 |
| 2020-12-7 | honda | 3 | 2 |
-----------------------------------------------------
解释
在 2020-12-8丰田toyota有领导者 [0, 1] 和合伙人 [0, 1, 2] 同时本田honda有领导者 [1, 2] 和合伙人 [1, 2]。
在 2020-12-7丰田toyota有领导者 [0] 和合伙人 [1, 2] 同时本田honda有领导者 [0, 1, 2] 和合伙人 [1, 2]。 分析 先通过date_id分组再进一步通过make_name分组分组完了用COUNT()聚合函数分别统计每组里lead_id、partner_id的行数使用DISTINCT去掉重复的行数 SELECT date_id,make_name,COUNT(DISTINCT lead_id) AS unique_leads,COUNT(DISTINCT partner_id) AS unique_partners
FROM DailySales
GROUP BY date_id,make_name 1731、每位经理的下属员工数量 表Employees -----------------------
| Column Name | Type |
-----------------------
| employee_id | int |
| name | varchar |
| reports_to | int |
| age | int |
-----------------------
employee_id 是这个表中具有不同值的列。
该表包含员工以及需要听取他们汇报的上级经理的 ID 的信息。 有些员工不需要向任何人汇报reports_to 为空。对于此问题我们将至少有一个其他员工需要向他汇报的员工视为一个经理。 编写一个解决方案来返回需要听取汇报的所有经理的 ID、名称、直接向该经理汇报的员工人数以及这些员工的平均年龄其中该平均年龄需要四舍五入到最接近的整数。 返回的结果集需要按照 employee_id 进行排序。 结果的格式如下 示例 1: 输入
Employees 表
---------------------------------------
| employee_id | name | reports_to | age |
---------------------------------------
| 9 | Hercy | null | 43 |
| 6 | Alice | 9 | 41 |
| 4 | Bob | 9 | 36 |
| 2 | Winston | null | 37 |
---------------------------------------
输出
------------------------------------------------
| employee_id | name | reports_count | average_age |
------------------------------------------------
| 9 | Hercy | 2 | 39 |
------------------------------------------------
解释
Hercy 有两个需要向他汇报的员工, 他们是 Alice and Bob. 他们的平均年龄是 (4136)/2 38.5, 四舍五入的结果是 39.示例 2: 输入
Employees 表
---------------------------------------
| employee_id | name | reports_to | age |
|-------------|---------|------------|-----|
| 1 | Michael | null | 45 |
| 2 | Alice | 1 | 38 |
| 3 | Bob | 1 | 42 |
| 4 | Charlie | 2 | 34 |
| 5 | David | 2 | 40 |
| 6 | Eve | 3 | 37 |
| 7 | Frank | null | 50 |
| 8 | Grace | null | 48 |
---------------------------------------
输出
--------------------------------------------------
| employee_id | name | reports_count | average_age |
| ----------- | ------- | ------------- | ----------- |
| 1 | Michael | 2 | 40 |
| 2 | Alice | 2 | 37 |
| 3 | Bob | 1 | 37 |
-------------------------------------------------- 分析 自连接分成两张表e1表示经理e2表示员工连接条件为e1.employee_id e2.reports_to使用JOIN写法更规范以e2表指向的经理号report_to不同来分组即以经理为单位分组输出的也是经理的信息使用COUNT统计每个经理组里的员工数量使用ROUND指定结果保留 0 位小数SUM() / COUNT() 求平均 SELECT e1.employee_id,e1.name,COUNT(e2.reports_to) AS reports_count,ROUND(SUM(e2.age)/COUNT(e2.reports_to),0) AS average_age
FROM Employees e1 INNER JOIN Employees e2ON e1.employee_id e2.reports_to
GROUP BY e2.reports_to
ORDER BY e1.employee_id 1741、查找每个员工花费的总时间 表: Employees -------------------
| Column Name | Type |
-------------------
| emp_id | int |
| event_day | date |
| in_time | int |
| out_time | int |
-------------------
在 SQL 中(emp_id, event_day, in_time) 是这个表的主键。
该表显示了员工在办公室的出入情况。
event_day 是此事件发生的日期in_time 是员工进入办公室的时间而 out_time 是他们离开办公室的时间。
in_time 和 out_time 的取值在1到1440之间。
题目保证同一天没有两个事件在时间上是相交的并且保证 in_time 小于 out_time。计算每位员工每天在办公室花费的总时间以分钟为单位。 请注意在一天之内同一员工是可以多次进入和离开办公室的。 在办公室里一次进出所花费的时间为out_time 减去 in_time。 返回结果表单的顺序无要求。 查询结果的格式如下 示例 1 输入
Employees table:
---------------------------------------
| emp_id | event_day | in_time | out_time |
---------------------------------------
| 1 | 2020-11-28 | 4 | 32 |
| 1 | 2020-11-28 | 55 | 200 |
| 1 | 2020-12-03 | 1 | 42 |
| 2 | 2020-11-28 | 3 | 33 |
| 2 | 2020-12-09 | 47 | 74 |
---------------------------------------
输出
--------------------------------
| day | emp_id | total_time |
--------------------------------
| 2020-11-28 | 1 | 173 |
| 2020-11-28 | 2 | 30 |
| 2020-12-03 | 1 | 41 |
| 2020-12-09 | 2 | 27 |
--------------------------------
解释
雇员 1 有三次进出: 有两次发生在 2020-11-28 花费的时间为 (32 - 4) (200 - 55) 173, 有一次发生在 2020-12-03 花费的时间为 (42 - 1) 41。
雇员 2 有两次进出: 有一次发生在 2020-11-28 花费的时间为 (33 - 3) 30, 有一次发生在 2020-12-09 花费的时间为 (74 - 47) 27。 分析 先以event_day分组再以emp_id进一步分组计算每个分组里out_time - in_time后的和也可以理解为计算每个分组里SUM(out_time)再减去SUM(in_time) SELECT event_day AS day,emp_id,SUM(out_time-in_time) AS total_time
FROM Employees
GROUP BY event_day,emp_id 1789、员工的直属部门 表Employee ------------------------
| Column Name | Type |
------------------------
| employee_id | int |
| department_id | int |
| primary_flag | varchar |
------------------------
这张表的主键为 employee_id, department_id (具有唯一值的列的组合)
employee_id 是员工的ID
department_id 是部门的ID表示员工与该部门有关系
primary_flag 是一个枚举类型值分别为(Y, N). 如果值为Y,表示该部门是员工的直属部门。 如果值是N,则否一个员工可以属于多个部门。当一个员工加入超过一个部门的时候他需要决定哪个部门是他的直属部门。请注意当员工只加入一个部门的时候那这个部门将默认为他的直属部门虽然表记录的值为N. 请编写解决方案查出员工所属的直属部门。 返回结果 没有顺序要求 。 返回结果格式如下例子所示 示例 1 输入
Employee table:
------------------------------------------
| employee_id | department_id | primary_flag |
------------------------------------------
| 1 | 1 | N |
| 2 | 1 | Y |
| 2 | 2 | N |
| 3 | 3 | N |
| 4 | 2 | N |
| 4 | 3 | Y |
| 4 | 4 | N |
------------------------------------------
输出
----------------------------
| employee_id | department_id |
----------------------------
| 1 | 1 |
| 2 | 1 |
| 3 | 3 |
| 4 | 3 |
----------------------------
解释
- 员工 1 的直属部门是 1
- 员工 2 的直属部门是 1
- 员工 3 的直属部门是 3
- 员工 4 的直属部门是 3 分析 使用了子查询 有两个筛选条件一primary_flag Y二以employee_id为分组依据组内行数只有一组的 SELECT employee_id, department_id
FROM Employee
WHERE primary_flag Y OR employee_id IN(SELECT employee_idFROM EmployeeGROUP BY employee_idHAVING COUNT(employee_id) 1
)1795、每个产品在不同商店的价格列转行 表Products ----------------------
| Column Name | Type |
----------------------
| product_id | int |
| store1 | int |
| store2 | int |
| store3 | int |
----------------------
在 SQL 中这张表的主键是 product_id产品Id。
每行存储了这一产品在不同商店 store1, store2, store3 的价格。
如果这一产品在商店里没有出售则值将为 null。请你重构 Products 表查询每个产品在不同商店的价格使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售则不输出这一行。 输出结果表中的 顺序不作要求 。 查询输出格式请参考下面示例。 示例 1 输入
Products table:
------------------------------------
| product_id | store1 | store2 | store3 |
------------------------------------
| 0 | 95 | 100 | 105 |
| 1 | 70 | null | 80 |
------------------------------------
输出
---------------------------
| product_id | store | price |
---------------------------
| 0 | store1 | 95 |
| 0 | store2 | 100 |
| 0 | store3 | 105 |
| 1 | store1 | 70 |
| 1 | store3 | 80 |
---------------------------
解释
产品 0 在 store1、store2、store3 的价格分别为 95、100、105。
产品 1 在 store1、store3 的价格分别为 70、80。在 store2 无法买到。 分析 使用 UNION 合并表格 先从 store1 列选择非空值然后选择对应的 store2 和 store3 列的非空值并将它们合并到一个结果集中。最后按照 product_id 和 store 排序输出结果 SELECT product_id, store1 AS store, store1 AS price
FROM Products
WHERE store1 IS NOT NULL
UNION
SELECT product_id, store2 AS store, store2 AS price
FROM Products
WHERE store2 IS NOT NULL
UNION
SELECT product_id, store3 AS store, store3 AS price
FROM Products
WHERE store3 IS NOT NULL1873、计算特殊奖金 表: Employees ----------------------
| 列名 | 类型 |
----------------------
| employee_id | int |
| name | varchar |
| salary | int |
----------------------
employee_id 是这个表的主键(具有唯一值的列)。
此表的每一行给出了雇员id 名字和薪水。编写解决方案计算每个雇员的奖金。如果一个雇员的 id 是 奇数 并且他的名字不是以 M 开头那么他的奖金是他工资的 100% 否则奖金为 0 。 返回的结果按照 employee_id 排序。 返回结果格式如下面的例子所示。 示例 1: 输入
Employees 表:
------------------------------
| employee_id | name | salary |
------------------------------
| 2 | Meir | 3000 |
| 3 | Michael | 3800 |
| 7 | Addilyn | 7400 |
| 8 | Juan | 6100 |
| 9 | Kannon | 7700 |
------------------------------
输出
--------------------
| employee_id | bonus |
--------------------
| 2 | 0 |
| 3 | 0 |
| 7 | 7400 |
| 8 | 0 |
| 9 | 7700 |
--------------------
解释
因为雇员id是偶数所以雇员id 是2和8的两个雇员得到的奖金是0。
雇员id为3的因为他的名字以M开头所以奖金是0。
其他的雇员得到了百分之百的奖金。 分析 使用IF(expr,v1,v2)如果表达式expr为TRUE则返回值v1否则返回值为v2除了使用LIKE外还可使用正则表达式 name NOT REGEXP ^M SELECT employee_id,IF(employee_id % 2 1 name NOT LIKE M%,salary,0) AS bonus
FROM Employees
ORDER BY employee_id1890、2020年最后一次登录 表: Logins --------------------------
| 列名 | 类型 |
--------------------------
| user_id | int |
| time_stamp | datetime |
--------------------------
(user_id, time_stamp) 是这个表的主键(具有唯一值的列的组合)。
每一行包含的信息是user_id 这个用户的登录时间。编写解决方案以获取在 2020 年登录过的所有用户的本年度 最后一次 登录时间。结果集 不 包含 2020 年没有登录过的用户。 返回的结果集可以按 任意顺序 排列。 返回结果格式如下例。 示例 1: 输入
Logins 表:
------------------------------
| user_id | time_stamp |
------------------------------
| 6 | 2020-06-30 15:06:07 |
| 6 | 2021-04-21 14:06:06 |
| 6 | 2019-03-07 00:18:15 |
| 8 | 2020-02-01 05:10:53 |
| 8 | 2020-12-30 00:46:50 |
| 2 | 2020-01-16 02:49:50 |
| 2 | 2019-08-25 07:59:08 |
| 14 | 2019-07-14 09:00:00 |
| 14 | 2021-01-06 11:59:59 |
------------------------------
输出
------------------------------
| user_id | last_stamp |
------------------------------
| 6 | 2020-06-30 15:06:07 |
| 8 | 2020-12-30 00:46:50 |
| 2 | 2020-01-16 02:49:50 |
------------------------------
解释
6号用户登录了3次但是在2020年仅有一次所以结果集应包含此次登录。
8号用户在2020年登录了2次一次在2月一次在12月所以结果集应该包含12月的这次登录。
2号用户登录了2次但是在2020年仅有一次所以结果集应包含此次登录。
14号用户在2020年没有登录所以结果集不应包含。 分析 以 user_id 为分组依据对 time_stamp 进行分组且选择的是以2020开头的 除了使用LIKE外也可使用正则表达式 WHERE time_stamp REGEXP ^2020或者使用 YEAR() 函数提取日期里的年份 WHERE YEAR(time_stamp) 2020 然后使用MAX()聚合函数选择每组分组中最大的 time_stamp 输出 SELECT user_id,MAX(time_stamp) AS last_stamp
From Logins
WHERE time_stamp LIKE 2020%
GROUP BY user_id
ORDER BY time_stamp
