深入学习SQL优化的第三天

目录

聚合函数

排序和分组


聚合函数

1251. 平均售价

表:Prices+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| start_date    | date    |
| end_date      | date    |
| price         | int     |
+---------------+---------+
(product_id,start_date,end_date) 是 prices 表的主键(具有唯一值的列的组合)。
prices 表的每一行表示的是某个产品在一段时期内的价格。
每个产品的对应时间段是不会重叠的,这也意味着同一个产品的价格时段不会出现交叉。表:UnitsSold+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| purchase_date | date    |
| units         | int     |
+---------------+---------+
该表可能包含重复数据。
该表的每一行表示的是每种产品的出售日期,单位和产品 id。编写解决方案以查找每种产品的平均售价。average_price 应该 四舍五入到小数点后两位。返回结果表 无顺序要求 。结果格式如下例所示。示例 1:输入:
Prices table:
+------------+------------+------------+--------+
| product_id | start_date | end_date   | price  |
+------------+------------+------------+--------+
| 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 table:
+------------+---------------+-------+
| product_id | purchase_date | units |
+------------+---------------+-------+
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |
+------------+---------------+-------+
输出:
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+
解释:
平均售价 = 产品总价 / 销售的产品数量。
产品 1 的平均售价 = ((100 * 5)+(15 * 20) )/ 115 = 6.96
产品 2 的平均售价 = ((200 * 15)+(30 * 30) )/ 230 = 16.96
SELECT p.product_id,  
ROUND(COALESCE(SUM(u.units * p.price) / NULLIF(SUM(u.units), 0), 0), 2) 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 p.product_id;
其中:SUM() 是一个聚合函数,用于计算一组值的总和。在这里,它被用来计算每个产品的总销售额(u.units * p.price)和总销售数量(u.units)。NULLIF() 函数接受两个参数,如果这两个参数相等,则返回 NULL;否则,返回第一个参数的值。在这里,它被用来避免除以零的错误,如果 SUM(u.units) 为零,则 NULLIF 会将其转换为 NULL。COALESCE() 函数接受多个参数,并返回参数列表中的第一个非 NULL 值。在这里,它被用来处理可能的 NULL 除数(由 NULLIF() 产生),如果除数为 NULL,则 COALESCE() 会将其替换为零。ROUND() 函数用于将数值四舍五入到指定的小数位数。在这里,它被用来将计算出的平均价格四舍五入到小数点后两位。

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
SELECT p.project_id,
ROUND(SUM(e.experience_years) / COUNT(*),2)
AS average_years
FROM Project p
LEFT JOIN Employee e
ON p.employee_id = e.employee_id
GROUP BY p.project_id
;

这道题就比较简单了,做完前面的几道例题一下子就可以把这道题也给写出来了。

1633. 各赛事的用户注册率

用户表: Users+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| user_name   | varchar |
+-------------+---------+
user_id 是该表的主键(具有唯一值的列)。
该表中的每行包括用户 ID 和用户名。注册表: Register+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| contest_id  | int     |
| user_id     | int     |
+-------------+---------+
(contest_id, user_id) 是该表的主键(具有唯一值的列的组合)。
该表中的每行包含用户的 ID 和他们注册的赛事。编写解决方案统计出各赛事的用户注册百分率,保留两位小数。返回的结果表按 percentage 的 降序 排序,若相同则按 contest_id 的 升序 排序。返回结果如下示例所示。示例 1:输入:
Users 表:
+---------+-----------+
| user_id | user_name |
+---------+-----------+
| 6       | Alice     |
| 2       | Bob       |
| 7       | Alex      |
+---------+-----------+Register 表:
+------------+---------+
| contest_id | user_id |
+------------+---------+
| 215        | 6       |
| 209        | 2       |
| 208        | 2       |
| 210        | 6       |
| 208        | 6       |
| 209        | 7       |
| 209        | 6       |
| 215        | 7       |
| 208        | 7       |
| 210        | 2       |
| 207        | 2       |
| 210        | 7       |
+------------+---------+
输出:
+------------+------------+
| contest_id | percentage |
+------------+------------+
| 208        | 100.0      |
| 209        | 100.0      |
| 210        | 100.0      |
| 215        | 66.67      |
| 207        | 33.33      |
+------------+------------+
解释:
所有用户都注册了 208、209 和 210 赛事,因此这些赛事的注册率为 100% ,我们按 contest_id 的降序排序加入结果表中。
Alice 和 Alex 注册了 215 赛事,注册率为 ((2/3) * 100) = 66.67%
Bob 注册了 207 赛事,注册率为 ((1/3) * 100) = 33.33%
SELECT r.contest_id,
ROUND((COUNT(r.user_id) / (SELECT COUNT(*) FROM Users)) * 100, 2)
AS percentage
FROM Register r
GROUP BY r.contest_id
ORDER BY percentage DESC,r.contest_id ASC
;

这个不需要用到左连接类的,因为

注册率的计算,只需要在注册表上操作,后面的总人数直接去查询用户表里的数据总条数即可。

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

Queries 表: +-------------+---------+
| Column Name | Type    |
+-------------+---------+
| query_name  | varchar |
| result      | varchar |
| position    | int     |
| rating      | int     |
+-------------+---------+
此表可能有重复的行。
此表包含了一些从数据库中收集的查询信息。
“位置”(position)列的值为 1 到 500 。
“评分”(rating)列的值为 1 到 5 。评分小于 3 的查询被定义为质量很差的查询。将查询结果的质量 quality 定义为:各查询结果的评分与其位置之间比率的平均值。将劣质查询百分比 poor_query_percentage 为:评分小于 3 的查询结果占全部查询结果的百分比。编写解决方案,找出每次的 query_name 、 quality 和 poor_query_percentage。quality 和 poor_query_percentage 都应 四舍五入到小数点后两位 。以 任意顺序 返回结果表。结果格式如下所示:示例 1:输入:
Queries table:
+------------+-------------------+----------+--------+
| query_name | result            | position | rating |
+------------+-------------------+----------+--------+
| 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      |
+------------+-------------------+----------+--------+
输出:
+------------+---------+-----------------------+
| query_name | quality | poor_query_percentage |
+------------+---------+-----------------------+
| Dog        | 2.50    | 33.33                 |
| Cat        | 0.66    | 33.33                 |
+------------+---------+-----------------------+
解释:
Dog 查询结果的质量为 ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50
Dog 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33Cat 查询结果的质量为 ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66
Cat 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33
SELECT query_name,
ROUND(AVG(rating / position), 2)
AS quality,
ROUND(AVG(rating < 3) * 100, 2)
AS poor_query_percentage
FROM Queries
WHERE query_name IS NOT NULL
GROUP BY query_name
;

1193. 每月交易 I

表:Transactions+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| country       | varchar |
| state         | enum    |
| amount        | int     |
| trans_date    | date    |
+---------------+---------+
id 是这个表的主键。
该表包含有关传入事务的信息。
state 列类型为 ["approved", "declined"] 之一。编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。以 任意顺序 返回结果表。查询结果格式如下所示。示例 1:输入:
Transactions table:
+------+---------+----------+--------+------------+
| id   | country | state    | amount | trans_date |
+------+---------+----------+--------+------------+
| 121  | US      | approved | 1000   | 2018-12-18 |
| 122  | US      | declined | 2000   | 2018-12-19 |
| 123  | US      | approved | 2000   | 2019-01-01 |
| 124  | DE      | approved | 2000   | 2019-01-07 |
+------+---------+----------+--------+------------+
输出:
+----------+---------+-------------+----------------+--------------------+-----------------------+
| month    | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 2018-12  | US      | 2           | 1              | 3000               | 1000                  |
| 2019-01  | US      | 1           | 1              | 2000               | 2000                  |
| 2019-01  | DE      | 1           | 1              | 2000               | 2000                  |
+----------+---------+-------------+----------------+--------------------+-----------------------+
SELECT 
DATE_FORMAT(trans_date,'%Y-%m') AS month,
country,
COUNT(*) AS trans_count,
SUM(IF(state = 'approved',1,0)) AS approved_count,
SUM(amount) AS trans_total_amount,
SUM(IF(state = 'approved',amount,0)) AS approved_total_amount
FROM Transactions
GROUP BY month,country

1174. 即时食物配送 II

