我尝试删除不必要的时间,因为我只需要min()
and max()
数据,来自选定的 ID。并且还通过 Stack Overflow 阅读了同样的问题:
- SQL 仅选择列上具有最大值的行 https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column
- 查找最大和最小时间 https://stackoverflow.com/questions/1277480/finding-max-and-min-times
- 删除除最新记录之外的所有记录? https://stackoverflow.com/questions/7238983/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都消失了。我的查询有误吗?或者您对此有什么建议吗?