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.salary1179、重新格式化部门表（行转列）
表
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_id1075、项目员工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_id1084、销售分析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 | +------------+--------------+------------+Salestable: +-----------+------------+----------+------------+----------+-------+ | 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.id1517、查找拥有有效邮箱的用户
表:
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_id1683、无效的推文
表：
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) > 151693、每天的领导和合伙人
表：
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_name1731、每位经理的下属员工数量
表：
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_id1741、查找每个员工花费的总时间
表:
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_id1789、员工的直属部门
表：
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 开始连续编号）及其比赛成绩（百分制）…...
-查询优化(23)-避免全表扫描)
【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 装饰器模式是一种结构型设计模式，它允许在运行时给对象添加新的行为或职责，而无需修改对象的源代码。这种模式通过创建一个包装对象，也称为装饰器，来包裹原…...
(LeetCode 每日一题) 3442. 奇偶频次间的最大差值 I (哈希、字符串)
题目：3442. 奇偶频次间的最大差值 I 思路 ：哈希，时间复杂度0(n)。 用哈希表来记录每个字符串中字符的分布情况，哈希表这里用数组即可实现。 C版本： class Solution { public:int maxDifference(string s) {int a[26]…...
【解密LSTM、GRU如何解决传统RNN梯度消失问题】
解密LSTM与GRU：如何让RNN变得更聪明？ 在深度学习的世界里，循环神经网络（RNN）以其卓越的序列数据处理能力广泛应用于自然语言处理、时间序列预测等领域。然而，传统RNN存在的一个严重问题——梯度消失&#…...
C#中的CLR属性、依赖属性与附加属性
CLR属性的主要特征 封装性： 隐藏字段的实现细节 提供对字段的受控访问 访问控制： 可单独设置get/set访问器的可见性 可创建只读或只写属性 计算属性： 可以在getter中执行计算逻辑 不需要直接对应一个字段 验证逻辑： 可以…...
力扣热题100 k个一组反转链表题解
题目: 代码: func reverseKGroup(head *ListNode, k int) *ListNode {cur : headfor i : 0; i < k; i {if cur nil {return head}cur cur.Next}newHead : reverse(head, cur)head.Next reverseKGroup(cur, k)return newHead }func reverse(start, end *ListNode) *ListN…...
HubSpot推出与ChatGPT的深度集成引发兴奋与担忧
上周三，HubSpot宣布已构建与ChatGPT的深度集成，这一消息在HubSpot用户和营销技术观察者中引发了极大的兴奋，但同时也存在一些关于数据安全的担忧。 许多网络声音声称，这对SaaS应用程序和人工智能而言是一场范式转变。 但向任何技…...
如何配置一个sql server使得其它用户可以通过excel odbc获取数据
要让其他用户通过 Excel 使用 ODBC 连接到 SQL Server 获取数据，你需要完成以下配置步骤： ✅ 一、在 SQL Server 端配置（服务器设置） 1. 启用 TCP/IP 协议 打开 “SQL Server 配置管理器”。导航到：SQL Server 网络配…...
【Linux】Linux安装并配置RabbitMQ
目录 1. 安装 Erlang 2. 安装 RabbitMQ 2.1.添加 RabbitMQ 仓库 2.2.安装 RabbitMQ 3.配置 3.1.启动和管理服务 4. 访问管理界面 5.安装问题 6.修改密码 7.修改端口 7.1.找到文件 7.2.修改文件 1. 安装 Erlang 由于 RabbitMQ 是用 Erlang 编写的，需要先安…...
前端调试HTTP状态码
1xx（信息类状态码） 这类状态码表示临时响应，需要客户端继续处理请求。 100 Continue 服务器已收到请求的初始部分，客户端应继续发送剩余部分。 2xx（成功类状态码） 表示请求已成功被服务器接收、理解并处…...
文件上传漏洞防御全攻略
要全面防范文件上传漏洞，需构建多层防御体系，结合技术验证、存储隔离与权限控制： 🔒 一、基础防护层 前端校验（仅辅助） 通过JavaScript限制文件后缀名（白名单）和大小，提…...
)
ArcPy扩展模块的使用(3)
管理工程项目 arcpy.mp模块允许用户管理布局、地图、报表、文件夹连接、视图等工程项目。例如，可以更新、修复或替换图层数据源，修改图层的符号系统，甚至自动在线执行共享要托管在组织中的工程项。 以下代码展示了如何更新图层的数据源&…...