如何将同一datetime下全为0的多行数据替换为单行？

可以通过事务+临时表/CTE一次性完成删除替换操作，保证原子性（要么全部执行成功，要么回滚），具体SQL方案如下：

通用版本（适配多数关系型数据库） #

BEGIN TRANSACTION;

-- 1. 筛选出所有"对应所有value均为0"的datetime，存入临时表
CREATE TEMPORARY TABLE target_dates AS
SELECT datetime
FROM foo
GROUP BY datetime
HAVING SUM(ABS(value)) = 0;  -- 用SUM(ABS)规避浮点精度问题，确保所有value都是0

-- 2. 删除这些datetime对应的所有原数据行
DELETE FROM foo
WHERE datetime IN (SELECT datetime FROM target_dates);

-- 3. 插入替换的单行数据
INSERT INTO foo (datetime, location, value)
SELECT datetime, 'EVERYWHERE', 0
FROM target_dates;

COMMIT TRANSACTION;

简化版本（支持CTE复用的数据库，如PostgreSQL、SQL Server） #

BEGIN TRANSACTION;

WITH target_dates AS (
    SELECT datetime
    FROM foo
    GROUP BY datetime
    HAVING SUM(ABS(value)) = 0
)
DELETE FROM foo
WHERE datetime IN (SELECT datetime FROM target_dates);

INSERT INTO foo (datetime, location, value)
SELECT datetime, 'EVERYWHERE', 0
FROM target_dates;

COMMIT TRANSACTION;

关键说明 #

• 用SUM(ABS(value)) = 0替代直接判断COUNT(CASE WHEN value=0 THEN 1 END) = COUNT(*)，是为了避免浮点类型的精度误差（比如0.0000000001这类近似0的值被误判）。
• 包裹在事务中是为了防止删除后插入失败导致数据丢失，确保操作的一致性。
• 临时表/CTE的作用是一次性筛选出目标日期，避免重复计算。

内容的提问来源于stack exchange，提问作者d.b