SQL技术问题：统计存在有效LeadSource的Account中Lead Source为Null的记录数

云原生阿岚

2026-8-10

统计符合条件的空LeadSource记录数量

需求明确：统计Accounts表中，同一Account ID下存在非空LeadSource记录的前提下，LeadSource为Null的记录总数量。

方案一：子查询筛选关联

先找出所有存在非空LeadSource的Account ID，再统计这些ID下的空值记录：

SELECT COUNT(*) AS null_leadsource_count
FROM Accounts
WHERE "Account ID" IN (
    SELECT DISTINCT "Account ID"
    FROM Accounts
    WHERE LeadSource IS NOT NULL
)
AND LeadSource IS NULL;

注：如果你的Account ID字段名带空格，需根据数据库语法用对应符号包裹（比如MySQL用反引号`Account ID`，PostgreSQL用双引号）。

方案二：窗口函数一次性计算

用窗口函数标记每个Account ID是否存在非空LeadSource，再过滤统计：

WITH AccountLeadStatus AS (
    SELECT 
        "Account ID",
        LeadSource,
        -- 标记当前Account ID是否存在非空LeadSource
        MAX(CASE WHEN LeadSource IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY "Account ID") AS has_non_null_lead
    FROM Accounts
)
SELECT COUNT(*) AS null_leadsource_count
FROM AccountLeadStatus
WHERE has_non_null_lead = 1
AND LeadSource IS NULL;