如何实现带条件的n次SQL自连接？附相关表结构示例

嘿，看起来你需要把同一批次（分组）里不同Type的记录通过自连接关联起来，而且要支持n次连接对吧？我给你分两种常见场景来讲解，应该能解决你的问题：

假设你的业务里n是固定的（比如示例里的n=3，对应Type 1、2、3），首先我们需要给每组记录分配一个组标识，这样才能把同一组的不同Type记录关联起来。这里我用窗口函数生成组ID，逻辑是：每遇到Type=1的记录就开启一个新组。

完整SQL示例：

WITH grouped_data AS (
    SELECT 
        Id,
        Type,
        -- 生成组ID：每次Type=1时组号+1
        SUM(CASE WHEN Type = 1 THEN 1 ELSE 0 END) OVER (ORDER BY Id) AS group_id
    FROM your_table_name -- 替换成你的表名
)
SELECT 
    g1.Id AS type1_id,
    g2.Id AS type2_id,
    g3.Id AS type3_id
FROM grouped_data g1
-- 自连接Type=2的记录，关联条件是同一组
JOIN grouped_data g2 ON g1.group_id = g2.group_id AND g2.Type = 2
-- 继续自连接Type=3的记录
JOIN grouped_data g3 ON g1.group_id = g3.group_id AND g3.Type = 3
WHERE g1.Type = 1; -- 以Type=1的记录为基准关联其他Type

如果n更大（比如n=4），只需要继续添加JOIN grouped_data g4 ON g1.group_id = g4.group_id AND g4.Type = 4即可。

如果你的Type数量不固定，纯静态SQL很难实现动态自连接，这里给你两种更灵活的方案：

方案A：用行转列替代自连接（推荐） #

这种方式不需要多次自连接，而是通过聚合函数+CASE语句把同一组的不同Type记录转成列，逻辑更简洁，也更容易扩展：

WITH grouped_data AS (
    SELECT 
        Id,
        Type,
        SUM(CASE WHEN Type = 1 THEN 1 ELSE 0 END) OVER (ORDER BY Id) AS group_id
    FROM your_table_name
)
SELECT 
    group_id,
    MAX(CASE WHEN Type = 1 THEN Id END) AS type1_id,
    MAX(CASE WHEN Type = 2 THEN Id END) AS type2_id,
    MAX(CASE WHEN Type = 3 THEN Id END) AS type3_id,
    -- 如果有更多Type，继续添加对应的CASE行即可
    MAX(CASE WHEN Type = n THEN Id END) AS typen_id
FROM grouped_data
GROUP BY group_id;

如果某组缺少某个Type，对应的列会显示NULL，如果需要过滤掉不完整的组，可以在末尾加HAVING COUNT(DISTINCT Type) = [你的最大Type值]。

方案B：用动态SQL自动生成逻辑 #

如果需要完全适配任意数量的Type，且不想手动修改SQL，可以用动态SQL自动生成连接/行转列逻辑（以下示例基于SQL Server，不同数据库语法略有差异）：

DECLARE @max_type INT;
DECLARE @dynamic_sql NVARCHAR(MAX);
DECLARE @select_columns NVARCHAR(MAX);

-- 获取表中最大的Type值（即n）
SELECT @max_type = MAX(Type) FROM your_table_name;

-- 自动生成SELECT列的部分
SET @select_columns = STUFF((
    SELECT ', MAX(CASE WHEN Type = ' + CAST(Type AS VARCHAR) + ' THEN Id END) AS type' + CAST(Type AS VARCHAR) + '_id'
    FROM (SELECT DISTINCT Type FROM your_table_name) AS type_list
    ORDER BY Type
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- 拼接最终的动态SQL
SET @dynamic_sql = N'
WITH grouped_data AS (
    SELECT 
        Id,
        Type,
        SUM(CASE WHEN Type = 1 THEN 1 ELSE 0 END) OVER (ORDER BY Id) AS group_id
    FROM your_table_name
)
SELECT group_id, ' + @select_columns + '
FROM grouped_data
GROUP BY group_id;';

-- 执行动态SQL
EXEC sp_executesql @dynamic_sql;

这个脚本会自动读取表中所有的Type值，生成对应的列，不管n是多少都能自动适配。

• 分组逻辑调整：如果你的分组规则不是“Type=1开启新组”，而是其他规则（比如Id前缀、时间批次等），需要修改group_id的生成逻辑，确保同一组的记录有相同的group_id。
• 数据库兼容性：不同数据库的窗口函数、动态SQL语法有差异，比如MySQL用PREPARE/EXECUTE执行动态SQL，Oracle用EXECUTE IMMEDIATE，需要根据你的数据库调整。
• 空值处理：如果存在某组缺少Type的情况，可以通过HAVING子句过滤掉不完整的组，或者用COALESCE函数给NULL值设置默认值。

内容的提问来源于stack exchange，提问作者user8666203