如何将数据表Type列的In/Out数据拆分对应为两列？

现有数据表（表名：T1） #

期望结果表 #

我的思路及问题 #

我想通过两个子查询分别筛选Type为In和Out的数据，按ID、Time排序后关联，写了如下SQL语句：

select  Intable.id,
        Intable.time,
        Outtable.time 
from (select * from T1
      where type='IN' order by ID, Time  as Intable) 
join ( select * from T1
        where type='out' as Outtable order by ID, Time) 
where Intable.ID = Outtable.id

请问该方案是否可行？如何让两个子表按相同索引进行匹配？

解答 #

你的现有SQL存在语法错误，且方案逻辑有漏洞：

• 子查询的as 别名必须放在括号外侧，不能跟在order by后面；
• SQL是基于集合的查询语言，子查询里的order by在没有limit限制时是无效的，数据库不会保证子查询的输出顺序，所以直接关联会出现配对混乱的问题。

思路方向是对的，但需要给每个ID下的In、Out记录添加分组序号，才能保证同组的In和Out正确匹配，具体实现如下：

方案一：使用窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等）

WITH in_data AS (
    SELECT 
        ID,
        Time AS In_Time,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Time) AS rn
    FROM T1
    WHERE Type = 'In'
),
out_data AS (
    SELECT 
        ID,
        Time AS Out_Time,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Time) AS rn
    FROM T1
    WHERE Type = 'Out'
)
SELECT 
    in_data.ID,
    in_data.In_Time AS `In`,
    out_data.Out_Time AS `Out`
FROM in_data
JOIN out_data 
    ON in_data.ID = out_data.ID 
    AND in_data.rn = out_data.rn;

说明：

1. 用ROW_NUMBER()按ID分组，每组内按Time排序，给每条In/Out记录分配序号rn，确保第一条In对应第一条Out，第二条In对应第二条Out；
2. 通过ID+rn关联两个子查询，实现精准配对。

方案二：兼容低版本MySQL（无窗口函数时）

SELECT 
    in_data.ID,
    in_data.In_Time AS `In`,
    out_data.Out_Time AS `Out`
FROM (
    SELECT 
        ID,
        Time AS In_Time,
        @in_rn := IF(@prev_id = ID, @in_rn + 1, 1) AS rn,
        @prev_id := ID
    FROM T1, (SELECT @in_rn := 0, @prev_id := '') AS vars
    WHERE Type = 'In'
    ORDER BY ID, Time
) AS in_data
JOIN (
    SELECT 
        ID,
        Time AS Out_Time,
        @out_rn := IF(@prev_id_out = ID, @out_rn + 1, 1) AS rn,
        @prev_id_out := ID
    FROM T1, (SELECT @out_rn := 0, @prev_id_out := '') AS vars
    WHERE Type = 'Out'
    ORDER BY ID, Time
) AS out_data 
    ON in_data.ID = out_data.ID 
    AND in_data.rn = out_data.rn;

说明：用自定义变量模拟分组序号，实现和窗口函数相同的配对逻辑。

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