当前位置：首页 > backend >正文

SQL练习（6/81）

backend 2025/5/17 7:19:11

1.寻找连续值

方法一：使用自连接（Self-Join）

方法二：使用窗口函数（Window Functions）

2.寻找有重复的值

GROUP BY子句

HAVING子句

常用聚合函数：

3.找不存在某属性的值

not in

not exist

性能比较

使用场景

1.寻找连续值

方法一：使用自连接（Self-Join）

select distinct l1.num as ConsecutiveNums
from logs l1
join logs l2 on l1.id = l2.id - 1 and l1.num = l2.num
join logs l3 on l2.id = l3.id - 1 and l2.num = l3.num

自连接（Self-Join）
- 自连接是指将同一张表连接到自身。通过为同一张表赋予不同的别名（如l1、l2、l3），可以将表中的行与其他行进行比较。
- 在这个例子中，l1、l2和l3分别代表logs表中的不同行，通过id的偏移量来确定它们之间的顺序关系。
连接条件（Join Conditions）
- l1.id = l2.id - 1：表示l2的id比l1的id大1，即l2是l1的下一行。
- l1.num = l2.num：表示l1和l2的num值相同。
- l2.id = l3.id - 1：表示l3的id比l2的id大1，即l3是l2的下一行。
- l2.num = l3.num：表示l2和l3的num值相同。
- 通过这些条件，确保了l1、l2和l3是连续的三行，并且它们的num值相同。
DISTINCT关键字
- DISTINCT用于去除结果中的重复行，确保输出的ConsecutiveNums是唯一的。

方法二：使用窗口函数（Window Functions）

select distinct num as ConsecutiveNums
from (select num,LAG(num,1) over(order by id ASC) as pre1,LAG(num,2) over(order by id ASC) as pre2,LEAD(num,1) over(order by id ASC) as post1,LEAD(num,2) over(order by id ASC) as post2from logs
) AS subquery
where (pre2 = pre1 and pre1 = num)or (pre1 = num and num = post1)or (num = post1 and post1 = post2)

窗口函数（Window Functions）
- 窗口函数允许在结果集中对每一行进行计算，同时考虑其他行的值。LAG和LEAD是两种常用的窗口函数。
- LAG(num,1) over(order by id ASC) as pre1：获取当前行的前一行的num值。
- LAG(num,2) over(order by id ASC) as pre2：获取当前行的前两行的num值。
- LEAD(num,1) over(order by id ASC) as post1：获取当前行的下一行的num值。
- LEAD(num,2) over(order by id ASC) as post2：获取当前行的下两行的num值。
子查询（Subquery）
- 子查询用于生成一个临时表（subquery），其中包含了原始表中的num值以及通过窗口函数计算出的前后行的num值。
WHERE子句
- WHERE子句用于筛选出满足连续三次相同数字的行：
  - (pre2 = pre1 and pre1 = num)：当前行的num值与其前两行的num值相同。
  - (pre1 = num and num = post1)：当前行的num值与其前一行和下一行的num值相同。
  - (num = post1 and post1 = post2)：当前行的num值与其下一行和下两行的num值相同。
DISTINCT关键字
- DISTINCT用于去除结果中的重复行，确保输出的ConsecutiveNums是唯一的。

180. 连续出现的数字 - 力扣（LeetCode）

2.寻找有重复的值

SELECT DISTINCT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(email) > 1;

GROUP BY子句

GROUP BY email：
- GROUP BY用于将结果集按一个或多个列分组。这里按email列分组，将具有相同email值的行归为一组。
- 分组后，每个email值只会出现一次，便于后续的聚合操作。

HAVING子句

HAVING COUNT(email) > 1：
- HAVING用于对分组后的结果进行筛选，类似于WHERE子句，但HAVING用于筛选分组后的聚合结果。
- COUNT(email)：计算每个分组中的行数，即每个email值出现的次数。
- HAVING COUNT(email) > 1：筛选出出现次数大于1的email值，即找出重复的email

常用聚合函数：

COUNT：计算某个列中非NULL值的数量，或使用COUNT(*)计算表中的总行数。
SUM：计算数值列的总和，仅适用于数值类型的列。
AVG：计算数值列的平均值，仅适用于数值类型的列。
MAX：找出某个列中的最大值，适用于数值列或字符串列。
MIN：找出某个列中的最小值，适用于数值列或字符串列。
COUNT(DISTINCT)：计算某个列中唯一值的数量，通过DISTINCT去除重复值后计数。
SUM(DISTINCT)：计算某个列中唯一值的总和，通过DISTINCT去除重复值后求和。
AVG(DISTINCT)：计算某个列中唯一值的平均值，通过DISTINCT去除重复值后求平均。
GROUP_CONCAT：将同一组中的值连接成一个字符串，可通过SEPARATOR指定分隔符。
STDDEV：计算数值列的标准差，用于衡量数据的离散程度。
VAR：计算数值列的方差，用于衡量数据的离散程度。
BIT_AND：计算一组值的按位与，用于位运算。
BIT_OR：计算一组值的按位或，用于位运算。
BIT_XOR：计算一组值的按位异或，用于位运算。

3.找不存在某属性的值

not in

select name as Customers
from Customers
where Customers.id not in (select customerId from Orders
)

定义：
- NOT IN 用于检查某个值是否不在一个子查询或值列表中。

语法：

SELECT column_name
FROM table_name
WHERE column_name NOT IN (subquery | value_list);

特点：
- 子查询：NOT IN 后面可以跟一个子查询，返回一个值列表。
- 值列表：也可以直接跟一个具体的值列表。
- 性能：在某些情况下，NOT IN 的性能可能不如 NOT EXISTS，尤其是在子查询返回大量数据时。
- 空值处理：如果子查询返回的列表中包含 NULL，NOT IN 会返回空结果集，因为 NULL 与任何值的比较结果都是 UNKNOWN。

not exist

SELECT name AS Customers
FROM Customers c
WHERE NOT EXISTS (SELECT 1FROM Orders oWHERE o.customerId = c.id
);

定义：
- NOT EXISTS 用于检查某个子查询是否不返回任何行。

语法：

SELECT column_name
FROM table_name
WHERE NOT EXISTS (subquery);

特点：
- 子查询：NOT EXISTS 后面必须跟一个子查询。
- 性能：通常比 NOT IN 更高效，尤其是在处理大量数据时。NOT EXISTS 会在找到第一个匹配的行时停止进一步检查，而 NOT IN 会检查整个子查询结果。
- 空值处理：NOT EXISTS 不受 NULL 值的影响，因为它只关心子查询是否返回行，而不是具体的值。

性能比较

NOT IN：
- 适用于子查询返回的值列表较小的情况。
- 如果子查询返回大量数据，性能可能会下降。
- 对 NULL 值敏感，可能导致意外结果。
NOT EXISTS：
- 通常更高效，尤其是在处理大量数据时。
- 不受 NULL 值的影响。
- 逻辑上更清晰，尤其是在涉及多表连接时。