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-012019-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 是该表的主键(具有唯一值的列)。
该表包含了网站已注册用户的信息。有一些电子邮件是无效的。

编写一个解决方案,以查找具有有效电子邮件的用户。

一个有效的电子邮件具有前缀名称和域,其中:

  1.  前缀 名称是一个字符串,可以包含字母(大写或小写),数字,下划线 '_' ,点 '.' 和/或破折号 '-' 。前缀名称 必须 以字母开头。
  2.  为 '@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 列选择非空值,然后选择对应的 store2store3 列的非空值,并将它们合并到一个结果集中。
  • 最后,按照 product_idstore 排序输出结果
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

 

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/331790.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

Docker化Spring Boot3应用:从镜像构建到部署

随着容器化技术的发展&#xff0c;越来越多的应用采用了容器化部署的方式。容器化部署极大地减少了因部署环境不同带来的差异&#xff0c;实现了一次构建、随处运行的效果。此外&#xff0c;容器化还具有版本管理、快速启动、持续集成等优点。今天&#xff0c;我们将介绍如何在…

C++进阶之路:何为拷贝构造函数,深入理解浅拷贝与深拷贝(类与对象_中篇)

✨✨ 欢迎大家来访Srlua的博文&#xff08;づ&#xffe3;3&#xffe3;&#xff09;づ╭❤&#xff5e;✨✨ &#x1f31f;&#x1f31f; 欢迎各位亲爱的读者&#xff0c;感谢你们抽出宝贵的时间来阅读我的文章。 我是Srlua小谢&#xff0c;在这里我会分享我的知识和经验。&am…

看这两位东北圣女美吗?如何描写美女的大长腿?

看这两位东北圣女美吗&#xff1f;如何描写美女的大长腿&#xff1f; 最近署名为懂球娘娘的一篇描写东北圣女的文章火了&#xff0c;文中描述了海棠朵朵与辛芷蕾这两位娇媚动人的角色。其美艳动人的形象和魅力四溢的描写让人为之倾倒。 这种通过文字展现人物魅力的能力让人佩服…

【PostgreSQL支持中文的全文检索插件(zhparser)】

PostgreSQL本身是支持全文检索的&#xff0c;提供两个数据类型&#xff08;tsvector,tsquery&#xff09;&#xff0c;并且通过动态检索自然语言文档的集合&#xff0c;定位到最匹配的查询结果。其内置的默认的分词解析器采用空格进行分词&#xff0c;但是因为中文的词语之间没…

Day06:Flex 布局

目标&#xff1a;熟练使用 Flex 完成结构化布局 一、标准流 标准流也叫文档流&#xff0c;指的是标签在页面中默认的排布规则&#xff0c;例如&#xff1a;块元素独占一行&#xff0c;行内元素可以一行显示多个。 二、浮动 1、基本使用 作用&#xff1a;让块元素水平排列。 …

Linux——进程信号

目录 一、信号的理解 二、信号的种类 2.1 标准信号 (1-31) 2.2 实时信号 (通常是34及以上) 三、信号的产生 3.1 用户通过终端产生信号 3.1.1 signal 函数 3.1.2 demo 测试 3.1.3 demo 现象 3.2 通过系统函数产生信号 3.2.1 demo 测试 3.3 由软件条件产生信号 3.3.1…

uni-app App端实现文字语音播报(Ba-TTS)

前言 最近在遇到消息提示语音播放出来&#xff0c;查了一圈文档发现并没有自带api 后面想起支付宝收钱播报&#xff0c;不受限与系统环境和版本环境&#xff08;后面查阅他是音频实现的&#xff09; 如果是由安卓端需要语音播放功能-直接使用Ba-TTs救急&#xff08;需要付费2…

MTK下载AP

只升级选Firemare Upgrade &#xff0c;点下载后&#xff0c;关机下插入USB

CSP俄罗斯方块(简单易懂)

开始将题目理解成了&#xff0c;开始的列应该是从输入图案的最左端开始计算&#xff0c;将前面所有的空列都删掉&#xff0c;代码如下&#xff1a; #include<bits/stdc.h> using namespace std; const int N 1e410; const int M 1e510; int a[20][20]; int b[5][5];int…

Java的类和对象

Java的类和对象 前言一、面向过程和面向对象初步认识C语言Java 二、类和类的实例化基本语法示例注意事项 类的实例化 三、类的成员字段/属性/成员变量注意事项默认值规则字段就地初始化 方法static 关键字修饰属性代码内存解析 修饰方法注意事项静态方法和实例无关, 而是和类相…

