SQL——SELECT相关的题目(力扣难度等级:简单)

目录

197、上升的温度

577、员工奖金

586、订单最多的客户

 596、超过5名学生的课

 610、判断三角形

 620、有趣的电影

181、超过经理收入的员工

 1179、重新格式化部门表(行转列)

 1280、学生参加各科测试的次数

1965、丢失信息的雇员

1068、产品销售分析I

 1075、项目员工I

1084、销售分析III

1327、列出指定时间段内所有的下单产品

1378、使用唯一标识码替换员工ID

1517、查找拥有有效邮箱的用户

 1661、每台机器的进程平均运行时间

1683、无效的推文

 1693、每天的领导和合伙人

 1731、每位经理的下属员工数量

 1741、查找每个员工花费的总时间

 1789、员工的直属部门

1795、每个产品在不同商店的价格(列转行)

1873、计算特殊奖金

 1890、2020年最后一次登录 

1978、上级经理已离职的公司员工

 2356、每位老师所教授的科目种类的数量


参考文章: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 e
    LEFT 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;

1965、丢失信息的雇员

表: Employees

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
+-------------+---------+
employee_id 是该表中具有唯一值的列。
每一行表示雇员的 id 和他的姓名。

表: Salaries

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| salary      | int     |
+-------------+---------+
employee_id 是该表中具有唯一值的列。
每一行表示雇员的 id 和他的薪水。

编写解决方案,找到所有 丢失信息 的雇员 id。当满足下面一个条件时,就被认为是雇员的信息丢失:

  • 雇员的 姓名 丢失了,或者
  • 雇员的 薪水信息 丢失了

返回这些雇员的 id  employee_id , 从小到大排序 

查询结果格式如下面的例子所示。

示例 1:

输入:
Employees table:
+-------------+----------+
| employee_id | name     |
+-------------+----------+
| 2           | Crew     |
| 4           | Haven    |
| 5           | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5           | 76071  |
| 1           | 22517  |
| 4           | 63539  |
+-------------+--------+
输出:
+-------------+
| employee_id |
+-------------+
| 1           |
| 2           |
+-------------+
解释:
雇员 1,2,4,5 都在这个公司工作。
1 号雇员的姓名丢失了。
2 号雇员的薪水信息丢失了。

分析:

  •  使用UNION将两个查询结果合并在一起,再按employee_id排序
  • 在查询Employees表时,只要该表的employee_id在Salaries表中找不到即为丢失了薪水信息,Salaries表也一样
