我有一個 SQL 查詢，它根據上次登錄日期在表中查找用戶 ID
select USER_ID, max(login_time) maxd from MyTable group by User_Id having max(login_time) < (sysdate - 90)
我需要洗掉此查詢找到的條目
我試過了
DELETE a
FROM MyTable a
JOIN
(
select USER_ID, max(login_time) maxd from MyTable group by User_Id having max(login_time) < (sysdate - 90)
) b ON a.USER_ID = b.USER_ID
但這會導致 SQL 錯誤：ORA-00933：SQL 命令未正確結束
任何人都可以幫忙嗎？
uj5u.com熱心網友回復：
Oracle 支持洗掉連接語法，但與其他資料庫略有不同。
DELETE FROM (
SELECT a.*
FROM MyTable a
INNER JOIN
(
SELECT USER_ID
FROM MyTable
GROUP BY User_Id
HAVING MAX(login_time) < (sysdate - 90)
) b ON a.USER_ID = b.USER_ID
);
uj5u.com熱心網友回復：
使用您的select作為子查詢：
DELETE FROM MyTable
WHERE (user_id, login_time) IN
-- this is your query:
( SELECT USER_ID, MAX (login_time) maxd
FROM MyTable
GROUP BY User_Id
HAVING MAX (login_time) < (SYSDATE - 90));
uj5u.com熱心網友回復：
您可以關聯ROWID偽列并使用分析函式：
DELETE FROM mytable
WHERE ROWID IN (
SELECT ROWID
FROM (
SELECT login_time,
MAX(login_time) OVER (PARTITION BY user_id) AS max_login_time
FROM mytable
)
WHERE max_login_time < SYSDATE - INTERVAL '90' DAY
AND login_time = max_login_time
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/349014.html
下一篇：如何對多列進行計數（不同）