如何查询同一AccountId在同一日期下多状态的SQL记录

需求：从PaymentRecord表中查询**同一AccountId、同一日期（忽略时间部分）**下存在多种不同Status的所有原始记录。

假设表结构 #

CREATE TABLE PaymentRecord (
    Id INT PRIMARY KEY,
    AccountId INT,
    Status VARCHAR(20),
    CreateTime DATETIME -- 包含日期和时间的字段
);

测试数据 #

INSERT INTO PaymentRecord VALUES
(1, 1001, 'SUCCESS', '2024-05-20 10:30:00'),
(2, 1001, 'FAILED', '2024-05-20 14:20:00'),
(3, 1001, 'SUCCESS', '2024-05-21 09:15:00'),
(4, 1002, 'PENDING', '2024-05-20 08:40:00'),
(5, 1002, 'SUCCESS', '2024-05-20 11:50:00'),
(6, 1003, 'SUCCESS', '2024-05-20 15:30:00');

预期结果 #

返回所有满足条件的原始记录：

正确SQL实现方案 #

方案1：子查询关联（兼容性好，适配所有SQL方言）

先筛选出存在多状态的AccountId+日期组合，再关联原表取出所有对应记录：

SELECT pr.*
FROM PaymentRecord pr
INNER JOIN (
    -- 找出存在多种状态的账号+日期组合
    SELECT 
        AccountId, 
        CAST(CreateTime AS DATE) AS RecordDate
    FROM PaymentRecord
    GROUP BY AccountId, CAST(CreateTime AS DATE)
    HAVING COUNT(DISTINCT Status) > 1
) AS valid_groups
ON pr.AccountId = valid_groups.AccountId 
AND CAST(pr.CreateTime AS DATE) = valid_groups.RecordDate;

方案2：窗口函数（高效，适合大数据量场景）

用窗口函数直接计算每组的状态数量，再筛选符合条件的记录：

SELECT Id, AccountId, Status, CreateTime
FROM (
    SELECT 
        *,
        -- 按账号+日期分组，计算组内不同状态的数量
        COUNT(DISTINCT Status) OVER (
            PARTITION BY AccountId, CAST(CreateTime AS DATE)
        ) AS distinct_status_count
    FROM PaymentRecord
) AS temp
WHERE distinct_status_count > 1;

关键说明 #

• 提取日期部分的语法：不同SQL方言有细微差异，比如MySQL用DATE(CreateTime)，SQL Server用CAST(CreateTime AS DATE)，根据你的数据库类型调整即可。
• COUNT(DISTINCT Status)确保统计的是不同状态的数量，避免同一状态多次出现导致误判。

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