配送表: Delivery+-----------------------------+---------+
| Column Name                 | Type    |
+-----------------------------+---------+
| delivery_id                 | int     |
| customer_id                 | int     |
| order_date                  | date    |
| customer_pref_delivery_date | date    |
+-----------------------------+---------+
delivery_id 是该表中具有唯一值的列。
该表保存着顾客的食物配送信息,顾客在某个日期下了订单,并指定了一个期望的配送日期(和下单日期相同或者在那之后)。如果顾客期望的配送日期和下单日期相同,则该订单称为 「即时订单」,否则称为「计划订单」。「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。编写解决方案以获取即时订单在所有用户的首次订单中的比例。保留两位小数。结果示例如下所示:示例 1:输入:
Delivery 表:
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1           | 1           | 2019-08-01 | 2019-08-02                  |
| 2           | 2           | 2019-08-02 | 2019-08-02                  |
| 3           | 1           | 2019-08-11 | 2019-08-12                  |
| 4           | 3           | 2019-08-24 | 2019-08-24                  |
| 5           | 3           | 2019-08-21 | 2019-08-22                  |
| 6           | 2           | 2019-08-11 | 2019-08-13                  |
| 7           | 4           | 2019-08-09 | 2019-08-09                  |
+-------------+-------------+------------+-----------------------------+
输出:
+----------------------+
| immediate_percentage |
+----------------------+
| 50.00                |
+----------------------+
解释:
1 号顾客的 1 号订单是首次订单,并且是计划订单。
2 号顾客的 2 号订单是首次订单,并且是即时订单。
3 号顾客的 5 号订单是首次订单,并且是计划订单。
4 号顾客的 7 号订单是首次订单,并且是即时订单。
因此,一半顾客的首次订单是即时的。

SELECT
ROUND(SUM(order_date = customer_pref_delivery_date) / COUNT(*) * 100, 2)
AS immediate_percentage
FROM Delivery
WHERE (customer_id,order_date) 
IN(SELECT customer_id,MIN(order_date) FROM Delivery GROUP BY customer_id)
;

550. 游戏玩法分析 IV

Table: Activity+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
(player_id,event_date)是此表的主键(具有唯一值的列的组合)。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录,他在某一天使用某个设备注销之前登录并玩了很多游戏(可能是 0)。编写解决方案,报告在首次登录的第二天再次登录的玩家的 比率,四舍五入到小数点后两位。换句话说,你需要计算从首次登录日期开始至少连续两天登录的玩家的数量,然后除以玩家总数。结果格式如下所示:示例 1:输入:
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-03-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
输出:
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
解释:
只有 ID 为 1 的玩家在第一天登录后才重新登录,所以答案是 1/3 = 0.33

SELECT ROUND(COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) 
AS fraction
FROM Activity
WHERE (player_id, event_date) IN (SELECT player_id, MIN(event_date) + INTERVAL 1 DAYFROM ActivityGROUP BY player_id
);

其中,COUNT(DISTINCT player_id)与(SELECT COUNT(DISTINCT player_id)的区别在于第一个

查询是出现在了上层的查询中,满足的是外层 where 子句后的处理过的数量。

而第二个计算的是整个表的去重的数量。

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。

排序和分组

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。
SELECT teacher_id,
COUNT(DISTINCT subject_id) 
AS cnt
FROM Teacher
GROUP BY teacher_id
;

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

表:Activity+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| session_id    | int     |
| activity_date | date    |
| activity_type | enum    |
+---------------+---------+
该表没有包含重复数据。
activity_type 列是 ENUM(category) 类型, 从 ('open_session', 'end_session', 'scroll_down', 'send_message') 取值。
该表记录社交媒体网站的用户活动。
注意,每个会话只属于一个用户。编写解决方案,统计截至 2019-07-27(包含2019-07-27),近 30 天的每日活跃用户数(当天只要有一条活动记录,即为活跃用户)。以 任意顺序 返回结果表。结果示例如下。示例 1:输入:
Activity table:
+---------+------------+---------------+---------------+
| user_id | session_id | activity_date | activity_type |
+---------+------------+---------------+---------------+
| 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   |
+---------+------------+---------------+---------------+
输出:
+------------+--------------+ 
| day        | active_users |
+------------+--------------+ 
| 2019-07-20 | 2            |
| 2019-07-21 | 2            |
+------------+--------------+ 
解释:注意非活跃用户的记录不需要展示。
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
;

1084. 销售分析III

表: Product+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
| unit_price   | int     |
+--------------+---------+
product_id 是该表的主键(具有唯一值的列)。
该表的每一行显示每个产品的名称和价格。
表:Sales+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+------ ------+---------+
这个表可能有重复的行。
product_id 是 Product 表的外键(reference 列)。
该表的每一行包含关于一个销售的一些信息。编写解决方案,报告 2019年春季 才售出的产品。即 仅 在 2019-01-01 (含)至 2019-03-31 (含)之间出售的商品。以 任意顺序 返回结果表。结果格式如下所示。示例 1:输入:
Product table:
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1          | S8           | 1000       |
| 2          | G4           | 800        |
| 3          | iPhone       | 1400       |
+------------+--------------+------------+
Sales table:
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date  | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1         | 1          | 1        | 2019-01-21 | 2        | 2000  |
| 1         | 2          | 2        | 2019-02-17 | 1        | 800   |
| 2         | 2          | 3        | 2019-06-02 | 1        | 800   |
| 3         | 3          | 4        | 2019-05-13 | 2        | 2800  |
+-----------+------------+----------+------------+----------+-------+
输出:
+-------------+--------------+
| product_id  | product_name |
+-------------+--------------+
| 1           | S8           |
+-------------+--------------+
解释:
id 为 1 的产品仅在 2019 年春季销售。
id 为 2 的产品在 2019 年春季销售,但也在 2019 年春季之后销售。
id 为 3 的产品在 2019 年春季之后销售。
我们只返回 id 为 1 的产品,因为它是 2019 年春季才销售的产品。
SELECT p.product_id, p.product_name
FROM Product p
LEFT JOIN Sales s
ON p.product_id = s.product_id
GROUP BY p.product_id
HAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) <= '2019-03-31'
;

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个学生,所以我们不包括它。
SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(student) >= 5
;

