mysql自连接 处理层次结构数据

MySQL 的自连接（Self Join）是一种特殊的连接方式，它允许一个表与自身进行连接。自连接通常用于处理具有层次结构或递归关系的数据，或者当同一张表中的数据需要相互关联时。以下是几种常见的场景，说明何时应该使用自连接。

1. 处理层次结构数据

当表中存储了具有父子关系或层级结构的数据时，自连接可以用来查询这些层次关系。例如，员工表中可能包含员工及其直接上级的信息，这时可以使用自连接来查询某个员工的所有下属，或者查找某位经理的所有直接下属。

示例：员工-经理关系

假设有一个 employees 表，其中每个员工都有一个 manager_id 字段，表示该员工的直接上级（经理）。你可以使用自连接来查询某个员工的所有下属。

SELECT e1.employee_id, e1.name AS employee_name, e2.name AS manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id
WHERE e2.employee_id = ?;  -- 替换为你要查询的经理ID

• 解释：
  • e1 表示员工表中的员工记录。
  • e2 表示员工表中的经理记录。
  • LEFT JOIN 用于将每个员工与其对应的经理关联起来。
  • WHERE e2.employee_id = ? 用于筛选出特定经理的所有下属。

2. 查找相邻时间点的数据

在某些情况下，你可能需要比较同一张表中不同时间点的数据。例如，你想比较某个月份的销售数据与前一个月的销售数据，或者计算某个指标的变化率。这时可以使用自连接来将当前月份的数据与前一个月的数据进行对比。

示例：比较相邻月份的销售数据

假设有一个 sales 表，记录了每个月的销售数据。你可以使用自连接来比较相邻两个月的销售情况。

WITH CurrentMonth AS (
    SELECT * 
    FROM sales 
    WHERE DATE_FORMAT(sale_date, '%Y-%m') = '2024-10'  -- 当前月份
),
PreviousMonth AS (
    SELECT * 
    FROM sales 
    WHERE DATE_FORMAT(sale_date, '%Y-%m') = '2024-09'  -- 前一个月
)
SELECT 
    c.month AS current_month,
    c.sales_amount AS current_sales,
    p.sales_amount AS previous_sales,
    (c.sales_amount - p.sales_amount) / p.sales_amount * 100 AS sales_change_percent
FROM CurrentMonth c
JOIN PreviousMonth p ON c.product_id = p.product_id;

• 解释：
  • CurrentMonth 和 PreviousMonth 是两个 CTE（Common Table Expressions），分别获取当前月份和前一个月的销售数据。
  • JOIN 用于将当前月份和前一个月的数据按产品 ID 进行关联。
  • 最后，计算销售增长百分比。

3. 查找重复或相似的记录

当你需要查找同一张表中存在重复或相似的记录时，自连接可以帮助你将每条记录与其他记录进行比较。例如，查找具有相同电话号码的不同用户，或者查找具有相同地址的多个客户。

示例：查找具有相同电话号码的用户

假设有一个 users 表，记录了用户的姓名和电话号码。你可以使用自连接来查找具有相同电话号码的不同用户。

SELECT u1.user_id, u1.name AS user1_name, u1.phone_number,
       u2.user_id, u2.name AS user2_name
FROM users u1
JOIN users u2 ON u1.phone_number = u2.phone_number
WHERE u1.user_id < u2.user_id;  -- 避免重复配对

• 解释：
  • u1 和 u2 是同一个 users 表的两个别名。
  • JOIN 用于将具有相同电话号码的用户进行关联。
  • WHERE u1.user_id < u2.user_id 用于避免重复配对（即避免 (u1, u2) 和 (u2, u1) 同时出现）。

4. 查找连续的记录

有时你需要查找连续的记录，例如查找连续登录的用户，或者查找连续几天内有活动的用户。自连接可以帮助你将当前记录与前后记录进行关联，从而判断是否存在连续性。

示例：查找连续登录的用户

假设有一个 user_logins 表，记录了用户的登录时间和用户 ID。你可以使用自连接来查找连续两天都登录的用户。

SELECT DISTINCT l1.user_id
FROM user_logins l1
JOIN user_logins l2 ON l1.user_id = l2.user_id
    AND l2.login_date = DATE_ADD(l1.login_date, INTERVAL 1 DAY);

• 解释：
  • l1 和 l2 是同一个 user_logins 表的两个别名。
  • JOIN 用于将同一个用户在相邻两天的登录记录进行关联。
  • DATE_ADD(l1.login_date, INTERVAL 1 DAY) 用于确保 l2 的登录日期是 l1 登录日期的下一天。
  • DISTINCT 用于去重，避免同一个用户多次出现在结果中。

在指定日期范围内查找连续7天登录的用户

要在指定日期范围内查找连续 7 天登录的用户，我们可以在查询中添加日期范围的过滤条件。具体来说，我们可以通过以下步骤来实现：

1. 限制查询的日期范围：在 user_logins 表中只选择指定日期范围内的登录记录。
2. 识别连续 7 天的登录：使用窗口函数或递归 CTE 来识别每个用户的连续登录天数。
3. 确保连续 7 天在指定日期范围内：确保用户的连续 7 天登录完全包含在指定的日期范围内。

方案 1：使用窗口函数（推荐）

有关窗口函数的，可以参考我的另一篇文章
mysql窗口函数(Window Functions)详解

我们将基于你之前的窗口函数方法，并添加日期范围的过滤条件。假设你要查找在 2023-01-01 到 2023-01-31 之间连续 7 天登录的用户。

SQL 查询

WITH login_gaps AS (
    -- 1. 选择指定日期范围内的登录记录，并为每个用户的登录记录分配行号
    SELECT 
        user_id, 
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn<