SELECT activity_date, COUNT(DISTINCT user_id) AS daily_active_users FROM Activity WHERE activity_date BETWEEN DATE_SUB(CURDATE(), INTERVAL29DAY) AND CURDATE() GROUPBY activity_date ORDERBY activity_date;
将日期格式化为特定字符串：
1
SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i:%s') AS formatted_now;
计算用户注册后经过的天数：
1 2 3
SELECT user_id, DATEDIFF(CURDATE(), registration_date) AS days_since_registration FROM Users;
七、性能与注意事项
索引和查询优化：当使用日期列作为过滤条件（如 WHERE activity_date BETWEEN ...）时，确保在该列上有合适的索引以加快查询速度。
函数与索引：在 WHERE 条件中直接对日期列使用函数（如 DATE(activity_datetime) = '2024-12-15'）可能导致索引失效。尽量在查询条件中使用原始列，并在应用层或SELECT列表中使用函数处理结果。
MySQL 本身不直接支持 FULL JOIN（全外连接），但可通过 UNION 合并 LEFT JOIN 和 RIGHT JOIN 的结果来模拟全连接。全连接会返回左右表的全部记录，如果有匹配则合并显示，没有匹配则对应列显示 NULL。
模拟方式：
1 2 3 4 5 6 7
SELECT u.name, o.order_no FROM users u LEFTJOIN orders o ON u.id = o.user_id UNION SELECT u.name, o.order_no FROM users u RIGHTJOIN orders o ON u.id = o.user_id;
虽然有些其他数据库（如 PostgreSQL）直接支持 FULL JOIN，在 MySQL 中则需要这样变通。
CROSS JOIN（交叉连接）
交叉连接不使用 ON 条件，它返回两表的笛卡尔积，即所有可能的组合。通常不直接用于实际业务查询，因为返回的记录数可能非常庞大。
1 2 3
SELECT u.name, o.order_no FROM users u CROSSJOIN orders o;
SELECT u.name, o.order_no, oi.product_name, oi.qty FROM users u INNERJOIN orders o ON u.id = o.user_id INNERJOIN order_items oi ON o.id = oi.order_id WHERE u.name ='Alice';
用法2：DESC 作为 ORDER BY 的修饰符 DESC 可以在 ORDER BY 子句中与 ASC 相对，表示降序排序。 示例：
1
SELECT*FROM users ORDERBY id DESC;
上例会根据 id 列从大到小排列查询结果。
ASC（Ascending）
ASC 是 ORDER BY 子句中用来指定升序排序的关键字，默认情况下，如不指定 ASC 或 DESC，排序即为升序。
示例：
1
SELECT*FROM users ORDERBY name ASC;
此例将根据 name 列从 A 到 Z 排序。
WHERE
WHERE 用于指定过滤条件，从而检索满足特定条件的记录。常用的比较和逻辑运算符包括 =、<>、!=、>、<、>=、<=、AND、OR、NOT 等。
示例：
1
SELECT*FROM users WHERE email ='alice@example.com';
AND、OR、NOT
AND：多个条件均需满足
OR：多个条件任意一个满足即可
NOT：对条件求反
示例：
1 2 3 4 5 6
SELECT*FROM users WHERE name ='Alice'AND email LIKE'%@example.com'; SELECT*FROM users WHERE city ='New York'OR city ='Los Angeles'; SELECT*FROM users WHERENOT (name ='Bob');
LIKE
LIKE 用于字符串的模糊匹配，可结合 %（匹配任意长度的字符）和 _（匹配单个字符）。
示例：
1
SELECT*FROM users WHERE email LIKE'%@example.com';
IN
IN 用于指定多值匹配条件，可以避免使用过多的 OR。
1 2
SELECT*FROM users WHERE city IN ('New York', 'Los Angeles');
BETWEEN … AND …
BETWEEN 用于范围匹配，适用于数字、日期等可比较的范围值。
示例：
1 2
SELECT*FROM orders WHERE total_price BETWEEN100AND500;
该语句会返回 total_price 在 100 至 500（含边界）之间的所有记录。
ALIAS (AS)
AS 用于对表名或列名设置别名，查询结果中显示别名更易读。
示例：
1 2
SELECT name AS username, email AS user_email FROM users;
在联表查询时常用表别名简化书写：
1 2 3
SELECT u.name, o.id AS order_id FROM users AS u JOIN orders AS o ON u.id = o.user_id;
SHOW
SHOW 是一系列用于查看数据库和服务器相关信息的命令：
SHOW DATABASES; 显示所有数据库
SHOW TABLES; 显示当前数据库中的所有数据表
SHOW COLUMNS FROM 表名; 显示表的列结构（与 DESC 表名; 效果类似）
SHOW INDEX FROM 表名; 显示指定表的索引信息
SHOW STATUS; 显示服务器状态信息
SHOW VARIABLES; 显示服务器系统变量信息
UNION
UNION 将两个或多个 SELECT 查询的结果合并成一个结果集，并去掉重复行。
语法：
1 2 3
SELECT 列名 FROM 表1 UNION SELECT 列名 FROM 表2;
如需保留重复结果，可使用 UNION ALL。
JOIN（补充说明）
前文已介绍过 JOIN，这里强调常见类型：
INNER JOIN：只返回两表中匹配的记录
LEFT JOIN：返回左表所有记录及匹配的右表记录
RIGHT JOIN：返回右表所有记录及匹配的左表记录
CROSS JOIN：笛卡尔积，不常用，返回组合结果
CASE
CASE 表达式用于条件逻辑判断，在 SELECT 查询中根据条件返回不同的值。
示例：
1 2 3 4 5 6 7 8
SELECT name, CASE WHEN city ='New York'THEN'East Coast' WHEN city ='Los Angeles'THEN'West Coast' ELSE'Other' ENDAS region FROM users;
IS NULL / IS NOT NULL
用于判断字段值是否为 NULL 或非 NULL。
1 2
sql复制代码SELECT * FROM users WHERE email IS NULL; SELECT * FROM users WHERE email IS NOT NULL;
EXISTS
EXISTS 用于判断子查询是否返回结果，通常和子查询结合使用。
1 2 3 4
sql复制代码SELECT * FROM users WHERE EXISTS ( SELECT 1 FROM orders WHERE orders.user_id = users.id );
如果 orders 表中存在与 users.id 对应的记录，则返回该用户。
LIMIT、OFFSET（再次强调）
LIMIT 用于限制返回的行数，OFFSET 指定起始返回行的偏移量。
1
SELECT*FROM users ORDERBY id ASC LIMIT 10OFFSET20;
+-------------+---------+ | Column Name | Type | +-------------+---------+ | name | varchar | | continent | varchar | | area | int | | population | int | | gdp | bigint | +-------------+---------+ name 是该表的主键（具有唯一值的列）。 这张表的每一行提供：国家名称、所属大陆、面积、人口和 GDP 值。
如果一个国家满足下述两个条件之一，则认为该国是 大国 ：
面积至少为 300 万平方公里（即，3000000 km2），或者
人口至少为 2500 万（即 25000000）
编写解决方案找出 大国 的国家名称、人口和面积。
按 任意顺序 返回结果表。
返回结果格式如下例所示。
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
输入： World 表： +-------------+-----------+---------+------------+--------------+ | name | continent | area | population | gdp | +-------------+-----------+---------+------------+--------------+ | Afghanistan | Asia | 652230 | 25500100 | 20343000000 | | Albania | Europe | 28748 | 2831741 | 12960000000 | | Algeria | Africa | 2381741 | 37100000 | 188681000000 | | Andorra | Europe | 468 | 78115 | 3712000000 | | Angola | Africa | 1246700 | 20609294 | 100990000000 | +-------------+-----------+---------+------------+--------------+ 输出： +-------------+------------+---------+ | name | population | area | +-------------+------------+---------+ | Afghanistan | 25500100 | 652230 | | Algeria | 37100000 | 2381741 | +-------------+------------+---------+
1
select name, population,area from World where area>=3000000or population>=25000000;
+----------------+---------+ | Column Name | Type | +----------------+---------+ | tweet_id | int | | content | varchar | +----------------+---------+ 在 SQL 中，tweet_id 是这个表的主键。 content 只包含美式键盘上的字符，不包含其它特殊字符。 这个表包含某社交媒体 App 中所有的推文。
查询所有无效推文的编号（ID）。当推文内容中的字符数严格大于15 时，该推文是无效的。
以任意顺序返回结果表。
查询结果格式如下所示：
示例 1：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
输入： Tweets 表： +----------+----------------------------------+ | tweet_id | content | +----------+----------------------------------+ | 1 | Vote for Biden | | 2 | Let us make America great again! | +----------+----------------------------------+
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | name | varchar | +---------------+---------+ 在 SQL 中，id 是这张表的主键。 这张表的每一行分别代表了某公司其中一位员工的名字和 ID 。
EmployeeUNI 表：
1 2 3 4 5 6 7 8
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | unique_id | int | +---------------+---------+ 在 SQL 中，(id, unique_id) 是这张表的主键。 这张表的每一行包含了该公司某位员工的 ID 和他的唯一标识码（unique ID）。
展示每位用户的 唯一标识码（unique ID ）；如果某位员工没有唯一标识码，使用 null 填充即可。
select Visits.customer_id, COUNT(*) as count_no_trans from Visits LEFTJOIN Transactions on Visits.visit_id=Transactions.visit_id where Transactions.transaction_id isNULL GROUPBY Visits.customer_id;
select Visits.customer_id, COUNT(*) as count_no_trans from Visits LEFT JOIN Transactions on Visits.visit_id=Transactions.visit_id GROUP BY Visits.visit_id where Visits.transaction_id is NULL; 以上代码错误：
WHERE 子句位置错误： 在 SQL 中，WHERE 子句应放在 GROUP BY 子句之前，而你将它放在了 GROUP BY 子句之后。
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | recordDate | date | | temperature | int | +---------------+---------+ id 是该表具有唯一值的列。 没有具有相同 recordDate 的不同行。 该表包含特定日期的温度信息
WITH ProcessTime AS ( SELECT a.machine_id, a.process_id, b.timestamp - a.timestamp AS time_spent FROM Activity a JOIN Activity b ON a.machine_id = b.machine_id AND a.process_id = b.process_id AND a.activity_type ='start' AND b.activity_type ='end' ) SELECT machine_id, ROUND(AVG(time_spent), 3) AS processing_time FROM ProcessTime GROUPBY machine_id;
自连接：
Activity 表被分成两份，分别用别名 a 和 b 表示。
使用 ON 条件确保连接条件是同一台机器 (machine_id) 和同一个进程 (process_id)。
输入： 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 次。
1 2 3 4 5 6 7 8 9 10 11 12
WITH StudentSubjects AS( select s.student_id, s.student_name, sub.subject_name from Students s CROSSJOIN Subjects sub ) select ss.student_id,ss.student_name,ss.subject_name,COUNT(e.subject_name) as attended_exams from StudentSubjects ss LEFTJOIN Examinations e on ss.student_id=e.student_id AND ss.subject_name=e.subject_name groupby ss.student_id, ss.subject_name orderby ss.student_id, ss.subject_name;
生成所有学生和科目组合：
使用 CROSS JOIN 将 Students 表与 Subjects 表组合，生成每个学生和每个科目的可能组合，命名为 StudentSubjects。
统计每个组合的考试次数：
使用 LEFT JOIN 将 StudentSubjects 和 Examinations 表连接起来。
按学生 ID 和科目名称分组，同时统计匹配的 Examinations.subject_name 出现次数。
确保分组和排序：
GROUP BY 包括 student_id, student_name, 和 subject_name。
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | department | varchar | | managerId | int | +-------------+---------+ id 是此表的主键（具有唯一值的列）。 该表的每一行表示雇员的名字、他们的部门和他们的经理的id。 如果managerId为空，则该员工没有经理。 没有员工会成为自己的管理者。
编写一个解决方案，找出至少有五个直接下属的经理。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
输入: Employee 表: +-----+-------+------------+-----------+ | id | name | department | managerId | +-----+-------+------------+-----------+ | 101 | John | A | Null | | 102 | Dan | A | 101 | | 103 | James | A | 101 | | 104 | Amy | A | 101 | | 105 | Anne | A | 101 | | 106 | Ron | B | 101 | +-----+-------+------------+-----------+ 输出: +------+ | name | +------+ | John | +------+
1 2 3 4 5 6 7 8 9 10 11 12
SELECT e.name FROM Employee m JOIN Employee e ON m.managerId = e.id GROUPBY e.id, e.name HAVING COUNT(m.id) >=5;
JOIN 语句：
通过
1
JOIN
，将
1
Employee
表分成两部分：
m 表：代表员工（managerId 是他们的经理）。
e 表：代表经理（id 是其自身的 ID）。
连接条件是 m.managerId = e.id。
分组统计：
按 e.id（经理 ID）和 e.name（经理姓名）分组。
使用 COUNT(m.id) 统计每个经理的直接下属数量。
筛选条件：
使用 HAVING COUNT(m.id) >= 5 筛选至少有 5 个直接下属的经理。
选择列：
最终查询仅返回满足条件的经理的姓名。
注：
HAVING 子句是 SQL 中用于筛选分组后的数据的一种工具。它与 GROUP BY 一起使用，用于对分组后的数据应用筛选条件。
HAVING 的作用
用于分组后的筛选
在 GROUP BY 之后，SQL 会对数据进行分组并计算聚合函数（如 SUM, COUNT, AVG, 等）的值。
HAVING 子句允许根据这些聚合结果进行筛选。
与 WHERE 不同，HAVING 操作的是分组后的结果，而 WHERE 只能作用于原始数据。
HAVING 的语法
1 2 3 4
SELECT column1, column2, AGGREGATE_FUNCTION(column3) FROM table_name GROUPBY column1, column2 HAVINGcondition;
AGGREGATE_FUNCTION：聚合函数，例如 SUM, AVG, COUNT。
condition：对聚合结果的过滤条件。
HAVING VS WHERE
区别
特性
WHERE
HAVING
作用时机
在数据分组之前过滤记录。
在数据分组之后过滤分组结果。
作用范围
作用于单行数据。
作用于分组或聚合后的结果。
是否支持聚合函数
不支持。
支持。
示例对比
假设有一张表 Sales：
Product
Region
Sales
A
East
100
A
West
200
B
East
150
B
West
300
使用 WHERE：
1 2 3 4 5
-- 筛选 Sales > 100 的行，按 Region 分组后计算总销售额 SELECT Region, SUM(Sales) FROM Sales WHERE Sales >100 GROUPBY Region;
结果：
1 2 3 4 5 6
+--------+-------------+ | Region | SUM(Sales) | +--------+-------------+ | West | 500 | | East | 150 | +--------+-------------+
在原始数据中，过滤掉了 Sales <= 100 的记录。
使用 HAVING：
1 2 3 4 5
-- 按 Region 分组后，筛选总销售额 > 300 的分组 SELECT Region, SUM(Sales) FROM Sales GROUPBY Region HAVINGSUM(Sales) >300;
结果：
1 2 3 4 5
+--------+-------------+ | Region | SUM(Sales) | +--------+-------------+ | West | 500 | +--------+-------------+
原始数据未过滤，分组后筛选出总销售额大于 300 的区域。
HAVING 的应用场景
聚合后数据的筛选：
找出销售额超过一定数值的商品/区域/店铺。
找出参与考试次数超过一定数量的学生。
筛选复杂分组条件：
比如，找出员工人数超过一定数量的部门。
示例 1：筛选总销量超过 500 的商品
1 2 3 4
SELECT Product, SUM(Sales) AS TotalSales FROM Sales GROUPBY Product HAVINGSUM(Sales) >500;
示例 2：筛选至少有 3 名成员的团队
1 2 3 4
SELECT Team, COUNT(MemberId) AS MemberCount FROM Members GROUPBY Team HAVINGCOUNT(MemberId) >=3;
WITH ConfirmationStats AS ( SELECT c.user_id, COUNT(*) AS total_requests, SUM(CASEWHEN c.action ='confirmed'THEN1ELSE0END) AS confirmed_requests FROM Confirmations c GROUPBY c.user_id ) SELECT s.user_id, ROUND(COALESCE(cs.confirmed_requests, 0) /COALESCE(cs.total_requests, 1), 2) AS confirmation_rate FROM Signups s LEFTJOIN ConfirmationStats cs ON s.user_id = cs.user_id;
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | start_date | date | | end_date | date | | price | int | +---------------+---------+ (product_id，start_date，end_date) 是 prices 表的主键（具有唯一值的列的组合）。 prices 表的每一行表示的是某个产品在一段时期内的价格。 每个产品的对应时间段是不会重叠的，这也意味着同一个产品的价格时段不会出现交叉。
表：UnitsSold
1 2 3 4 5 6 7 8 9
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | purchase_date | date | | units | int | +---------------+---------+ 该表可能包含重复数据。 该表的每一行表示的是每种产品的出售日期，单位和产品 id。
WITH Sales AS ( SELECT u.product_id, u.units, p.price, (u.units * p.price) AS total_revenue FROM UnitsSold u JOIN Prices p ON u.product_id = p.product_id AND u.purchase_date BETWEEN p.start_date AND p.end_date ), Aggregated AS ( SELECT product_id, SUM(total_revenue) AS total_revenue, SUM(units) AS total_units FROM Sales GROUPBY product_id ) SELECT p.product_id, ROUND(COALESCE(a.total_revenue / a.total_units, 0), 2) AS average_price FROM (SELECTDISTINCT product_id FROM Prices) p LEFTJOIN Aggregated a ON p.product_id = a.product_id;
SELECT p.project_id, ROUND(AVG(e.experience_years), 2) AS average_years FROM Project p JOIN Employee e ON p.employee_id = e.employee_id GROUPBY p.project_id;
+-------------+---------+ | Column Name | Type | +-------------+---------+ | user_id | int | | user_name | varchar | +-------------+---------+ user_id 是该表的主键(具有唯一值的列)。 该表中的每行包括用户 ID 和用户名。
注册表： Register
1 2 3 4 5 6 7 8
+-------------+---------+ | Column Name | Type | +-------------+---------+ | contest_id | int | | user_id | int | +-------------+---------+ (contest_id, user_id) 是该表的主键(具有唯一值的列的组合)。 该表中的每行包含用户的 ID 和他们注册的赛事。
WITH TotalUsers AS ( SELECTCOUNT(*) AS total_users FROM Users ) SELECT r.contest_id, ROUND(COUNT(DISTINCT r.user_id) *100.0/ tu.total_users, 2) AS percentage FROM Register r CROSSJOIN TotalUsers tu GROUPBY r.contest_id, tu.total_users ORDERBY percentage DESC, contest_id ASC;
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | country | varchar | | state | enum | | amount | int | | trans_date | date | +---------------+---------+ id 是这个表的主键。 该表包含有关传入事务的信息。 state 列类型为 ["approved", "declined"] 之一。
SELECT DATE_FORMAT(trans_date, '%Y-%m') ASmonth, country, COUNT(*) AS trans_count, SUM(CASEWHEN state ='approved'THEN1ELSE0END) AS approved_count, SUM(amount) AS trans_total_amount, SUM(CASEWHEN state ='approved'THEN amount ELSE0END) AS approved_total_amount FROM Transactions GROUPBY DATE_FORMAT(trans_date, '%Y-%m'), country;
WITH FirstOrders AS ( -- 找出每个顾客的首次订单 SELECT* FROM Delivery d WHERE d.order_date = ( SELECTMIN(order_date) FROM Delivery WHERE customer_id = d.customer_id ) ), ImmediateOrders AS ( -- 标记首次订单是否为即时订单 SELECT *, CASE WHEN order_date = customer_pref_delivery_date THEN1 ELSE0 ENDAS is_immediate FROM FirstOrders ) -- 计算即时订单的比例 SELECT ROUND(SUM(is_immediate) *100.0/COUNT(*), 2) AS immediate_percentage FROM ImmediateOrders;
WITH FirstLogin AS ( -- 计算每个玩家的首次登录日期和首次登录的第二天日期 SELECT player_id, MIN(event_date) AS first_login_date, DATE_ADD(MIN(event_date), INTERVAL1DAY) AS second_login_date FROM Activity GROUPBY player_id )
SELECT ROUND(SUM(CASEWHEN a.player_id ISNOTNULLTHEN1ELSE0END) /COUNT(*), 2) AS fraction FROM FirstLogin fl LEFTJOIN Activity a ON fl.player_id = a.player_id AND fl.second_login_date = a.event_date;
WITH Mylist as( selectDISTINCT t.teacher_id, t.subject_id from Teacher t groupby t.teacher_id,t.subject_id ) select m.teacher_id, COUNT(*) as cnt from Mylist m groupby m.teacher_id;
1 2 3 4 5
SELECT teacher_id, COUNT(DISTINCT subject_id) AS cnt FROM Teacher GROUPBY teacher_id;
select a.activity_date asday, COUNT(DISTINCT user_id) as active_users from Activity a WHERE activity_date BETWEEN DATE_SUB('2019-07-27', INTERVAL29DAY) AND'2019-07-27' groupby a.activity_date;
WITH SpringSales AS ( SELECTDISTINCT s.product_id FROM Sales s WHERE s.sale_date BETWEEN'2019-01-01'AND'2019-03-31' ), NonSpringSales AS ( SELECTDISTINCT s.product_id FROM Sales s WHERE s.sale_date <'2019-01-01'OR s.sale_date >'2019-03-31' ) SELECT p.product_id, p.product_name FROM Product p JOIN SpringSales ss ON p.product_id = ss.product_id WHERE p.product_id NOTIN (SELECT product_id FROM NonSpringSales);
输入: 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 个学生，所以我们不包括它。
1 2 3 4
SELECT class FROM Courses GROUPBY class HAVINGCOUNT(student) >=5;
+-------------+------+ | Column Name | Type | +-------------+------+ | num | int | +-------------+------+ 该表可能包含重复项（换句话说，在SQL中，该表没有主键）。 这张表的每一行都含有一个整数。
+-------------+----------+ | Column Name | Type | +-------------+----------+ | employee_id | int | | name | varchar | | reports_to | int | | age | int | +-------------+----------+ employee_id 是这个表中具有不同值的列。 该表包含员工以及需要听取他们汇报的上级经理的 ID 的信息。 有些员工不需要向任何人汇报（reports_to 为空）。
SELECT e.employee_id, e.name, COUNT(r.employee_id) AS reports_count, ROUND(AVG(r.age)) AS average_age FROM Employees e LEFTJOIN Employees r ON e.employee_id = r.reports_to WHERE r.employee_id ISNOTNULL GROUPBY e.employee_id, e.name ORDERBY e.employee_id;
SELECT employee_id, department_id FROM Employee WHERE primary_flag ='Y' OR (employee_id NOTIN (SELECT employee_id FROM Employee WHERE primary_flag ='Y') AND primary_flag ='N');
+-------------+------+ | Column Name | Type | +-------------+------+ | x | int | | y | int | | z | int | +-------------+------+ 在 SQL 中，(x, y, z)是该表的主键列。 该表的每一行包含三个线段的长度。
对每三个线段报告它们是否可以形成一个三角形。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
输入: Triangle 表: +----+----+----+ | x | y | z | +----+----+----+ | 13 | 15 | 30 | | 10 | 20 | 15 | +----+----+----+ 输出: +----+----+----+----------+ | x | y | z | triangle | +----+----+----+----------+ | 13 | 15 | 30 | No | | 10 | 20 | 15 | Yes | +----+----+----+----------+
1 2 3 4 5 6
SELECT x, y, z, CASE WHEN x + y > z AND x + z > y AND y + z > x THEN'Yes' ELSE'No' ENDAS triangle FROM Triangle;
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ 在 SQL 中，id 是该表的主键。 id 是一个自增列。
WITH ConsecutiveLogs AS ( SELECT num, LEAD(num) OVER (ORDERBY id) AS next_num1, LEAD(num, 2) OVER (ORDERBY id) AS next_num2 FROM Logs ) SELECTDISTINCT num AS ConsecutiveNums FROM ConsecutiveLogs WHERE num = next_num1 AND num = next_num2;
WITH ConsecutiveLogs:
使用窗口函数 LEAD，为每条记录计算它的下一个和下两个数字。
这样可以方便地检测连续的三个相同数字。
主查询:
从 ConsecutiveLogs 中筛选出 num 等于 next_num1 且等于 next_num2 的记录，表示该数字至少连续出现三次。
使用 DISTINCT 确保结果中每个数字只出现一次。
LEAD 的语法
LEAD 是一种窗口函数，用于从当前行向后（即下一行）查找某列的值。它非常适合处理序列中后续的值，无需在表中进行多次自连接。
1 2 3 4 5
sql
复制代码 LEAD(column_name, offset, default_value) OVER (PARTITION BY column_name ORDER BY column_name)
sql复制代码WITH ConsecutiveLogs AS ( SELECT id, num, LEAD(num) OVER (ORDER BY id) AS next_num1, LEAD(num, 2) OVER (ORDER BY id) AS next_num2 FROM Logs ) SELECT DISTINCT num FROM ConsecutiveLogs WHERE num = next_num1 AND num = next_num2;
WITH LatestPrices AS ( SELECT product_id, new_price, change_date FROM Products WHERE change_date <='2019-08-16' ), RankedPrices AS ( SELECT product_id, new_price, ROW_NUMBER() OVER (PARTITIONBY product_id ORDERBY change_date DESC) AS rn FROM LatestPrices ) SELECT p.product_id, COALESCE(lp.new_price, 10) AS price FROM (SELECTDISTINCT product_id FROM Products) p LEFTJOIN RankedPrices lp ON p.product_id = lp.product_id AND lp.rn =1;
输入： Queue 表 +-----------+-------------+--------+------+ | person_id | person_name | weight | turn | +-----------+-------------+--------+------+ | 5 | Alice | 250 | 1 | | 4 | Bob | 175 | 5 | | 3 | Alex | 350 | 2 | | 6 | John Cena | 400 | 3 | | 1 | Winston | 500 | 6 | | 2 | Marie | 200 | 4 | +-----------+-------------+--------+------+ 输出： +-------------+ | person_name | +-------------+ | John Cena | +-------------+ 解释： 为了简化，Queue 表按 turn 列由小到大排序。 +------+----+-----------+--------+--------------+ | Turn | ID | Name | Weight | Total Weight | +------+----+-----------+--------+--------------+ | 1 | 5 | Alice | 250 | 250 | | 2 | 3 | Alex | 350 | 600 | | 3 | 6 | John Cena | 400 | 1000 | (最后一个上巴士) | 4 | 2 | Marie | 200 | 1200 | (无法上巴士) | 5 | 4 | Bob | 175 | ___ | | 6 | 1 | Winston | 500 | ___ | +------+----+-----------+--------+--------------+
1 2 3 4 5 6 7 8 9 10
WITH CumulativeWeight AS ( SELECT person_id, person_name, weight, turn, SUM(weight) OVER (ORDERBY turn) AS total_weight FROM Queue ) SELECT person_name FROM CumulativeWeight WHERE total_weight <=1000 ORDERBY total_weight DESC LIMIT 1;
CumulativeWeight 子查询:
使用 SUM(weight) OVER (ORDER BY turn) 计算乘客按 turn 顺序的累积体重。
过滤条件:
筛选 total_weight 小于或等于 1000 的乘客。
主查询:
按累积体重降序排序 (ORDER BY total_weight DESC)，并限制为最后一个符合条件的乘客 (LIMIT 1)。
WITH SalaryCategories AS ( SELECT CASE WHEN income <20000THEN'Low Salary' WHEN income BETWEEN20000AND50000THEN'Average Salary' ELSE'High Salary' ENDAS category FROM Accounts ) SELECT category, COUNT(*) AS accounts_count FROM SalaryCategories GROUPBY category UNIONALL SELECT'Low Salary', 0WHERENOTEXISTS (SELECT1FROM SalaryCategories WHERE category ='Low Salary') UNIONALL SELECT'Average Salary', 0WHERENOTEXISTS (SELECT1FROM SalaryCategories WHERE category ='Average Salary') UNIONALL SELECT'High Salary', 0WHERENOTEXISTS (SELECT1FROM SalaryCategories WHERE category ='High Salary');
WITH MissingManagers AS ( SELECT e.employee_id FROM Employees e LEFTJOIN Employees m ON e.manager_id = m.employee_id WHERE m.employee_id ISNULLAND e.manager_id ISNOTNULL ), LowSalaryEmployees AS ( SELECT employee_id FROM Employees WHERE salary <30000 ) SELECT e.employee_id FROM LowSalaryEmployees e JOIN MissingManagers mm ON e.employee_id = mm.employee_id ORDERBY e.employee_id;
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | student | varchar | +-------------+---------+ id 是该表的主键（唯一值）列。 该表的每一行都表示学生的姓名和 ID。 ID 序列始终从 1 开始并连续增加。
WITH SeatSwap AS ( SELECT CASE WHENMOD(id, 2) =1AND id +1<= (SELECTMAX(id) FROM Seat) THEN id +1 WHENMOD(id, 2) =0THEN id -1 ELSE id ENDAS new_id, student FROM Seat ) SELECT new_id AS id, student FROM SeatSwap ORDERBY id;
+---------------+---------+ | Column Name | Type | +---------------+---------+ | movie_id | int | | title | varchar | +---------------+---------+ movie_id 是这个表的主键(具有唯一值的列)。 title 是电影的名字。
表：Users
1 2 3 4 5 6 7 8
+---------------+---------+ | Column Name | Type | +---------------+---------+ | user_id | int | | name | varchar | +---------------+---------+ user_id 是表的主键(具有唯一值的列)。 'name' 列具有唯一值。
表：MovieRating
1 2 3 4 5 6 7 8 9 10 11
+---------------+---------+ | Column Name | Type | +---------------+---------+ | movie_id | int | | user_id | int | | rating | int | | created_at | date | +---------------+---------+ (movie_id, user_id) 是这个表的主键(具有唯一值的列的组合)。 这个表包含用户在其评论中对电影的评分 rating 。 created_at 是用户的点评日期。
请你编写一个解决方案：
查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。
查找在 February 2020平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。
-- 查找评论电影数量最多的用户名 WITH UserMovieCounts AS ( SELECT u.name, COUNT(mr.movie_id) AS review_count FROM Users u JOIN MovieRating mr ON u.user_id = mr.user_id GROUPBY u.name ), MaxReviewCount AS ( SELECT MAX(review_count) AS max_reviews FROM UserMovieCounts ), TopUsers AS ( SELECT umc.name FROM UserMovieCounts umc JOIN MaxReviewCount mrc ON umc.review_count = mrc.max_reviews ORDERBY umc.name LIMIT 1 )
-- 查找在 2020 年 2 月平均评分最高的电影名称 , FebruaryRatings AS ( SELECT mr.movie_id, AVG(mr.rating) AS avg_rating FROM MovieRating mr WHERE mr.created_at BETWEEN'2020-02-01'AND'2020-02-29' GROUPBY mr.movie_id ), MaxAverageRating AS ( SELECT MAX(avg_rating) AS max_avg_rating FROM FebruaryRatings ), TopMovies AS ( SELECT m.title FROM FebruaryRatings fr JOIN MaxAverageRating mar ON fr.avg_rating = mar.max_avg_rating JOIN Movies m ON fr.movie_id = m.movie_id ORDERBY m.title LIMIT 1 )
-- 返回结果 SELECT tu.name AS results FROM TopUsers tu UNIONALL SELECT tm.title AS results FROM TopMovies tm;
UserMovieCounts: 计算每个用户的点评电影数量。
MaxReviewCount: 找到点评电影数量最多的用户。
TopUsers: 获取点评数量最多且字典序最小的用户名。
FebruaryRatings: 计算2020年2月每部电影的平均评分。
MaxAverageRating: 找到2020年2月平均评分最高的电影。
TopMovies: 获取评分最高且字典序最小的电影名称。
最后通过 UNION ALL 合并结果，并以正确的顺序返回答案。
UNION ALL 是 SQL 中用来将多个查询结果组合在一起的关键字。其作用是将两个或多个结果集合并为一个集合，并包括所有的行（即使存在重复）。
UNION ALL
包含重复行：
不会对结果进行去重。
如果多个查询返回相同的记录，它们会全部出现在最终的结果集中。
效率高：
因为 UNION ALL 不需要对结果集进行去重（不像 UNION），所以在处理大数据量时性能更高。
结果顺序：
合并的结果集不会自动排序。可以通过 ORDER BY 对整个结果进行排序。
使用场景
需要保留重复记录：
如果希望保留所有重复记录（如日志或交易数据的合并），UNION ALL 是合适的选择。
性能优先：
在大规模数据处理中，去重操作可能代价高昂。使用 UNION ALL 可以避免去重的开销，提升查询速度。
示例
假设有两张表 Table1 和 Table2：
数据：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Table1: +----+------+ | ID | Name | +----+------+ | 1 | A | | 2 | B | +----+------+
Table2: +----+------+ | ID | Name | +----+------+ | 2 | B | | 3 | C | +----+------+
查询：
1 2 3
SELECT*FROM Table1 UNIONALL SELECT*FROM Table2;
结果：
1 2 3 4 5 6 7 8
+----+------+ | ID | Name | +----+------+ | 1 | A | | 2 | B | | 2 | B | -- 包含重复行 | 3 | C | +----+------+
对比 UNION
如果使用 UNION：
1 2 3
SELECT*FROM Table1 UNION SELECT*FROM Table2;
结果会去重：
1 2 3 4 5 6 7
+----+------+ | ID | Name | +----+------+ |1| A | |2| B | |3| C | +----+------+
总结
UNION ALL 是 UNION 的高效版本，适用于无需去重的数据合并场景。在合并结果时需明确是否需要去重，合理选择 UNION 或 UNION ALL。
WITH MyClist as( SELECT c.visited_on, SUM(c.amount) as amount FROM Customer c GROUPBY c.visited_on ), SevenDayWindow AS ( SELECT c1.visited_on, SUM(c2.amount) AS total_amount, COUNT(DISTINCT c2.visited_on) AS total_days FROM MyClist c1 JOIN MyClist c2 ON c2.visited_on BETWEEN DATE_SUB(c1.visited_on, INTERVAL6DAY) AND c1.visited_on GROUPBY c1.visited_on HAVING total_days =7-- 确保滚动窗口的天数为 7 ) SELECT visited_on, total_amount AS amount, ROUND(total_amount / total_days, 2) AS average_amount FROM SevenDayWindow ORDERBY visited_on;
+----------------+---------+ | Column Name | Type | +----------------+---------+ | requester_id | int | | accepter_id | int | | accept_date | date | +----------------+---------+ (requester_id, accepter_id) 是这张表的主键(具有唯一值的列的组合)。 这张表包含发送好友请求的人的 ID ，接收好友请求的人的 ID ，以及好友请求通过的日期。
SELECT id, COUNT(*) AS num FROM ( SELECT requester_id AS id FROM RequestAccepted UNIONALL SELECT accepter_id AS id FROM RequestAccepted ) AS combined_ids GROUPBY id ORDERBY num DESC LIMIT 1;
1 2 3 4 5
SELECT requester_id AS id FROM RequestAccepted UNIONALL SELECT accepter_id AS id FROM RequestAccepted
-- 查询符合条件的投保人在2016年的投保金额之和 SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016 FROM Insurance WHERE tiv_2015 IN ( -- 找出在2015年投保金额相同的投保人 SELECT tiv_2015 FROM Insurance GROUPBY tiv_2015 HAVINGCOUNT(*) >1 ) AND (lat, lon) NOTIN ( -- 找出位置重复的城市 SELECT lat, lon FROM Insurance GROUPBY lat, lon HAVINGCOUNT(*) >1 );
第一部分：
子查询 SELECT tiv_2015 FROM Insurance GROUP BY tiv_2015 HAVING COUNT(*) > 1 找出在 tiv_2015 中存在重复值的记录。
第二部分：
子查询 SELECT lat, lon FROM Insurance GROUP BY lat, lon HAVING COUNT(*) > 1 找出重复的 (lat, lon) 对。
+--------------+---------+ | Column Name | Type | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ id 是该表的主键列(具有唯一值的列)。 departmentId 是 Department 表中 ID 的外键（reference 列）。 该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。
表: Department
1 2 3 4 5 6 7 8
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ id 是该表的主键列(具有唯一值的列)。 该表的每一行表示部门ID和部门名。
-- 修改后的查询每个部门收入最高的员工的 SQL 语句 WITH RankedSalaries AS ( SELECT e.id AS employee_id, e.name AS employee_name, e.salary, e.departmentId, d.name AS department_name, DENSE_RANK() OVER (PARTITIONBY e.departmentId ORDERBY e.salary DESC) AS rk FROM Employee e JOIN Department d ON e.departmentId = d.id ) SELECT department_name AS Department, employee_name AS Employee, salary AS Salary FROM RankedSalaries WHERE rk <=3;
+----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | name | varchar | +----------------+---------+ user_id 是该表的主键(具有唯一值的列)。 该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。
编写解决方案，修复名字，使得只有第一个字符是大写的，其余都是小写的。
返回按 user_id 排序的结果表。
返回结果格式示例如下。
示例 1：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
输入： Users table: +---------+-------+ | user_id | name | +---------+-------+ | 1 | aLice | | 2 | bOB | +---------+-------+ 输出： +---------+-------+ | user_id | name | +---------+-------+ | 1 | Alice | | 2 | Bob | +---------+-------+
1 2 3
select user_id, CONCAT(UPPER(SUBSTRING(name, 1, 1)), LOWER(SUBSTRING(name, 2))) as name from Users Orderby user_id ASC;
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | email | varchar | +-------------+---------+ id 是该表的主键列(具有唯一值的列)。 该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。
编写解决方案 删除 所有重复的电子邮件，只保留一个具有最小 id 的唯一电子邮件。
（对于 SQL 用户，请注意你应该编写一个 DELETE 语句而不是 SELECT 语句。）
（对于 Pandas 用户，请注意你应该直接修改 Person 表。）
运行脚本后，显示的答案是 Person 表。驱动程序将首先编译并运行您的代码片段，然后再显示 Person 表。Person 表的最终顺序 无关紧要 。
DELETEFROM Person WHERE id IN ( SELECT id FROM ( SELECT id FROM Person WHERE id NOTIN ( SELECTMIN(id) FROM Person GROUPBY email ) ) AS Temp ); -- 解释： -- 1. 内层子查询 `SELECT MIN(id) FROM Person GROUP BY email` 找到每个唯一 email 的最小 id。 -- 2. 中间子查询生成一个临时表 `Temp`，包含需要删除的 id。 -- 3. 主查询从 `Person` 表中删除这些 id 对应的记录。 -- 这种方式避免了直接在 `DELETE` 中引用目标表的 FROM 子查询问题。
+------------------+---------+ | Column Name | Type | +------------------+---------+ | product_id | int | | product_name | varchar | | product_category | varchar | +------------------+---------+ product_id 是该表主键(具有唯一值的列)。 该表包含该公司产品的数据。
表: Orders
1 2 3 4 5 6 7 8 9 10
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | order_date | date | | unit | int | +---------------+---------+ 该表可能包含重复行。 product_id 是表单 Products 的外键（reference 列）。 unit 是在日期 order_date 内下单产品的数目。
SELECT p.product_name, SUM(o.unit) AS unit FROM Products p JOIN Orders o ON p.product_id = o.product_id WHERE DATE_FORMAT(o.order_date, '%Y-%m') ='2020-02' GROUPBY p.product_id, p.product_name HAVING SUM(o.unit) >=100;