牛客NC295 连续子链表最大和【simple 动态规划 Java/Go/PHP/C++】

题目 题目链接&#xff1a; https://www.nowcoder.com/practice/650b68dfa69d492d92645aecd7da9b21 思路 动态规划动态规划算法通过迭代遍历输入数组&#xff0c;维护一个额外的数组 dp 来记录截止到每个位置的最大连续子数组和&#xff0c;并利用一个变量 max_num 实时更新全…

买房送户口!多城加入“抢人大战”

业内人士认为&#xff0c;近期&#xff0c;多地推出的购房落户政策已区别于此前的人才落户政策&#xff0c;更聚焦于住房消费&#xff0c;降低了落户门槛&#xff0c;体现了各地对导入人口的重视&#xff0c;有利于人才流动&#xff0c;推动新型城镇化建设。 千万人口城市“后…

GpuMall智算云:xinntao/Real-ESRGAN/Real-ESRGAN-v0.2.5.0

介绍 Real-ESRGAN旨在开发用于一般图像/视频恢复的实用算法。用纯合成数据训练现实世界盲人超级分辨率。我们将强大的ESRGAN扩展到一个实用的恢复应用程序&#xff08;即Real-ESRGAN&#xff09;&#xff0c;该应用程序使用纯合成数据进行训练。GpuMall智算云 | 省钱、好用、弹…

Qt下使用QImage和OpenCV实现图像的拼接与融合

文章目录 前言一、使用QImage进行水平拼接二、使用OpenCV进行水平拼接三、使用OpenCV进行图像融合四、示例完整代码总结 前言 本文主要讲述了在Qt下使用QImage和OpenCV实现图像的拼接与融合&#xff0c;并结合相应的示例进行讲解&#xff0c;以便大家学习&#xff0c;如有错误…

光速入门python的OpenCV

前言 欢迎来到我的博客 个人主页:北岭敲键盘的荒漠猫-CSDN博客 本文整理python的OpenCV模块的关键知识点 争取用最短的时间入门OpenCV 并且做到笔记功能直接复制使用 OpenCV简介 不浪费时间的介绍: 就是类似于ps操作图片。 至于为什么不直接用ps&#xff0c;因为只有程序能…

JAVA -- > 初识JAVA

初始JAVA 第一个JAVA程序详解 public class Main {public static void main(String[] args) {System.out.println("Hello world");} }1.public class Main: 类型,作为被public修饰的类,必须与文件名一致 2.public static 是JAVA中main函数准写法,记住该格式即可 …

碌时刻必备!微信自动回复让你告别消息堆积

在忙碌的时候&#xff0c;我们往往会面临消息堆积如山的情况。无法及时回复消息不仅容易造成交流障碍&#xff0c;还可能错过重要的机会。 但是现在&#xff0c;有一个神奇的工具——个微管理系统&#xff0c;可以帮助我们轻松应对这个问题 &#xff0c;实现微信自动回复。 首…

【DZ模板】价值288克米设计APP手机版DZ模板 数据本地化+完美使用

模版介绍 【DZ模板】价值288克米设计APP手机版DZ模板 数据本地化完美使用 腾讯官方出品discuz论坛DIY的后台设置&#xff0c;功能齐全&#xff0c;论坛功能不亚于葫芦侠&#xff0c;自定义马甲&#xff0c;自定义认证&#xff0c;自定义广告&#xff0c;完全可以打造出自己想…

【手把手带你搓组件库】从零开始实现Element Plus

从零开始实现Element Plus 前言亮点项目搭建1、创建项目初始化monorepo创建 .gitignore目录结构安装基础依赖配置文件创建各个分包入口utilscomponentscoreplaytheme 2、创建VitePress文档3、部署到Github Actions生成 GH_TOKENGitHub Page 演示 4、总结 前言 在本文中&#xf…

《计算机网络微课堂》3-11 虚拟局域网 VLAN

本节课我们介绍虚拟局域网 VLAN 的基本概念。 ‍ 3.11.1 虚拟局域网 VLAN 概述 在之前课程中我们已经介绍过了以太网交换机自学习和转发帧的流程&#xff0c;‍‍以及为避免网络环路而产生的生成树协议。 以太网交换机工作在数据链路层&#xff0c;‍‍也包括物理层&#xf…