1729. 求关注者的数量

表: Followers+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id     | int  |
| follower_id | int  |
+-------------+------+
(user_id, follower_id) 是这个表的主键(具有唯一值的列的组合)。
该表包含一个关注关系中关注者和用户的编号,其中关注者关注用户。编写解决方案,对于每一个用户,返回该用户的关注者数量。按 user_id 的顺序返回结果表。查询结果的格式如下示例所示。示例 1:输入:
Followers 表:
+---------+-------------+
| user_id | follower_id |
+---------+-------------+
| 0       | 1           |
| 1       | 0           |
| 2       | 0           |
| 2       | 1           |
+---------+-------------+
输出:
+---------+----------------+
| user_id | followers_count|
+---------+----------------+
| 0       | 1              |
| 1       | 1              |
| 2       | 2              |
+---------+----------------+
解释:
0 的关注者有 {1}
1 的关注者有 {0}
2 的关注者有 {0,1}
SELECT user_id,
COUNT(*) AS followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id
;

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

MyNumbers 表:+-------------+------+
| Column Name | Type |
+-------------+------+
| num         | int  |
+-------------+------+
该表可能包含重复项(换句话说,在SQL中,该表没有主键)。
这张表的每一行都含有一个整数。单一数字 是在 MyNumbers 表中只出现一次的数字。找出最大的 单一数字 。如果不存在 单一数字 ,则返回 null 。查询结果如下例所示。示例 1:输入:
MyNumbers 表:
+-----+
| num |
+-----+
| 8   |
| 8   |
| 3   |
| 3   |
| 1   |
| 4   |
| 5   |
| 6   |
+-----+
输出:
+-----+
| num |
+-----+
| 6   |
+-----+
解释:单一数字有 1、4、5 和 6 。
6 是最大的单一数字,返回 6 。
示例 2:输入:
MyNumbers table:
+-----+
| num |
+-----+
| 8   |
| 8   |
| 7   |
| 7   |
| 3   |
| 3   |
| 3   |
+-----+
输出:
+------+
| num  |
+------+
| null |
+------+
解释:输入的表中不存在单一数字,所以返回 null 。
SELECT(SELECT numFROM MyNumbersGROUP BY numHAVING COUNT(num)=1ORDER BY num DESCLIMIT 1
)
AS num
;

1045. 买下所有产品的客户

Customer 表:+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| customer_id | int     |
| product_key | int     |
+-------------+---------+
该表可能包含重复的行。
customer_id 不为 NULL。
product_key 是 Product 表的外键(reference 列)。
Product 表:+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_key | int     |
+-------------+---------+
product_key 是这张表的主键(具有唯一值的列)。编写解决方案,报告 Customer 表中购买了 Product 表中所有产品的客户的 id。返回结果表 无顺序要求 。返回结果格式如下所示。示例 1:输入:
Customer 表:
+-------------+-------------+
| customer_id | product_key |
+-------------+-------------+
| 1           | 5           |
| 2           | 6           |
| 3           | 5           |
| 3           | 6           |
| 1           | 6           |
+-------------+-------------+
Product 表:
+-------------+
| product_key |
+-------------+
| 5           |
| 6           |
+-------------+
输出:
+-------------+
| customer_id |
+-------------+
| 1           |
| 3           |
+-------------+
解释:
购买了所有产品(5 和 6)的客户的 id 是 1 和 3 。

