SQLite基于特定条件查询存在多条活跃记录的客户对应数据行

• 实现逻辑：先统计每个客户的活跃记录（即end_record_date = '00:00:00'的记录）数量，筛选出活跃数≥2的客户编号，再返回这些客户对应的所有活跃记录行。

写法1：子查询+IN子句（全版本SQLite兼容） #

将代码中的你的表名替换为实际存储客户数据的表名即可运行：

SELECT row_id, customer_number, end_record_date
FROM 你的表名
WHERE end_record_date = '00:00:00'
  AND customer_number IN (
    SELECT customer_number
    FROM 你的表名
    WHERE end_record_date = '00:00:00'
    GROUP BY customer_number
    HAVING COUNT(*) >= 2
);

写法2：窗口函数（SQLite 3.25及以上版本支持） #

WITH customer_active_cnt AS (
    SELECT 
        row_id, 
        customer_number, 
        end_record_date,
        COUNT(*) OVER (PARTITION BY customer_number) AS active_count
    FROM 你的表名
    WHERE end_record_date = '00:00:00'
)
SELECT row_id, customer_number, end_record_date
FROM customer_active_cnt
WHERE active_count >= 2;

两种写法输出结果完全一致，均匹配你给出的示例需求：仅返回有≥2条活跃记录的客户对应的所有活跃数据行。

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