SQL——SELECT相关的题目
目录
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() 函数,如果x+y>z AND x+z>y AND y+z>x 为 true,就输出“Yes”,否则“No”
SELECT x,y,z,IF(x+y>z AND x+z>y AND y+z>x,"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,否则返回NULL
- id分组后,又提取到了某个 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_id=p.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 | winston@leetcode.com | | 2 | Jonathan | jonathanisgreat | | 3 | Annabelle | bella-@leetcode.com | | 4 | Sally | sally.come@leetcode.com | | 5 | Marwan | quarz#2020@leetcode.com | | 6 | David | david69@gmail.com | | 7 | Shapiro | .shapo@leetcode.com | +---------+-----------+-------------------------+ 输出: +---------+-----------+-------------------------+ | user_id | name | mail | +---------+-----------+-------------------------+ | 1 | Winston | winston@leetcode.com | | 3 | Annabelle | bella-@leetcode.com | | 4 | Sally | sally.come@leetcode.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. 他们的平均年龄是 (41+36)/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 NULL
1873、计算特殊奖金
表:
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_id
1890、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
相关文章:

SQL——SELECT相关的题目
目录 197、上升的温度 577、员工奖金 586、订单最多的客户 596、超过5名学生的课 610、判断三角形 620、有趣的电影 181、超过经理收入的员工 1179、重新格式化部门表(行转列) 1280、学生参加各科测试的次数 1068、产品销售分析I 1075、项目员工I …...

etcd集群部署
1.etcd介绍 1.1 什么是etcd etcd的官方定义如下: A distributed, reliable key-value store for the most critical data of distributed systemetcd是一个Go语言编写的分布式、高可用的一致性键值存储系统,用于提供可靠的分布式键值(key value)存储、配置共享和服务发现等…...

VBA_MF系列技术资料1-615
MF系列VBA技术资料1-615 为了让广大学员在VBA编程中有切实可行的思路及有效的提高自己的编程技巧,我参考大量的资料,并结合自己的经验总结了这份MF系列VBA技术综合资料,而且开放源码(MF04除外),其中MF01-0…...
常用激活函数学习
常用激活函数及其应用 ReLU (Rectified Linear Unit) 公式: f ( x ) max ( 0 , x ) f(x) \max(0, x) f(x)max(0,x)理解: 当输入值为正时,输出等于输入值;否则输出为0。ReLU函数简单且计算效率高,能有效缓解梯度消失问题,促进…...

html中被忽略的简单标签
1: alt的作用是在图片不能显示时的提示信息 <img src"https://img.xunfei.cn/mall/dev/ifly-mall-vip- service/business/vip/common/202404071019208761.jp" alt"提示信息" width"100px" height"100px" /> 2&#…...

Vue.Draggable:强大的Vue拖放组件技术探索
一、引言 随着前端技术的不断发展,拖放(Drag-and-Drop)功能已经成为许多Web应用不可或缺的一部分。Vue.js作为现代前端框架的佼佼者,为开发者提供了丰富的生态系统和强大的工具链。Vue.Draggable作为基于Sortable.js的Vue拖放组件…...

linux mail命令及其历史
一、【问题描述】 最近隔壁组有人把crontab删了,crontab这个命令有点反人类,它的参数特别容易误操作: crontab - 是删除计划表 crontab -e 是编辑,总之就是特别容易输入错误。 好在可以通过mail命令找回,但是mai…...
数据驱动(Data-Driven)和以数据为中心(Data-Centric)的区别
一、什么是数据驱动? 数据驱动(Data-Driven)是在管理科学领域经常提到的名词。数据驱动决策(Data-Driven Decision Making,简称DDD)是一种方法论,即在决策过程中主要依赖于数据分析和解释&…...

aosp14的分屏接口ISplitScreen接口获取方式更新-学员疑问答疑
背景: 有学员朋友在学习马哥的分屏pip自由窗口专题时候,做相关分屏做小桌面项目时候,因为原来课程版本是基于android 13进行的讲解的,但是现在公司已经开始逐渐进行相关的android 14的适配了,但是android 14这块相比a…...
定积分求解过程是否变限问题 以及当换元时注意事项
目录 定积分求解过程是否变限问题 文字理解: 实例理解: 易错点和易混点: 1:定积分中的换元指什么? 2: 不定积分中第一类换元法和第二类换元法的本质和区别 3: df(x) ----> df(x)这…...
保研机试算法训练个人记录笔记(七)
输入格式: 在第1 行给出不超过10^5 的正整数N, 即参赛}人数。随后N 行,每行给出一位参赛者的 信息和成绩,包括其所代表的学校的编号(从1 开始连续编号)及其比赛成绩(百分制)…...
【MySQL精通之路】SQL优化(1)-查询优化(23)-避免全表扫描
当MySQL使用全表扫描来解析查询时,EXPLAIN的输出在type列中显示ALL。 这种情况通常发生在以下情况下: 该表非常小,因此执行全表扫描比查找关键字更快。这对于少于10行且行长较短的表来说很常见。 对于索引列,ON或WHERE子句中没有…...