我们可能会想到使用:

SELECT customer_id
FROM Customer
WHERE COUNT(*) = (SELECT COUNT(*) FROM Product)
GROUP BY customer_id
;

但是这个却会报错,因为:

  1. WHERE子句:在数据分组前对行进行过滤。它不能用于聚合函数,因为WHERE在数据分组和聚合计算之前执行。

  2. HAVING子句:在数据分组和聚合计算后对分组进行过滤。它可以使用聚合函数,因为它是在分组和计算聚合之后应用的。

所以:

SELECT customer_id
FROM Customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(*)FROM Product
)

近日总结:这实习谁爱呆谁呆吧,学不到东西,天天闲的离谱,需求少得可怜,还要被导师针对,无语

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

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

相关文章

K8S - Java微服务配置 - 使用ConfigMap配置redis

参考文档&#xff1a;https://v1-27.docs.kubernetes.io/zh-cn/docs/tutorials/configuration/configure-redis-using-configmap/ cat <<EOF >./example-redis-config.yaml apiVersion: v1 kind: ConfigMap metadata:name: example-redis-config data:redis-config: …

“解决Windows电脑无法投影到其他屏幕的问题:尝试更新驱动程序或更换视频卡“

目录 背景: 解决方法1: 解决方法2: 什么是驱动程序&#xff1a; 背景: 今天在日常的工作中&#xff0c; 我想将笔记本分屏到另一个显示屏&#xff0c;我这电脑Windows10系统&#xff0c;当我按下Windows键P键&#xff0c;屏幕信息上提示我"你的电脑不能投影到其他屏幕…

C++:继承(protected、隐藏、不能被继承的类、)

目录 继承的概念 继承的使用 继承方式 protected 继承类模板 赋值兼容转换 隐藏 子类的默认成员函数 构造函数 拷贝构造函数 赋值重载函数 析构函数 不能被继承的类 方法1&#xff1a;父类的构造函数私有 方法2&#xff1a;final 继承与友元 继承与静态成员 …

Redis 键值型数据库

一、Redis是什么 Redis&#xff1a;REmote DIctionary Server&#xff08;远程字典服务器&#xff09; 是完全开源免费的&#xff0c;用C语言编写的&#xff0c;遵守BSD协议&#xff0c;是一个高性能的&#xff08;Key/Value&#xff09;分布式内存数据 库&#xff0c;基于内存…

tcp 和udp通信

一.recvfrom recvfrom函数是一个系统调用&#xff0c;用于从套接字接收数据。该函数通常与无连接的数据报服务&#xff08;如 UDP&#xff09;一起使用&#xff0c;但也可以与其他类型的套接字使用。与简单的 recv() 函数不同&#xff0c;recvfrom() 可以返回数据来源的地址信息…

FreeRTOS学习:内存管理

FreeRTOS内存管理简介 在使用 FreeRTOS 创建任务、队列、信号量等对象的时候&#xff0c; FreeRTOS 一般都提供了两种方法&#xff0c; 动态方法创建&#xff1a;自动地从 FreeRTOS 管理的内存堆中申请所创建对象所需的内存&#xff0c;在对象被删除后&#xff0c;又可以将这…

Vue3开始

1.创建 Vue3 工程 1.基于 vue-cli创建 2.基于vite创建工程 Home | Vite中文网 (vitejs.cn) nodejs 环境可以 使用 nvm 来管理 nodejs 的版本 3.开始创建 4.目录结构 5.创建完 执行 安装依赖 npm i 6.运行项目 为什么命令是 dev 就是从这里配置的 npm run dev

大白话社融中M1和M2的学习笔记

一、背景 为什么我看社融数据呢&#xff1f;因为作为一个码农&#xff0c;我不清楚当前个人所处的样本情况是否真实。我所处的环境是在深圳南山&#xff0c;身边一些同事有不少找工作不怎么顺利&#xff0c;我所在的公司今年也没招大学生了&#xff0c;人员也只出不进为主&…

本地生活服务商系统如何利用本地推获得更多曝光?

随着本地生活赛道中的竞争愈演愈烈&#xff0c;越来越多的本地生活服务商和本地生活商家开始计划着通过在本地推等平台投放相关信息&#xff0c;以提高品牌店铺的曝光量和知名度。不过&#xff0c;就目前的情况来看&#xff0c;绝大多数人都陷入了一种“投入多&#xff0c;转化…

