SQL删除指定ID的非min()/max()时间数据时误删全部数据，求解决方法

我需要删除指定ID（1、3、4、5、6、7）中除min(jam)和max(jam)之外的冗余时间记录，仅保留每个ID的这两个极值数据。我尝试编写了SQL语句，但执行多次后指定ID的所有记录被意外删除，以下是详细情况：

原SQL语句 #

DELETE FROM table1
WHERE ID_karyawan IN (1, 3, 4, 5, 6, 7) -- 目标ID列表
  AND jam NOT IN (SELECT MIN(jam) 
                  FROM table1 
                  WHERE ID_karyawan IN (1, 3, 4, 5, 6, 7)
                  UNION
                  SELECT MAX(jam) 
                  FROM table1 
                  WHERE ID_karyawan IN (1, 3, 4, 5, 6, 7)
                 );

SELECT 
    table1.ID_karyawan, table1.nama_karyawan, 
    table1.jam, table1.tanggal, table1.arah
FROM 
    table1 
GROUP BY 
    ID_karyawan, nama_karyawan, jam, tanggal, arah

原数据 #

ID karyawan nama karyawan       jam          tanggal    arah
-------------------------------------------------------------
1   ridho azhar megantara     07:44:45    2023-07-20  masuk
1   ridho azhar megantara     17:04:46    2023-07-20  keluar
3   Hendy Arief Yuwono        17:24:47    2023-07-20  keluar
3   Hendy Arief Yuwono        06:58:41    2023-07-20  masuk
3   Hendy Arief Yuwono        17:24:41    2023-07-20  keluar
4   Ety wulandari             07:51:48    2023-07-20  masuk
4   Ety wulandari             17:04:07    2023-07-20  keluar
5   Joseph Tan                17:03:48    2023-07-20  keluar
5   Joseph Tan                07:40:31    2023-07-20  masuk
6   Herry Joko Susilo         17:04:16    2023-07-20  keluar
6   Herry Joko Susilo         07:26:11    2023-07-20  masuk
6   Herry Joko Susilo         07:26:16    2023-07-20  masuk
7   Martha Ayu Wulandari      07:49:53    2023-07-20  masuk
7   Martha Ayu Wulandari      07:50:23    2023-07-20  masuk
7   Martha Ayu Wulandari      17:04:43    2023-07-20  keluar

期望结果 #

ID karyawan nama karyawan       jam          tanggal    arah
-----------------------------------------------------------
1   ridho azhar megantara     07:44:45    2023-07-20  masuk
1   ridho azhar megantara     17:04:46    2023-07-20  keluar
3   Hendy Arief Yuwono        06:58:41    2023-07-20  masuk
3   Hendy Arief Yuwono        17:24:41    2023-07-20  keluar
4   Ety wulandari             07:51:48    2023-07-20  masuk
4   Ety wulandari             17:04:07    2023-07-20  keluar
5   Joseph Tan                17:03:48    2023-07-20  keluar
5   Joseph Tan                07:40:31    2023-07-20  masuk
6   Herry Joko Susilo         07:26:11    2023-07-20  masuk
6   Herry Joko Susilo         17:04:16    2023-07-20  keluar
7   Martha Ayu Wulandari      07:49:53    2023-07-20  masuk
7   Martha Ayu Wulandari      17:04:43    2023-07-20  keluar

实际问题 #

执行上述SQL多次后，指定ID的记录被大量删除，例如ID3仅剩下非期望的记录，甚至最终可能被全部删除：

3   Hendy Arief Yuwono        06:58:41    2023-07-20 00:00:00.000
3   Hendy Arief Yuwono        17:24:47    2023-07-20 00:00:00.000

你的SQL核心问题在于：子查询是针对所有指定ID的整体取MIN(jam)和MAX(jam)，而不是每个ID单独取自己的极值。

比如所有指定ID中，整体最小的jam是ID3的06:58:41，整体最大的是ID3的17:24:47。第一次执行DELETE时，所有jam不是这两个值的指定ID记录都会被删掉——包括ID1的07:44:45和17:04:46、ID4的所有记录等。第二次执行时，剩下的记录的jam会形成新的整体极值范围，导致更多记录被删除，最终指定ID的记录可能被清空。

需要按ID_karyawan分组，获取每个ID自己的MIN(jam)和MAX(jam)，再删除不在对应ID极值列表中的记录。以下是两种可行的写法：

方法一：使用关联子查询 #

DELETE t1
FROM table1 t1
WHERE t1.ID_karyawan IN (1, 3, 4, 5, 6, 7)
  AND t1.jam NOT IN (
      SELECT jam 
      FROM (
          SELECT ID_karyawan, MIN(jam) AS jam FROM table1 WHERE ID_karyawan IN (1, 3, 4, 5, 6, 7) GROUP BY ID_karyawan
          UNION ALL
          SELECT ID_karyawan, MAX(jam) AS jam FROM table1 WHERE ID_karyawan IN (1, 3, 4, 5, 6, 7) GROUP BY ID_karyawan
      ) t2
      WHERE t2.ID_karyawan = t1.ID_karyawan
  );

方法二：使用CTE（适用于支持CTE的数据库如MySQL 8+、SQL Server等） #

WITH KaryawanExtremes AS (
    SELECT 
        ID_karyawan,
        MIN(jam) AS min_jam,
        MAX(jam) AS max_jam
    FROM table1
    WHERE ID_karyawan IN (1, 3, 4, 5, 6, 7)
    GROUP BY ID_karyawan
)
DELETE t1
FROM table1 t1
JOIN KaryawanExtremes ke ON t1.ID_karyawan = ke.ID_karyawan
WHERE t1.jam NOT IN (ke.min_jam, ke.max_jam);

执行DELETE前，建议先把语句改成SELECT，确认要删除的记录是否正确：

-- 验证要删除的记录
SELECT *
FROM table1 t1
WHERE t1.ID_karyawan IN (1, 3, 4, 5, 6, 7)
  AND t1.jam NOT IN (
      SELECT jam 
      FROM (
          SELECT ID_karyawan, MIN(jam) AS jam FROM table1 WHERE ID_karyawan IN (1, 3, 4, 5, 6, 7) GROUP BY ID_karyawan
          UNION ALL
          SELECT ID_karyawan, MAX(jam) AS jam FROM table1 WHERE ID_karyawan IN (1, 3, 4, 5, 6, 7) GROUP BY ID_karyawan
      ) t2
      WHERE t2.ID_karyawan = t1.ID_karyawan
  );

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