LeetCode 高频SQL50（基础版）

🕗 发布于 2024-11-26 13:42 leetcode 算法

文章目录

[高频 SQL 50 题（基础版）](https://leetcode.cn/studyplan/sql-free-50/)

1 查询

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| low_fats    | enum    |
| recyclable  | enum    |
+-------------+---------+
product_id 是该表的主键（具有唯一值的列）。
low_fats 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品是低脂产品，'N' 表示不是低脂产品。
recyclable 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品可回收，而 'N' 表示不可回收。


select product_id from Products where low_fats="Y" and recyclable="Y";

584. 寻找用户推荐人

输入： 
Customer 表:
+----+------+------------+
| id | name | referee_id |
+----+------+------------+
| 1  | Will | null       |
| 2  | Jane | null       |
| 3  | Alex | 2          |
| 4  | Bill | null       |
| 5  | Zack | 1          |
| 6  | Mark | 2          |
+----+------+------------+
输出：
+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

select name from Customer 
where referee_id!=2 or referee_id is null;

MySQL 使用三值逻辑 —— TRUE, FALSE 和 UNKNOWN。任何与 NULL 值进行的比较都会与第三种值 UNKNOWN 做比较。这个“任何值”包括 NULL 本身！这就是为什么 MySQL 提供 IS NULL 和 IS NOT NULL 两种操作来对 NULL 特殊判断。

因此，在 WHERE 语句中我们需要做一个额外的条件判断 `referee_id IS NULL’。

595. 大的国家

World 表：

±------------±--------+

| Column Name | Type |

±------------±--------+

| name | varchar |

| continent | varchar |

| area | int |

| population | int |

| gdp | bigint |

±------------±--------+

name 是该表的主键（具有唯一值的列）。

这张表的每一行提供：国家名称、所属大陆、面积、人口和 GDP 值。

如果一个国家满足下述两个条件之一，则认为该国是大国：

面积至少为 300 万平方公里（即，3000000 km2），或者

人口至少为 2500 万（即 25000000）

编写解决方案找出大国的国家名称、人口和面积。

按任意顺序返回结果表。

select name,population,area from World
where area>=3000000 or population>=25000000;

1148. 文章浏览 I

Views 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| article_id    | int     |
| author_id     | int     |
| viewer_id     | int     |
| view_date     | date    |
+---------------+---------+
此表可能会存在重复行。（换句话说，在 SQL 中这个表没有主键）
此表的每一行都表示某人在某天浏览了某位作者的某篇文章。
请注意，同一人的 author_id 和 viewer_id 是相同的。

请查询出所有浏览过自己文章的作者

结果按照 id 升序排列。

查询结果的格式如下所示：

示例 1：

输入：
Views 表：
+------------+-----------+-----------+------------+
| article_id | author_id | viewer_id | view_date  |
+------------+-----------+-----------+------------+
| 1          | 3         | 5         | 2019-08-01 |
| 1          | 3         | 6         | 2019-08-02 |
| 2          | 7         | 7         | 2019-08-01 |
| 2          | 7         | 6         | 2019-08-02 |
| 4          | 7         | 1         | 2019-07-22 |
| 3          | 4         | 4         | 2019-07-21 |
| 3          | 4         | 4         | 2019-07-21 |
+------------+-----------+-----------+------------+

输出：
+------+
| id   |
+------+
| 4    |
| 7    |
+------+

select 
    distinct author_id as id
from Views
where author_id=viewer_id
order by id

1683. 无效的推文

表：Tweets

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| tweet_id       | int     |
| content        | varchar |
+----------------+---------+
在 SQL 中，tweet_id 是这个表的主键。
这个表包含某社交媒体 App 中所有的推文。

查询所有无效推文的编号（ID）。当推文内容中的字符数严格大于 15 时，该推文是无效的。

以任意顺序返回结果表。

查询结果格式如下所示：

输入：
Tweets 表：
+----------+----------------------------------+
| tweet_id | content                          |
+----------+----------------------------------+
| 1        | Vote for Biden                   |
| 2        | Let us make America great again! |
+----------+----------------------------------+

输出：
+----------+
| tweet_id |
+----------+
| 2        |
+----------+
解释：
推文 1 的长度 length = 14。该推文是有效的。
推文 2 的长度 length = 32。该推文是无效的。

select tweet_id
from Tweets
where char_length(content)>15;

对于SQL表，用于计算字符串中字符数的最佳函数是 CHAR_LENGTH(str)，它返回字符串 str 的长度。

另一个常用的函数 LENGTH(str) 在这个问题中也适用，因为列 content 只包含英文字符，没有特殊字符。否则，LENGTH() 可能会返回不同的结果，因为该函数返回字符串 str 的字节数，某些字符包含多于 1 个字节。

以字符 ‘¥’ 为例：CHAR_LENGTH() 返回结果为 1，而 LENGTH() 返回结果为 2，因为该字符串包含 2 个字节。

2 连接

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

展示每位用户的唯一标识码（unique ID ）；如果某位员工没有唯一标识码，使用 null 填充即可。

创建表并插入数据

首先，我们需要创建两个表 Employees 和 EmployeeUNI，并插入相应的数据。

-- 创建 Employees 表
CREATE TABLE Employees (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 插入数据到 Employees 表
INSERT INTO Employees (id, name) VALUES
(1, 'Alice'),
(7, 'Bob'),
(11, 'Meir'),
(90, 'Winston'),
(3, 'Jonathan');

-- 创建 EmployeeUNI 表
CREATE TABLE EmployeeUNI (
    id INT,
    unique_id INT,
    PRIMARY KEY (id, unique_id)
);

-- 插入数据到 EmployeeUNI 表
INSERT INTO EmployeeUNI (id, unique_id) VALUES
(3, 1),
(11, 2),
(90, 3);

执行查询

接下来，我们使用 LEFT JOIN 将这两个表连接起来，以获取每个员工的唯一标识码。如果某个员工没有唯一标识码，则 unique_id 字段会显示为 NULL。

select 
    u.unique_id,
    e.name
from 
    Employees e
left join
    EmployeeUNI u on e.id = u.id;

LEFT JOIN 确保了 Employees 表中的所有记录都包含在结果中。

如果某个员工在 EmployeeUNI 表中没有对应的记录，那么 unique_id 字段将显示为 NULL。

1068. 产品销售分析 I

-- 创建 Sales 表
CREATE TABLE Sales (
    sale_id INT,
    product_id INT,
    year INT,
    quantity INT,
    price INT,
    PRIMARY KEY (sale_id, year)
);

-- 插入数据到 Sales 表
INSERT INTO Sales (sale_id, product_id, year, quantity, price) VALUES
(1, 100, 2008, 10, 5000),
(2, 100, 2009, 12, 5000),
(7, 200, 2011, 15, 9000);

-- 创建 Product 表
CREATE TABLE Product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100)
);

-- 插入数据到 Product 表
INSERT INTO Product (product_id, product_name) VALUES
(100, 'Nokia'),
(200, 'Apple'),
(300, 'Samsung');

select
    p.product_name,s.year,s.price
from 
    Sales s
left join
    Product p 
on 
    s.product_id=p.product_id

INNER JOIN：我们将 Sales 表和 Product 表通过 product_id 字段进行连接。这样可以确保只有当 Sales 表中的 product_id 在 Product 表中存在时，才会返回相应的记录。

选择字段：我们选择了 Product 表中的 product_name 和 Sales 表中的 year 和 price 字段。

1581. 进店却未进行过交易的顾客

-- 创建 Visits 表
CREATE TABLE Visits (
    visit_id INT,
    customer_id INT,
    PRIMARY KEY (visit_id)
);

-- 插入数据到 Visits 表
INSERT INTO Visits (visit_id, customer_id) VALUES
(1, 23),
(2, 9),
(4, 30),
(5, 54),
(6, 96),
(7, 54),
(8, 54);

-- 创建 Transactions 表
CREATE TABLE Transactions (
    transaction_id INT,
    visit_id INT,
    amount INT,
    PRIMARY KEY (transaction_id)
);

-- 插入数据到 Transactions 表
INSERT INTO Transactions (transaction_id, visit_id, amount) VALUES
(2, 5, 310),
(3, 5, 300),
(9, 5, 200),
(12, 1, 910),
(13, 2, 970);

select 
    v.customer_id, 
    count(v.visit_id) as count_no_trans
from 
    Visits v 
left join
    Transactions t
on v.visit_id=t.visit_id
where 
    t.visit_id is null
group by
    v.customer_id;

LEFT JOIN：将 Visits 表和 Transactions 表通过 visit_id 字段进行左连接。这样可以确保所有 Visits 表中的记录都会出现在结果中，即使它们在 Transactions 表中没有对应的记录。

WHERE t.visit_id IS NULL：这个条件用于筛选出那些在 Transactions 表中没有对应记录的访问记录，即那些没有进行交易的访问。

GROUP BY v.customer_id：我们按 customer_id 分组，以便统计每个顾客的无交易访问次数。

COUNT(v.visit_id)：计算每个顾客的无交易访问次数。

197. 上升的温度

找出那些温度比前一天高的日期，并返回这些日期对应的 id。我们可以使用自连接（self join）来比较每一天的温度与其前一天的温度。

-- 创建 Weather 表
CREATE TABLE Weather (
    id INT PRIMARY KEY,
    recordDate DATE,
    temperature INT
);

-- 插入数据到 Weather 表
INSERT INTO Weather (id, recordDate, temperature) VALUES
(1, '2015-01-01', 10),
(2, '2015-01-02', 25),
(3, '2015-01-03', 20),
(4, '2015-01-04', 30);

select w1.id
from 
    Weather w1
    join
        Weather w2 
    on w1.recordDate=date_add(w2.recordDate,interval 1 day)
where
    w1.temperature > w2.temperature

JOIN：我们将 Weather 表与自身进行连接，连接条件是 w1.recordDate 等于 w2.recordDate 的下一天（即 w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY)）。这样可以确保我们在比较每一天的温度时，能够找到前一天的记录。
WHERE w1.temperature > w2.temperature：这个条件用于筛选出那些温度比前一天高的记录。
SELECT w1.id：我们只需要返回符合条件的记录的 id。

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

计算每个进程的运行时间：对于每个进程，我们需要找到它的开始时间和结束时间，并计算它们之间的差值。
按机器分组：将计算出的运行时间按机器分组，计算每台机器的总运行时间和进程数量。
计算平均耗时：对于每台机器，计算总运行时间除以进程数量，并将结果四舍五入到三位小数。

-- 创建 Activity 表
CREATE TABLE Activity (
    machine_id INT,
    process_id INT,
    activity_type ENUM('start', 'end'),
    timestamp FLOAT,
    PRIMARY KEY (machine_id, process_id, activity_type)
);

-- 插入数据到 Activity 表
INSERT INTO Activity (machine_id, process_id, activity_type, timestamp) VALUES
(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);

select a1.machine_id,
    round(avg(a2.timestamp-a1.timestamp),3) as processing_time
from Activity a1
    join Activity a2
    on a1.machine_id=a2.machine_id and a1.process_id=a2.process_id
where 
    a1.activity_type='start' and a2.activity_type='end'
group by 
    a1.machine_id;

JOIN：我们将 Activity 表与自身进行连接，连接条件是 a1.machine_id = a2.machine_id 和 a1.process_id = a2.process_id。这样可以确保我们在比较每个进程的开始时间和结束时间时，能够找到对应的记录。
WHERE a1.activity_type = ‘start’ AND a2.activity_type = ‘end’：这个条件用于筛选出开始时间和结束时间的记录。
a2.timestamp - a1.timestamp：计算每个进程的运行时间。
AVG(a2.timestamp - a1.timestamp)：计算每台机器上所有进程的平均运行时间。
ROUND(…, 3)：将结果四舍五入到三位小数。
GROUP BY a1.machine_id：按机器分组，以便计算每台机器的平均运行时间。

+------------+-----------------+
| machine_id | processing_time |
+------------+-----------------+
| 0          | 0.894           |
| 1          | 0.995           |
| 2          | 1.456           |
+------------+-----------------+

577. 员工奖金

-- 创建 Employee 表
CREATE TABLE Employee (
    empId INT PRIMARY KEY,
    name VARCHAR(100),
    supervisor INT,
    salary INT
);

-- 插入数据到 Employee 表
INSERT INTO Employee (empId, name, supervisor, salary) VALUES
(3, 'Brad', null, 4000),
(1, 'John', 3, 1000),
(2, 'Dan', 3, 2000),
(4, 'Thomas', 3, 4000);

-- 创建 Bonus 表
CREATE TABLE Bonus (
    empId INT PRIMARY KEY,
    bonus INT
);

-- 插入数据到 Bonus 表
INSERT INTO Bonus (empId, bonus) VALUES
(2, 500),
(4, 2000);

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;

LEFT JOIN：我们将 Employee 表和 Bonus 表通过 empId 字段进行左连接。这样可以确保所有 Employee 表中的记录都会出现在结果中，即使它们在 Bonus 表中没有对应的记录。
WHERE b.bonus < 1000 OR b.bonus IS NULL：这个条件用于筛选出那些奖金少于 1000 或者没有奖金记录的员工。
SELECT e.name, b.bonus：我们选择员工的姓名和奖金数额。

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

-- 创建 Students 表
CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100)
);

-- 插入数据到 Students 表
INSERT INTO Students (student_id, student_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(13, 'John'),
(6, 'Alex');

-- 创建 Subjects 表
CREATE TABLE Subjects (
    subject_name VARCHAR(100) PRIMARY KEY
);

-- 插入数据到 Subjects 表
INSERT INTO Subjects (subject_name) VALUES
('Math'),
('Physics'),
('Programming');

-- 创建 Examinations 表
CREATE TABLE Examinations (
    student_id INT,
    subject_name VARCHAR(100)
);

-- 插入数据到 Examinations 表
INSERT INTO Examinations (student_id, subject_name) VALUES
(1, 'Math'),
(1, 'Physics'),
(1, 'Programming'),
(2, 'Programming'),
(1, 'Physics'),
(1, 'Math'),
(13, 'Math'),
(13, 'Programming'),
(13, 'Physics'),
(2, 'Math'),
(1, 'Math');

select 
    stu.student_id,
    stu.student_name,
    sub.subject_name,
    count(Ex.subject_name) as attended_exams
from 
    Students stu
join
    Subjects sub
left join
        Examinations Ex
    on  stu.student_id=Ex.student_id
    and sub.subject_name=Ex.subject_name
group by
    stu.student_id,stu.student_name,sub.subject_name
order by
    stu.student_id,sub.subject_name;

+------------+--------------+--------------+----------------+
| 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              |
+------------+--------------+--------------+----------------+

570. 至少有5名直接下属的经理

-- 创建 Employee 表
CREATE TABLE Employee (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    managerId INT
);

-- 插入数据到 Employee 表
INSERT INTO Employee (id, name, department, managerId) VALUES
(101, 'John', 'A', null),
(102, 'Dan', 'A', 101),
(103, 'James', 'A', 101),
(104, 'Amy', 'A', 101),
(105, 'Anne', 'A', 101),
(106, 'Ron', 'B', 101);

-- 查询每个经历的直接下属数量,并筛选出下属数量大于5的经理
select
    e1.name
from
    employee e1
join
    (select 
     managerId, count(*) as num_subordinates
     from 
     Employee
     where
     managerId is not null
     group by 
     managerId
     having
     count(*) >=5) e2
on
    e1.id=e2.managerId;

子查询：

SELECT managerId, COUNT(*) AS num_subordinates FROM Employee WHERE managerId IS NOT NULL GROUP BY managerId HAVING COUNT(*) >= 5：这个子查询统计每个经理的直接下属数量，并筛选出那些直接下属数量大于等于5的经理。

managerId 是经理的 id。

COUNT(*) 计算每个经理的直接下属数量。

HAVING COUNT(*) >= 5 筛选出直接下属数量大于等于5的经理。

主查询：

SELECT e1.name FROM Employee e1 JOIN (...) e2 ON e1.id = e2.managerId：主查询通过 JOIN 操作将经理的 id 映射到他们的姓名。

e1 是 Employee 表的别名。

e2 是子查询的结果表的别名。

ON e1.id = e2.managerId 确保我们只选择那些经理的记录。

1934. 确认率

-- 创建 Signups 表
CREATE TABLE Signups (
    user_id INT PRIMARY KEY,
    time_stamp DATETIME
);

-- 插入数据到 Signups 表
INSERT INTO Signups (user_id, time_stamp) VALUES
(3, '2020-03-21 10:16:13'),
(7, '2020-01-04 13:57:59'),
(2, '2020-07-29 23:09:44'),
(6, '2020-12-09 10:39:37');

-- 创建 Confirmations 表
CREATE TABLE Confirmations (
    user_id INT,
    time_stamp DATETIME,
    action ENUM('confirmed', 'timeout'),
    PRIMARY KEY (user_id, time_stamp)
);

-- 插入数据到 Confirmations 表
INSERT INTO Confirmations (user_id, time_stamp, action) VALUES
(3, '2021-01-06 03:30:46', 'timeout'),
(3, '2021-07-14 14:00:00', 'timeout'),
(7, '2021-06-12 11:57:29', 'confirmed'),
(7, '2021-06-13 12:58:28', 'confirmed'),
(7, '2021-06-14 13:59:27', 'confirmed'),
(2, '2021-01-22 00:00:00', 'confirmed'),
(2, '2021-02-28 23:59:59', 'timeout');

select 
    s.user_id,
    round(
      ifnull(sum(case when c.action='confirmed' then 1 else 0 end)/count(c.action),0),
      2
    )
        as confirmation_rate
from 
    Signups s
left join
    Confirmations c
    on s.user_id=c.user_id
group by
    s.user_id;

+---------+-------------------+
| user_id | confirmation_rate |
+---------+-------------------+
| 6       | 0.00              |
| 3       | 0.00              |
| 7       | 1.00              |
| 2       | 0.50              |
+---------+-------------------+

LEFT JOIN：我们将 Signups 表和 Confirmations 表通过 user_id 字段进行左连接。这样可以确保所有 Signups 表中的用户都会出现在结果中，即使他们在 Confirmations 表中没有对应的记录。
SUM(CASE WHEN c.action = ‘confirmed’ THEN 1 ELSE 0 END)：这个表达式用于计算每个用户的 ‘confirmed’ 消息数。
COUNT(c.action)：这个表达式用于计算每个用户的总消息数。
IFNULL(…, 0)：如果某个用户没有任何确认消息记录，SUM 和 COUNT 都会返回 NULL，使用 IFNULL 将其转换为 0。
ROUND(…, 2)：将确认率四舍五入到小数点后两位。
GROUP BY s.user_id：按用户分组，以便计算每个用户的确认率。

3 聚合函数

620. 有趣的电影

-- 创建 cinema 表
CREATE TABLE cinema (
    id INT PRIMARY KEY,
    movie VARCHAR(100),
    description VARCHAR(100),
    rating FLOAT
);

-- 插入数据到 cinema 表
INSERT INTO cinema (id, movie, description, rating) VALUES
(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);

select 
    id,movie,description,rating
from
    cinema
where
    description!='boring' and id%2=1
order by 
    rating DESC

1251. 平均售价

-- 创建 Prices 表
CREATE TABLE Prices (
    product_id INT,
    start_date DATE,
    end_date DATE,
    price INT,
    PRIMARY KEY (product_id, start_date, end_date)
);

-- 插入数据到 Prices 表
INSERT INTO Prices (product_id, start_date, end_date, price) VALUES
(1, '2019-02-17', '2019-02-28', 5),
(1, '2019-03-01', '2019-03-22', 20),
(2, '2019-02-01', '2019-02-20', 15),
(2, '2019-02-21', '2019-03-31', 30);

-- 创建 UnitsSold 表
CREATE TABLE UnitsSold (
    product_id INT,
    purchase_date DATE,
    units INT
);

-- 插入数据到 UnitsSold 表
INSERT INTO UnitsSold (product_id, purchase_date, units) VALUES
(1, '2019-02-25', 100),
(1, '2019-03-01', 15),
(2, '2019-02-10', 200),
(2, '2019-03-22', 30);

连接两个表：将 Prices 表和 UnitsSold 表通过 product_id 进行连接，并确保 purchase_date 在 start_date 和 end_date 之间。
计算总销售额：对于每个产品的每个销售记录，计算其销售额（price * units）。
计算总销售数量：统计每个产品的总销售数量。
计算平均售价：将总销售额除以总销售数量，并四舍五入到小数点后两位。
处理没有销售记录的产品：对于没有销售记录的产品，假设其平均售价为 0。

select
    p.product_id,
    coalesce(round(sum(p.price*u.units)/sum(u.units),2),0) as average_price
from
    Prices p
left join
        UnitsSold u
    on p.product_id=u.product_id and u.purchase_date between p.start_date and p.end_date
group by
    product_id;

LEFT JOIN：我们将 Prices 表和 UnitsSold 表通过 product_id 进行左连接，并确保 purchase_date 在 start_date 和 end_date 之间。这样可以确保所有产品的记录都会出现在结果中，即使它们在 UnitsSold 表中没有对应的记录。
SUM(p.price * u.units)：计算每个产品的总销售额。
SUM(u.units)：计算每个产品的总销售数量。
COALESCE(…, 0)：如果某个产品没有任何销售记录，SUM(u.units) 会返回 NULL，使用 COALESCE 将其转换为 0。
ROUND(…, 2)：将平均售价四舍五入到小数点后两位。
GROUP BY p.product_id：按产品分组，以便计算每个产品的平均售价。

1075. 项目员工 I

-- 创建 Project 表
CREATE TABLE Project (
    project_id INT,
    employee_id INT,
    PRIMARY KEY (project_id, employee_id)
);

-- 插入数据到 Project 表
INSERT INTO Project (project_id, employee_id) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(2, 4);

-- 创建 Employee 表
CREATE TABLE Employee (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    experience_years INT
);

-- 插入数据到 Employee 表
INSERT INTO Employee (employee_id, name, experience_years) VALUES
(1, 'Khaled', 3),
(2, 'Ali', 2),
(3, 'John', 1),
(4, 'Doe', 2);

连接两个表：将 Project 表和 Employee 表通过 employee_id 进行连接。
计算每个项目的总工作年限：对每个项目的员工的工作年限求和。
计算每个项目的员工数量：统计每个项目的员工数量。
计算平均工作年限：将总工作年限除以员工数量，并四舍五入到小数点后两位。

select 
    p.project_id,
    round(avg(e.experience_years),2) as average_years
from
    Project p
    left join Employee e
    on p.employee_id=e.employee_id
group by
    p.project_id;

JOIN：我们将 Project 表和 Employee 表通过 employee_id 进行连接。
AVG(e.experience_years)：计算每个项目的员工平均工作年限。
ROUND(…, 2)：将平均工作年限四舍五入到小数点后两位。
GROUP BY p.project_id：按项目分组，以便计算每个项目的平均工作年限。

1633. 各赛事的用户注册率

统计每个赛事的注册用户数量：使用 GROUP BY 和 COUNT 统计每个赛事的注册用户数量。
计算总用户数量：统计 Users 表中的总用户数量。
计算注册百分率：将每个赛事的注册用户数量除以总用户数量，乘以 100 并四舍五入到小数点后两位。
排序结果：按注册百分率降序排序，如果相同则按赛事 ID 升序排序。

-- 创建 Users 表
CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(100)
);

-- 插入数据到 Users 表
INSERT INTO Users (user_id, user_name) VALUES
(6, 'Alice'),
(2, 'Bob'),
(7, 'Alex');

-- 创建 Register 表
CREATE TABLE Register (
    contest_id INT,
    user_id INT,
    PRIMARY KEY (contest_id, user_id)
);

-- 插入数据到 Register 表
INSERT INTO Register (contest_id, user_id) VALUES
(215, 6),
(209, 2),
(208, 2),
(210, 6),
(208, 6),
(209, 7),
(209, 6),
(215, 7),
(208, 7),
(210, 2),
(207, 2),
(210, 7);

select 
    r.contest_id,
    round(count(r.user_id)*100.0/(select count(*) from Users),2) as percentage
from 
    Register r
group by
    r.contest_id
order by 
    percentage DESC,r.contest_id ASC;

COUNT(r.user_id)：统计每个赛事的注册用户数量。
(SELECT COUNT(*) FROM Users)：统计总用户数量。
ROUND((COUNT(r.user_id) * 100.0 / (SELECT COUNT(*) FROM Users)), 2)：计算每个赛事的注册百分率，并四舍五入到小数点后两位。
GROUP BY r.contest_id：按赛事 ID 分组，以便计算每个赛事的注册用户数量。
ORDER BY percentage DESC, r.contest_id ASC：按注册百分率降序排序，如果相同则按赛事 ID 升序排序。

1211. 查询结果的质量和占比

-- 创建 Queries 表
CREATE TABLE Queries (
    query_name VARCHAR(100),
    result VARCHAR(100),
    position INT,
    rating INT
);

-- 插入数据到 Queries 表
INSERT INTO Queries (query_name, result, position, rating) VALUES
('Dog', 'Golden Retriever', 1, 5),
('Dog', 'German Shepherd', 2, 5),
('Dog', 'Mule', 200, 1),
('Cat', 'Shirazi', 5, 2),
('Cat', 'Siamese', 3, 3),
('Cat', 'Sphynx', 7, 4);

计算每个查询的 **quality**：
- quality 定义为各查询结果的评分与其位置之间比率的平均值。
- 对于每个查询结果，计算 rating / position。
- 对于每个 query_name，计算所有查询结果的 rating / position 的平均值。
计算每个查询的 **poor_query_percentage**：
- poor_query_percentage 定义为评分小于 3 的查询结果占全部查询结果的百分比。
- 对于每个 query_name，统计评分小于 3 的查询结果数量。
- 计算评分小于 3 的查询结果数量占总查询结果数量的百分比。
注意where条件，query_name非空。

select
    query_name,
    round(avg(rating/position),2) quality,
    round(sum(if(rating<3,1,0))*100/count(*),2) poor_query_percentage
from Queries
where query_name is not null
group by query_name;

AVG(q.rating / q.position)：计算每个查询结果的 rating / position 的平均值。
SUM(CASE WHEN q.rating < 3 THEN 1 ELSE 0 END)：统计评分小于 3 的查询结果数量。
COUNT(*)：统计每个 query_name 的总查询结果数量。
ROUND(…, 2)：将结果四舍五入到小数点后两位。
GROUP BY q.query_name：按 query_name 分组，以便计算每个查询的 quality 和 poor_query_percentage。

1193. 每月交易 I

1174. 即时食物配送 II

550. 游戏玩法分析 IV

4 排序和分组

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

按教师分组：使用 GROUP BY 按 teacher_id 分组。
统计不同科目数量：使用 COUNT(DISTINCT subject_id) 统计每个教师教授的不同科目数量。

-- 创建 Teacher 表
CREATE TABLE Teacher (
    teacher_id INT,
    subject_id INT,
    dept_id INT,
    PRIMARY KEY (subject_id, dept_id)
);

-- 插入数据到 Teacher 表
INSERT INTO Teacher (teacher_id, subject_id, dept_id) VALUES
(1, 2, 3),
(1, 2, 4),
(1, 3, 3),
(2, 1, 1),
(2, 2, 1),
(2, 3, 1),
(2, 4, 1);

select 
    teacher_id,
    count(distinct subject_id) as cnt
from 
    Teacher
group by
    teacher_id

COUNT(DISTINCT subject_id)：统计每个教师教授的不同科目数量。
GROUP BY teacher_id：按 teacher_id 分组，以便计算每个教师的科目数量。

1141. 查询近30天活跃用户数

-- 创建 Activity 表
CREATE TABLE Activity (
    user_id INT,
    session_id INT,
    activity_date DATE,
    activity_type ENUM('open_session', 'end_session', 'scroll_down', 'send_message')
);

-- 插入数据到 Activity 表
INSERT INTO Activity (user_id, session_id, activity_date, activity_type) VALUES
(1, 1, '2019-07-20', 'open_session'),
(1, 1, '2019-07-20', 'scroll_down'),
(1, 1, '2019-07-20', 'end_session'),
(2, 4, '2019-07-20', 'open_session'),
(2, 4, '2019-07-21', 'send_message'),
(2, 4, '2019-07-21', 'end_session'),
(3, 2, '2019-07-21', 'open_session'),
(3, 2, '2019-07-21', 'send_message'),
(3, 2, '2019-07-21', 'end_session'),
(4, 3, '2019-06-25', 'open_session'),
(4, 3, '2019-06-25', 'end_session');

筛选日期范围：选择 activity_date 在 2019-06-28 到 2019-07-27 之间的记录。
按日期分组：按 activity_date 分组。
统计每日活跃用户数：统计每个日期的唯一 user_id 数量。

select 
    activity_date as day,
    count(distinct user_id) as active_users
from
    Activity
where
    activity_date between '2019-06-28' and '2019-07-27'
group by  
    activity_date;

WHERE activity_date BETWEEN ‘2019-06-28’ AND ‘2019-07-27’：筛选出 activity_date 在 2019-06-28 到 2019-07-27 之间的记录。
COUNT(DISTINCT user_id)：统计每个日期的唯一 user_id 数量。
GROUP BY activity_date：按 activity_date 分组，以便计算每个日期的活跃用户数。

1084. 销售分析 III

-- 创建 Product 表
CREATE TABLE Product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    unit_price INT
);

-- 插入数据到 Product 表
INSERT INTO Product (product_id, product_name, unit_price) VALUES
(1, 'S8', 1000),
(2, 'G4', 800),
(3, 'iPhone', 1400);

-- 创建 Sales 表
CREATE TABLE Sales (
    seller_id INT,
    product_id INT,
    buyer_id INT,
    sale_date DATE,
    quantity INT,
    price INT,
    FOREIGN KEY (product_id) REFERENCES Product(product_id)
);

-- 插入数据到 Sales 表
INSERT INTO Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) VALUES
(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);

筛选销售记录：从 Sales 表中筛选出在 2019 年春季销售的记录。
排除非春季销售的产品：从 Sales 表中找出在 2019 年春季之外销售的记录，并排除这些产品。
获取最终结果：从 Product 表中获取符合条件的产品信息。

select 
    p.product_id,p.product_name
from 
    Product p
where 
    p.product_id not in(
      select distinct s.product_id
      from Sales s
      where s.sale_date <'2019-01-01' or s.sale_date>'2019-03-31' 
    )
            and p.product_id in(
              select distinct s.product_id
              from Sales s
              where s.sale_date between '2019-01-01' and '2019-03-31'
            );

子查询 1：SELECT DISTINCT s.product_id FROM Sales s WHERE s.sale_date < '2019-01-01' OR s.sale_date > '2019-03-31' 用于找出在 2019 年春季之外销售的产品。
子查询 2：SELECT DISTINCT s.product_id FROM Sales s WHERE s.sale_date BETWEEN '2019-01-01' AND '2019-03-31' 用于找出在 2019 年春季销售的产品。
主查询：从 Product 表中选择那些在 2019 年春季销售但不在 2019 年春季之外销售的产品。

596. 超过 5 名学生的课

按班级分组：使用 GROUP BY 按 class 分组。
统计每个班级的学生数量：使用 COUNT 统计每个班级的学生数量。
筛选学生数量大于等于 5 的班级：使用 HAVING 子句筛选出学生数量大于等于 5 的班级。

-- 创建 Courses 表
CREATE TABLE Courses (
    student VARCHAR(100),
    class VARCHAR(100),
    PRIMARY KEY (student, class)
);

-- 插入数据到 Courses 表
INSERT INTO Courses (student, class) VALUES
('A', 'Math'),
('B', 'English'),
('C', 'Math'),
('D', 'Biology'),
('E', 'Math'),
('F', 'Computer'),
('G', 'Math'),
('H', 'Math'),
('I', 'Math');

select 
    class
from
    Courses
group by 
    class
having
    count(student)>=5;

1729. 求关注者的数量

-- 创建 Followers 表
CREATE TABLE Followers (
    user_id INT,
    follower_id INT,
    PRIMARY KEY (user_id, follower_id)
);

-- 插入数据到 Followers 表
INSERT INTO Followers (user_id, follower_id) VALUES
(0, 1),
(1, 0),
(2, 0),
(2, 1);

按用户分组：使用 GROUP BY 按 user_id 分组。
统计每个用户的关注者数量：使用 COUNT 统计每个用户的关注者数量。
按 **user_id** 排序：确保结果按 user_id 的顺序返回。

select 
    user_id,
    count(follower_id) as followers_count
from 
    Followers
group by
    user_id
order by
    user_id;

GROUP BY user_id：按 user_id 分组，以便统计每个用户的关注者数量。
COUNT(follower_id)：统计每个用户的关注者数量。
ORDER BY user_id：按 user_id 的顺序返回结果。

619. 只出现一次的最大数字

-- 创建 MyNumbers 表
CREATE TABLE MyNumbers (
    num INT
);

-- 插入数据到 MyNumbers 表
INSERT INTO MyNumbers (num) VALUES
(8),
(8),
(3),
(3),
(1),
(4),
(5),
(6);

统计每个数字的出现次数：使用 GROUP BY 和 COUNT 统计每个数字的出现次数。
筛选只出现一次的数字：使用 HAVING 子句筛选出出现次数为 1 的数字。
找到最大的单一数字：使用降序排序，限制结果为1，找到最大的单一数字。
确保结果中没有重复项：使用子查询来确保最终结果中只有一个值。

select 
    (select num
    from MyNumbers
    group by num
    having(count(num))=1
    order by num DESC
    limit 1)
as num;

子查询

1.1 FROM MyNumbers

作用：指定查询的数据来源表 MyNumbers。

1.2 GROUP BY num

作用：按 num 列进行分组。这意味着每个不同的 num 值会被单独处理。
结果：生成一个中间结果，其中每个 num 值作为一个组。

1.3 HAVING COUNT(num) = 1

作用：筛选出那些在 MyNumbers 表中只出现一次的 num 值。
条件：COUNT(num) = 1 表示某个 num 值在表中只出现了一次。
结果：生成一个中间结果，其中只包含那些在表中只出现一次的 num 值。

1.4 ORDER BY num DESC

作用：按 num 列的值进行降序排序。
结果：生成一个中间结果，其中只出现一次的 num 值按从大到小的顺序排列。

1.5 LIMIT 1

作用：限制结果集为一条记录，即返回排序后的第一个值。
结果：返回最大的那个只出现一次的 num 值。

原文地址：https://blog.csdn.net/m0_73678713/article/details/144030344

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

SQL注入--理论--堆叠注入
在SQL语句操作的时候，每个语句都是以分号;标志结尾的。在我们注入的过程中，通过，就是堆叠注入。
阅读更多2024-11-26
K8S简介、使用教程
K8S简介、使用教程
阅读更多2024-11-26
Qt中2D绘制系统
Qt 的绘图系统允许使用相同的 API 在屏幕和打印设备上进行绘制。整个绘图系统基于 QPainter，QPainterDevice 和 QPaintEngine 三个类。 QPaint
阅读更多2024-11-26
rust web框架actix和axum比较
rust web框架actix和axum比较
阅读更多2024-11-26
如何打开RAW文件？——详细的工具与方法指南
对于 **摄影师** 或 **图像处理专家**，如果预算允许，**Adobe Photoshop + Camera Raw 插件** 和 **Adobe Lightroom** 是最常见且功能强大的选
阅读更多2024-11-26
数据库约束&表设计
数据库约束是关系型数据库的一个重要功能，主要作用就是保证数据库数据的完整性，也可以理解为数据的正确性（数据本身是否正确，关联关系是否正确）约束一般是指定在列上。
阅读更多2024-11-26
19.QT程序简单的运行脚本
【代码】19.QT程序简单的运行脚本。
阅读更多2024-11-26
转换的艺术：如何在JavaScript中序列化Map为Object及逆向操作
Map 不能直接被 JSON.stringify()序列化，需要先转成对象。因为 JSON.stringify()方法仅能处理纯 JSON 数据结构，而 Map 是一个特殊的集合类型，它不能直接对应于
阅读更多2024-11-26
LeetCode739. 每日温度（2024冬季每日一题 15）
给定一个整数数组 temperatures ，表示每天的温度，返回一个数组 answer ，其中 answer[i] 是指对于第 i 天，下一个更高温度出现在几天后。如果气温在这之后都不会升高，请在该
阅读更多2024-11-26
Gitee markdown 使用方法（持续更新）
与众多代码托管网站一样，Gitee 也是用 Markdown 的文件编辑方式，历经过数次改动，本人用的也是极其不适应，因此，这里打算新开一篇，及时记录 Gitee Markdown 文件的使用方法。首
阅读更多2024-11-26

LeetCode 高频SQL50（基础版）

文章目录

[高频 SQL 50 题（基础版）](https://leetcode.cn/studyplan/sql-free-50/)

1 查询

2 连接

3 聚合函数

**4 排序和分组**

相关文章

4 排序和分组