MS Access 2007 SQL查询：在ProposalUrgency查询中添加嵌套COUNT统计

要在你的ProposalUrgency查询里，让每行提案同时显示对应承保人被分配的总提案数量，可以通过两种简洁且实用的方式实现，下面是具体写法和细节说明：

方法1：关联子查询（简单直观，适合小数据集） #

这种方式直接在SELECT语句中嵌入子查询，针对当前行的承保人ID实时统计其总提案数，写法上手快：

SELECT 
    proposalID,
    isUrgent,
    deadline,
    underwriterID,
    -- 子查询：统计当前承保人负责的所有提案数量
    (SELECT COUNT(proposalID) FROM Proposals WHERE underwriterID = p.underwriterID) AS assigned_proposals_count
FROM Proposals p
ORDER BY isUrgent DESC, deadline ASC;

细节说明： #

• 子查询会自动匹配主查询每行的underwriterID，返回该承保人的总提案数
• 别名assigned_proposals_count可以根据你的习惯自定义，清晰标识这列的含义
• 逻辑简单易懂，适合数据量不大的场景，不需要额外的预计算步骤

方法2：CTE预统计+左连接（性能更优，适合大数据量） #

如果你的提案数据量比较大，推荐先一次性统计所有承保人的提案数，再通过左连接关联到主查询，避免重复计算，提升执行效率：

-- 先预统计每个承保人的提案总数，用CTE临时存储结果
WITH UnderwriterProposalStats AS (
    SELECT 
        underwriterID,
        COUNT(proposalID) AS assigned_proposals_count
    FROM Proposals
    GROUP BY underwriterID
)
SELECT 
    p.proposalID,
    p.isUrgent,
    p.deadline,
    p.underwriterID,
    -- 用COALESCE处理空值，确保无提案的承保人显示0而非NULL
    COALESCE(ups.assigned_proposals_count, 0) AS assigned_proposals_count
FROM Proposals p
LEFT JOIN UnderwriterProposalStats ups 
    ON p.underwriterID = ups.underwriterID
ORDER BY isUrgent DESC, deadline ASC;

细节说明： #

• CTE（公共表表达式）UnderwriterProposalStats只执行一次统计操作，相比子查询重复计算的方式，大数据量下性能优势明显
• 使用LEFT JOIN保证即使出现承保人无提案的极端情况（虽然主表是提案记录，这种情况很少见），也能正常显示0而不是NULL
• 如果需要过滤特定状态的提案（比如只统计未完成的），可以在CTE的SELECT里添加WHERE条件（例如WHERE status != 'completed'）

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