教育部-华为产学合作协同育人项目 | 仓颉编程语言专项

为响应《教育部高等教育司关于调整产学合作协同育人项目运行模式及征集2024年产学合作协同育人项目的通知》号召&#xff0c;华为公司2024年第二批70个项目已发布&#xff0c;其中仓颉编程语言领域共计10个项目&#xff0c;如下所示&#xff0c;通过新工科建设项目&#xff0c;…

U盘安装Ubuntu24.04,乌邦图,UltralISO

文章目录 前言通过UltraISO&#xff0c;制作启动U盘下载镜像制作工具UltraISO(软碟通)下载ubuntu镜像文件制作启动U盘 安装ubuntu设置root密码&#xff0c;并登陆root 前言 在Ubuntu作为主流的linux系统&#xff0c;有时候使用VMware安装使用&#xff0c;总归有一定的性能损耗…

力扣每日一题 数组最后一个元素的最小值 位运算

Problem: 3133. 数组最后一个元素的最小值 &#x1f468;‍&#x1f3eb; 灵神题解 class Solution {public long minEnd(int n, int x) {n--; // 先把 n 减一&#xff0c;这样下面讨论的 n 就是原来的 n-1long ans x;int i 0;int j 0;while((n >> j) > 0){// …

springboot集成海康sdk,针对视频流获取某一点的实时温度

直接上代码吧: 前端页面专递点的x和y的坐标及其设备的ip @RequestMapping(value = "/getRealTemperatureByPoint") public float getRealTemperatureByPoint(HttpServletRequest request) {Map<String, Object> params = ParamUtil.getParams(request);Strin…

小区社区超市商城停车场管理系统-计算机毕设Java|springboot实战项目

&#x1f34a;作者&#xff1a;计算机毕设匠心工作室 &#x1f34a;简介&#xff1a;毕业后就一直专业从事计算机软件程序开发&#xff0c;至今也有8年工作经验。擅长Java、Python、微信小程序、安卓、大数据、PHP、.NET|C#、Golang等。 擅长&#xff1a;按照需求定制化开发项目…

Linux 信号 signal,sigaction,sigqueue,kill,相关函数

驱动与应用的结合&#xff0c;参考我的这篇&#xff1a;https://blog.csdn.net/rjszcb/article/details/113573517 一、 什么是信号 信号是软中断,用于通知进程某个事件已经发生。进程可以选择如何响应信号:忽略、默认处理、自定义处理等。 常见信号有:SIGINT(键盘中断)、SIGK…

C语言程序设计-练习篇

华夏波澜壮阔&#xff0c;少年仍需前行。 十&#xff0c;实现一个函数&#xff0c;打印乘法口诀表&#xff0c;口诀表的行数和列数自己指定 #include <stdio.h> //实现一个函数&#xff0c;打印乘法口诀表&#xff0c;口诀表的行数和列数自己指定 void print_table(int …

启动docker镜像

1、运行容器 2、当前运行的进程 3、当前位置和启动时间 4、cat/etc/redhat-release查看版本 5.镜像是模版&#xff0c;容器是实例 6.容器中没有命令运 7.容器总是能轻易获取 8.配置yum 9.安装http 10.修改index⽂件 11.httpd -k start 12.访问 13.退出就没有服务了 14…

Redis配置及idea部分操作

配置Redis远程访问 修改访问IP地址 #跳转到redis安装目录 cd /usr/local/redis-6.2.1 #修改redis.conf配置文件 vi redis.conf #注释redis.conf第69行的配置项 #bind 127.0.0.1 设置登录密码 找到下面这一行并去除注释&#xff0c;并添加密…

【微服务】SpringCloud Alibaba 10-14章

10 SpringCloud Alibaba入门简介 10.1 是什么 诞生 2018.10.31&#xff0c;Spring Cloud Alibaba 正式入驻了 Spring Cloud 官方孵化器&#xff0c;并在 Maven 中央库发布了第一个版本。 Spring Cloud Alibaba 介绍 10.2 能干嘛 https://github.com/alibaba/spring-cloud-al…

git常用操作合集

1 撤销 1.1 适用场景 如果在git上提交了commit&#xff0c;但是当前提交的代码有问题&#xff0c;需要回退到上个版本 1.2 操作命令 1、git log 查看历史提交记录及对应的commit id 找到需要回退的commit id 2、执行git reset回退到之前的状态 git reset --hard <commi…