【Linux】写时拷贝技术COW (copy-on-write)
文章目录 Linux写时拷贝技术(copy-on-write)进程的概念进程的定义进程和程序的区别PCB的内部构成 程序是如何被加载变成进程的?写时复制(Copy-On-Write, COW)写时复制机制的原理写时拷贝的场景 fork与COWvfork与fork Linux写时拷贝技术(copy-…...
用python使用主成分分析数据
import pandas as pd #导入处理二维表格的库 import numpy as np #导入数值计算的库 from sklearn.preprocessing import StandardScaler #导入数据标准化模块 import matplotlib.pyplot as plt #导入画图的包 from sklearn.decomposition import PCA #导入主成…...

用WPS将多张图片生成一个pdf文档,注意参数设置
目录 1 新建一个docx格式的文档 2 向文档中插入图片 3 设置页边距 4 设置图片大小 5 导出为pdf格式 需要把十几张图片合并为一个pdf文件,本以为很简单,迅速从网上找到两个号称免费的在线工具,结果浪费了好几分钟时间,发现需要…...

virtual box ubuntu20 全屏展示
virtual box 虚拟机 ubuntu20 系统 全屏展示 ubuntu20.04 视图-自动调整窗口大小 视图-自动调整显示尺寸 系统黑屏解决 ##设备-安装增强功能 ##进入终端 ##终端打不开,解决方案-传送门ubuntu Open in Terminal打不开终端解决方案-CSDN博客 ##点击cd盘按钮进入文…...
react中的数据驱动视图,useState()的使用
前端开发如今有一个很重要的思想就是数据驱动视图,数据发生变化使ui发生变化,比如一个变量count,为0显示三个按钮,为1显示一个按钮,为2显示两个按钮。这就是一个简单的数据驱动视图。 import { useState } from reactf…...
金融数据库,实时行情,股票财务数据在线查询
jvQuant在线SQL 文档(current)接入 测试 查询 #股票代码股票简称最新价(元)2024-05-21股东总户数(户)截至2024-05-21上市板块是否ST股票区间涨跌幅(%)2024-05-15-2024-05-21市盈率(TTM)(倍)2024-05-21营业收入(元)截至2024-03-31总市值(元)2024-05-21量比2024-05-21利润率(%…...

开源模型应用落地-LangSmith试炼-入门初体验-数据集评估(三)
一、前言 LangSmith是一个用于构建生产级 LLM 应用程序的平台,它提供了调试、测试、评估和监控基于任何 LLM 框架构建的链和智能代理的功能,并能与LangChain无缝集成。通过使用LangSmith帮助开发者深入了解模型在不同场景下的表现,让开发者能…...
设计模式 15 Decorator Pattern 装饰器模式
设计模式 15 Decorator Pattern 装饰器模式 1.定义 Decorator Pattern 装饰器模式是一种结构型设计模式,它允许在运行时给对象添加新的行为或职责,而无需修改对象的源代码。这种模式通过创建一个包装对象,也称为装饰器,来包裹原…...
FFmpeg 低延迟同屏方案
引言 在实时互动需求激增的当下,无论是在线教育中的师生同屏演示、远程办公的屏幕共享协作,还是游戏直播的画面实时传输,低延迟同屏已成为保障用户体验的核心指标。FFmpeg 作为一款功能强大的多媒体框架,凭借其灵活的编解码、数据…...

[ICLR 2022]How Much Can CLIP Benefit Vision-and-Language Tasks?
论文网址:pdf 英文是纯手打的!论文原文的summarizing and paraphrasing。可能会出现难以避免的拼写错误和语法错误,若有发现欢迎评论指正!文章偏向于笔记,谨慎食用 目录 1. 心得 2. 论文逐段精读 2.1. Abstract 2…...

linux arm系统烧录
1、打开瑞芯微程序 2、按住linux arm 的 recover按键 插入电源 3、当瑞芯微检测到有设备 4、松开recover按键 5、选择升级固件 6、点击固件选择本地刷机的linux arm 镜像 7、点击升级 (忘了有没有这步了 估计有) 刷机程序 和 镜像 就不提供了。要刷的时…...
macOS多出来了:Google云端硬盘、YouTube、表格、幻灯片、Gmail、Google文档等应用
文章目录 问题现象问题原因解决办法 问题现象 macOS启动台(Launchpad)多出来了:Google云端硬盘、YouTube、表格、幻灯片、Gmail、Google文档等应用。 问题原因 很明显,都是Google家的办公全家桶。这些应用并不是通过独立安装的…...
Java多线程实现之Thread类深度解析
Java多线程实现之Thread类深度解析 一、多线程基础概念1.1 什么是线程1.2 多线程的优势1.3 Java多线程模型 二、Thread类的基本结构与构造函数2.1 Thread类的继承关系2.2 构造函数 三、创建和启动线程3.1 继承Thread类创建线程3.2 实现Runnable接口创建线程 四、Thread类的核心…...
Web 架构之 CDN 加速原理与落地实践
文章目录 一、思维导图二、正文内容(一)CDN 基础概念1. 定义2. 组成部分 (二)CDN 加速原理1. 请求路由2. 内容缓存3. 内容更新 (三)CDN 落地实践1. 选择 CDN 服务商2. 配置 CDN3. 集成到 Web 架构 …...

AI语音助手的Python实现
引言 语音助手(如小爱同学、Siri)通过语音识别、自然语言处理(NLP)和语音合成技术,为用户提供直观、高效的交互体验。随着人工智能的普及,Python开发者可以利用开源库和AI模型,快速构建自定义语音助手。本文由浅入深,详细介绍如何使用Python开发AI语音助手,涵盖基础功…...

MyBatis中关于缓存的理解
MyBatis缓存 MyBatis系统当中默认定义两级缓存:一级缓存、二级缓存 默认情况下,只有一级缓存开启(sqlSession级别的缓存)二级缓存需要手动开启配置,需要局域namespace级别的缓存 一级缓存(本地缓存&#…...
es6+和css3新增的特性有哪些
一:ECMAScript 新特性(ES6) ES6 (2015) - 革命性更新 1,记住的方法,从一个方法里面用到了哪些技术 1,let /const块级作用域声明2,**默认参数**:函数参数可以设置默认值。3&#x…...

实战设计模式之模板方法模式
概述 模板方法模式定义了一个操作中的算法骨架,并将某些步骤延迟到子类中实现。模板方法使得子类可以在不改变算法结构的前提下,重新定义算法中的某些步骤。简单来说,就是在一个方法中定义了要执行的步骤顺序或算法框架,但允许子类…...