求筛选存在In设备记录但无对应Out记录的SQL查询语句

问题描述 #

现有数据表数据如下：

Pin      Device          Date Time
1         A-in           01/01/2023 10.00
1         B-Out          01/01/2023 10.30
2         B-in           01/01/2023 11.00
2         A-Out          01/01/2023 11.30
3         C-In           01/01/2023 13.00

需求：筛选出同一Pin下，存在带'In'的Device记录，但没有对应设备的'Out'记录的行。比如示例中Pin 3只有C-In，没有C-Out，需要输出该行。

方法1：使用NOT EXISTS子查询 #

逻辑直观，针对每条In记录，检查同Pin下是否存在对应设备的Out记录：

SELECT t1.*
FROM your_table t1
WHERE t1.Device LIKE '%In'
  AND NOT EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.Pin = t1.Pin
      AND t2.Device = REPLACE(t1.Device, 'In', 'Out')
  );

• t1.Device LIKE '%In'先筛选所有In类型记录
• REPLACE(t1.Device, 'In', 'Out')生成对应设备的Out名称，用于匹配
• NOT EXISTS确保同Pin下无对应Out记录

方法2：使用LEFT JOIN筛选NULL值 #

通过左连接匹配对应Out记录，筛选连接失败的行：

SELECT t1.*
FROM your_table t1
LEFT JOIN your_table t2
  ON t1.Pin = t2.Pin
  AND t2.Device = REPLACE(t1.Device, 'In', 'Out')
WHERE t1.Device LIKE '%In'
  AND t2.Pin IS NULL;

• 左连接保留所有In记录，尝试匹配对应的Out记录
• 未匹配到Out记录时，t2的字段为NULL，通过t2.Pin IS NULL筛选目标行

方法3：分组统计验证 #

先按Pin和设备前缀分组统计In/Out数量，再筛选仅存在In的分组：

WITH device_groups AS (
  SELECT 
    Pin,
    REPLACE(Device, 'In', '') AS device_prefix,
    SUM(CASE WHEN Device LIKE '%In' THEN 1 ELSE 0 END) AS in_count,
    SUM(CASE WHEN Device LIKE '%Out' THEN 1 ELSE 0 END) AS out_count
  FROM your_table
  GROUP BY Pin, REPLACE(Device, 'In', '')
)
SELECT t.*
FROM your_table t
JOIN device_groups dg
  ON t.Pin = dg.Pin
  AND REPLACE(t.Device, 'In', '') = dg.device_prefix
WHERE t.Device LIKE '%In'
  AND dg.out_count = 0;

• CTE先分组统计每个Pin下各设备前缀的In/Out数量
• 关联原表后，筛选出In记录且对应前缀Out数量为0的行

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