我试图删除不必要的时间，因为我只需要min()和max()数据，从选定的id.并通过堆栈溢出读取相同的问题:
- SQL select only rows with max value on a column个
- Finding Max and Min Times个
- Delete all records except the most recent one?个
但很多命令都使用SELECT.因为按id删除除min()和max()以外的数据.
以下是我的疑问:
DELETE FROM table1
WHERE ID_karyawan IN (1, 3, 4, 5, 6, 7) -- List all the IDs you want to filter here
AND jam NOT IN (SELECT MIN(jam)
FROM table1
WHERE ID_karyawan IN (1, 3, 4, 5, 6, 7) -- Repeat the list of IDs here
UNION
SELECT MAX(jam)
FROM table1
WHERE ID_karyawan IN (1, 3, 4, 5, 6, 7) -- Repeat the list of IDs here
);
SELECT
table1.ID_karyawan, table1.nama_karyawan,
table1.jam, table1.tanggal, table1.arah
FROM
table1
GROUP BY
ID_karyawan, nama_karyawan, jam, tanggal, arah
这是我的表(表1):
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
但是，如果执行多于1的查询，则所有选定的id将被永久删除.
就像这样:
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
8 Aries Krisnawan 07:49:06 2023-07-20 00:00:00.000
8 Aries Krisnawan 07:49:11 2023-07-20 00:00:00.000
8 Aries Krisnawan 17:04:30 2023-07-20 00:00:00.000
所以所有选定的id都消失了.我的查询是不是错了？或者你对此有什么建议？