在DELETE查询中使用JOIN：删除重复活跃数据的问题求助

你已经找对了核心思路——先定位每个账户下需要删除的最大时间戳行，但问题出在不同数据库对DELETE结合JOIN的语法支持不一样。下面针对几种主流数据库给出适配的解决方案：

先明确需求 #

你需要删除的是：每个ACCOUNT分组下，ACTIVE=1且TIMESTAMP最大的那一行（仅当该账户存在多个ACTIVE=1的条目时）。

1. MySQL/MariaDB 解决方案 #

MySQL支持DELETE ... JOIN语法，但必须明确指定要删除的表别名：

WITH TMP AS (
    SELECT account, MAX(timestamp) AS MAXTIME 
    FROM balance 
    WHERE active = 1 
    GROUP BY account 
    HAVING COUNT(*) > 1
)
DELETE b 
FROM balance b
INNER JOIN TMP t 
    ON b.account = t.account AND b.timestamp = t.MAXTIME;

你的原语句缺少了DELETE b FROM中的b FROM部分，这是MySQL要求的语法规则——必须指明要删除的目标表。

2. Oracle 解决方案 #

Oracle不支持DELETE ... JOIN的写法，推荐用窗口函数精准定位要删除的行，这种方式也更灵活（能处理同一账户多组重复的场景）：

DELETE FROM balance
WHERE (account, timestamp) IN (
    SELECT account, timestamp
    FROM (
        SELECT 
            account, 
            timestamp,
            ROW_NUMBER() OVER (PARTITION BY account ORDER BY timestamp DESC) AS rn
        FROM balance
        WHERE active = 1
    )
    WHERE rn = 1
);

这个查询会给每个ACCOUNT下的ACTIVE=1行按TIMESTAMP降序编号，编号为1的就是最大时间戳的行，直接删除这些行即可。

3. PostgreSQL 解决方案 #

PostgreSQL可以用USING子句结合CTE实现需求：

WITH TMP AS (
    SELECT account, MAX(timestamp) AS MAXTIME 
    FROM balance 
    WHERE active = 1 
    GROUP BY account 
    HAVING COUNT(*) > 1
)
DELETE FROM balance b
USING TMP t
WHERE b.account = t.account AND b.timestamp = t.MAXTIME;

你也可以采用和Oracle类似的窗口函数写法，效果完全一致。

4. SQL Server 解决方案 #

SQL Server支持DELETE ... JOIN，语法和你的原语句接近，调整后即可运行：

WITH TMP AS (
    SELECT account, MAX(timestamp) AS MAXTIME 
    FROM balance 
    WHERE active = 1 
    GROUP BY account 
    HAVING COUNT(*) > 1
)
DELETE b
FROM balance b
INNER JOIN TMP t 
    ON b.account = t.account AND b.timestamp = t.MAXTIME;

注意需要确保你的SQL Server版本支持CTE（2008及以上版本均支持）。

验证示例数据 #

针对你给出的示例数据：

ACCOUNT BALANCE_AMT TIMESTAMP ACTIVE
832076635 10.23 02-MAR-18 1
832076635 13.34 29-DEC-17 1
832076635 9.22 01-OCT-17 0

执行上述对应数据库的语句后，02-MAR-18的那一行会被删除，剩下的两行保留，完全符合你的需求。

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