SELECT employee_id
FROM Employees
WHERE employee_id NOT IN(
    SELECT employee_id
    FROM Salaries 
)
UNION 
SELECT employee_id
FROM Salaries 
WHERE employee_id NOT IN(
    SELECT employee_id
    FROM Employees
)
ORDER BY employee_id

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_id
    AND s.product_id NOT IN(
    SELECT product_id
    FROM Sales
    WHERE 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 e2
    ON 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_id
    FROM Employee
    GROUP BY employee_id
    HAVING 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

1978、上级经理已离职的公司员工

表: Employees

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| employee_id | int      |
| name        | varchar  |
| manager_id  | int      |
| salary      | int      |
+-------------+----------+
在 SQL 中,employee_id 是这个表的主键。
这个表包含了员工,他们的薪水和上级经理的id。
有一些员工没有上级经理(其 manager_id 是空值)。

查找这些员工的id,他们的薪水严格少于$30000 并且他们的上级经理已离职。当一个经理离开公司时,他们的信息需要从员工表中删除掉,但是表中的员工的manager_id  这一列还是设置的离职经理的id 。

返回的结果按照employee_id 从小到大排序。

查询结果如下所示:

示例:

输入:
Employees table:
+-------------+-----------+------------+--------+
| employee_id | name      | manager_id | salary |
+-------------+-----------+------------+--------+
| 3           | Mila      | 9          | 60301  |
| 12          | Antonella | null       | 31000  |
| 13          | Emery     | null       | 67084  |
| 1           | Kalel     | 11         | 21241  |
| 9           | Mikaela   | null       | 50937  |
| 11          | Joziah    | 6          | 28485  |
+-------------+-----------+------------+--------+
输出:
+-------------+
| employee_id |
+-------------+
| 11          |
+-------------+

解释:
薪水少于 30000 美元的员工有 1 号(Kalel) 和 11号 (Joziah)。
Kalel 的上级经理是 11 号员工,他还在公司上班(他是 Joziah )。
Joziah 的上级经理是 6 号员工,他已经离职,因为员工表里面已经没有 6 号员工的信息了,它被删除了。

分析:

  •  先筛选 salary < 30000的,并且manager_id 不在子查询结果中即manager_id != employee_id
  • 结果再按employee_id排序
SELECT employee_id 
FROM Employees
WHERE salary < 30000 AND manager_id NOT IN(
    SELECT employee_id 
    FROM Employees
)
ORDER BY employee_id

 2356、每位老师所教授的科目种类的数量

表: Teacher

+-------------+------+
| Column Name | Type |
+-------------+------+
| teacher_id  | int  |
| subject_id  | int  |
| dept_id     | int  |
+-------------+------+
在 SQL 中,(subject_id, dept_id) 是该表的主键。
该表中的每一行都表示带有 teacher_id 的教师在系 dept_id 中教授科目 subject_id。

查询每位老师在大学里教授的科目种类的数量。

以 任意顺序 返回结果表。

查询结果格式示例如下。

示例 1:

输入: 
Teacher 表:
+------------+------------+---------+
| teacher_id | subject_id | dept_id |
+------------+------------+---------+
| 1          | 2          | 3       |
| 1          | 2          | 4       |
| 1          | 3          | 3       |
| 2          | 1          | 1       |
| 2          | 2          | 1       |
| 2          | 3          | 1       |
| 2          | 4          | 1       |
+------------+------------+---------+
输出:  
+------------+-----+
| teacher_id | cnt |
+------------+-----+
| 1          | 2   |
| 2          | 4   |
+------------+-----+
解释: 
教师 1:
  - 他在 3、4 系教科目 2。
  - 他在 3 系教科目 3。
教师 2:
  - 他在 1 系教科目 1。
  - 他在 1 系教科目 2。
  - 他在 1 系教科目 3。
  - 他在 1 系教科目 4。

分析:

  •  按teacher_id对subject_id进行分组
  • 然后用COUNT()统计每组subject_id的行数,使用DISTINCT去掉重复的行
SELECT teacher_id,COUNT(DISTINCT subject_id) AS cnt
FROM Teacher 
GROUP BY teacher_id

 

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

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

教你网站如何免费实现https

想要实现https访问最简单有效的的方法就是安装SSL证书。只要证书正常安装上以后&#xff0c;浏览器就不会出现网站不安全提示或者访问被拦截的情况。下面我来教大家怎么去获取免费的SSL证书&#xff0c;又如何安装证书实现https访问。 一、选择免费SSL证书提供商 有多家机构提…

CLI举例:负载分担场景下的源NAT配置(主备设备共用同一个地址池)

CLI举例&#xff1a;负载分担场景下的源NAT配置&#xff08;主备设备共用同一个地址池&#xff09; 组网需求 如图1所示&#xff0c;企业的两台FW的业务接口都工作在三层&#xff0c;上下行分别连接路由器。FW与上下行路由器之间运行OSPF协议。上行接口连接同一个ISP。 现在希…

word-主控文档、文档拆分及标书编写技巧建议

一、主控文档 视图-大纲视图-显示文档-插入子文档 子文档一旦更新&#xff0c;主文档也会更新。更新主文档&#xff0c;子文档也会更新 需要注意&#xff0c;不可修改子文档名字 二、上交文件 显示文档-折叠子文档-只显示一级-取消链接-关闭大纲视图-保存 三、文档拆分 根…

数据结构算法题day03

数据结构算法题day03 题目 题目 2.设计一个高效算法&#xff0c;将顺序表L的所有元素逆置&#xff0c;要求算法的空间复杂度为O(1)算法思想&#xff1a; 1、常规的解法&#xff1a; Void reverse (sqlist &L){Elemtype temp; //辅助变量for(i 0,i < L.length; i){temp…

Thinkphp5响应式进销存仓库管理系统

随着企业规模的不断扩大和市场竞争的日益激烈&#xff0c;进销存管理在企业的运营中扮演着越来越重要的角色。为了提高企业的运营效率&#xff0c;降低库存成本&#xff0c;提升客户满意度&#xff0c;越来越多的企业开始引入进销存仓库管理系统。 进销存仓库管理系统是一种集…

【机器学习】深入探索机器学习:线性回归算法的原理与应用

❀线性回归算法 &#x1f4d2;1. 引言&#x1f4d2;2. 线性回归的基本原理&#x1f389;回归方程&#x1f389;最小化误差&#x1f389;线性回归的假设条件 &#x1f4d2;3. 线性回归算法的实现&#x1f4d2;4. 线性回归算法的特征工程&#x1f4d2;5. 线性回归模型评估与优化&…

蓝桥杯练习系统(算法训练)ALGO-932 低阶行列式计算

资源限制 内存限制&#xff1a;64.0MB C/C时间限制&#xff1a;1.0s Java时间限制&#xff1a;3.0s Python时间限制&#xff1a;5.0s 问题描述 给出一个n阶行列式(1<n<9)&#xff0c;求出它的值。 输入格式 第一行给出两个正整数n,p&#xff1b;   接下来n行&…

oracle准确记录数据提交时间

注意&#xff1a;mysql中的默认值同样记录的是dml操作发出时的时间&#xff0c;并且没有找到mysql中准确记录commit时间的方法。 oracle中数据发生变动时&#xff0c;如何准确记录发生变动时的时间。一般会使用ts字段&#xff0c;该字段使用默认值&#xff0c;default to_char…

JeeSite 4.x and 5.x快速开发平台前端技术探索与实践

一、引言 随着企业信息化建设的不断推进&#xff0c;对于快速、高效、安全的企业级应用需求日益增长。JeeSite作为一款企业级快速开发平台&#xff0c;以其强大的后端功能和灵活的前端架构&#xff0c;为开发者提供了强大的支持。本文旨在探讨JeeSite快速开发平台在前端技术方…

c++——模板初始识

1.函数模板 我们经常用到Swap函数交换两个值。由于需要交换的数据的类型不同&#xff0c;我们就需要写不同参数类型的同名函数&#xff0c;也就是函数重载&#xff1a; 然而这三个函数的逻辑是一样的&#xff0c;写这么多有些多此一举&#xff0c;通过函数模版可以写一个通用…

摸鱼大数据——Hive表操作——文件数据的导入和导出

数据导入和导出 1、文件数据导入 1.1 直接上传文件 window页面上传 需求: 已知emp1.txt文件在windows/mac系统,要求使用hdfs保存此文件 并且使用hivesql建表关联数据 use day06; ​ -- 1- 创建Hive表 create table emp1 (id int,name string,salary int,dept string )row for…

CUDA_VISIBLE_DEVICES‘ 不是内部或外部命令,也不是可运行的程序或批处理文件。

问题&#xff1a; 命令行出现CUDA_VISIBLE_DEVICES0 python trainer.py这种命令 这是Linux可以的&#xff0c;但是Windows不行。 解决方案&#xff1a; 这条命令的含义是指定某个GPU来运行程序&#xff0c;我们可以在程序开头添加指定GPU的代码&#xff0c;效果是一样的&…

xLSTM——解析扩展长短期记忆的网络算法与应用

1.概述 二十多年来&#xff0c; 塞普霍赫赖特 创举 长短期记忆 (LSTM) 架构在许多深度学习突破和实际应用中发挥了重要作用。从生成自然语言到为语音识别系统提供动力&#xff0c;LSTM 一直是人工智能革命背后的驱动力。 然而&#xff0c;即使是 LSTM 的创建者也认识到它们固…

【Sql Server】随机查询一条表记录,并重重温回顾下存储过程的封装和使用

大家好&#xff0c;我是全栈小5&#xff0c;欢迎来到《小5讲堂》。 这是《Sql Server》系列文章&#xff0c;每篇文章将以博主理解的角度展开讲解。 温馨提示&#xff1a;博主能力有限&#xff0c;理解水平有限&#xff0c;若有不对之处望指正&#xff01; 目录 前言随机查询语…

Visual Studio +Resharp,让你在C#编程界一骑绝尘!【文末送2024C#入门到精通教程视频+源码C#.NET全栈开发高级VIP班[完结】

目录 前言 一、Visual Studio&#xff1a;C#开发的强大后盾 二、ReSharper&#xff1a;智能代码编辑器的典范 三、Visual Studio ReSharper&#xff1a;一骑绝尘的编程组合 四、如何发挥最大效能 五、结论 文末福利&#xff1a; 文末有福利 前言 在C#编程领域&#x…

情感分析及数据集代码解析

15.1. 情感分析及数据集 — 动手学深度学习 2.0.0 documentation (d2l.ai) 代码 import os import torch from torch import nn from d2l import torch as d2l#save d2l.DATA_HUB[aclImdb] (http://ai.stanford.edu/~amaas/data/sentiment/aclImdb_v1.tar.gz,01ada507287d828…

【Altium Designer】安装教程

一、软件下载 二、软件安装 三、授权文件激活 一、软件下载 Altium Designer官网下载 二、软件安装 1.解压完下载好的软件&#xff0c;鼠标右击【OfflineSetup_Altium_Designer_Public_24_5_1】安装程序&#xff0c;选择【以管理员身份运行】。 2.语言选择【Chinese】&#…

如何向《大众日报》理论稿投稿?

《大众日报》是山东省委机关报&#xff0c;其理论版是该报的重要版面之一&#xff0c;主要刊登关于政治、经济、文化、社会等方面的理论文章和研究成果。 - 投稿方式&#xff1a;通常可以通过电子邮件或在线投稿系统向《大众日报》理论版投稿。 - 选题范围&#xff1a;理论版…

回收站删掉的照片还能找回来吗?掌握这5个方法,很简单!

“我一不小心在回收站中误删了一些照片&#xff0c;这些照片对我来说是比较重要的&#xff0c;不知道这些还有机会将它们找回来吗&#xff1f;” 当我们不小心将珍贵的照片从计算机的回收站中误删时&#xff0c;那种瞬间的心痛和焦虑难以言表。这些照片可能记录了我们生活中的重…

kafka-生产者发送消息消费者消费消息

文章目录 1、生产者发送消息&消费者消费消息1.1、获取 kafka-console-producer.sh 的帮助信息1.2、生产者发送消息到某个主题1.3、消费主题数据 1、生产者发送消息&消费者消费消息 1.1、获取 kafka-console-producer.sh 的帮助信息 [rootlocalhost ~]# kafka-console…