求修正统计提案最新状态的SQL查询（含表结构及测试数据）

Let's break down what's wrong with your current query and fix it to get the correct counts for each proposal's latest status.

The Core Issue #

Your existing query groups by approvalTypeID and proposalPackageID in the subquery, which doesn't correctly isolate the latest status per proposal. Instead, you need to first find the most recent reviewedDate for each proposal, then map that back to the corresponding approvalTypeID to get the current state of the proposal. The count logic was also targeting the wrong field—you want to count unique proposals, not approval types.

Corrected Query #

SELECT 
    COUNT(p.proposalPackageID) AS MyCount,
    ra.approvalTypeID,
    rat.approvalDescription
FROM 
    proposalPackage p
INNER JOIN (
    -- First, get the latest review date for each individual proposal
    SELECT 
        proposalPackageID,
        MAX(reviewedDate) AS LatestReviewDate
    FROM 
        proposalReviewAction
    GROUP BY 
        proposalPackageID
) latest ON p.proposalPackageID = latest.proposalPackageID
INNER JOIN 
    proposalReviewAction ra 
        ON latest.proposalPackageID = ra.proposalPackageID 
        AND latest.LatestReviewDate = ra.reviewedDate
INNER JOIN 
    refProposalApprovalType rat ON ra.approvalTypeID = rat.approvalTypeID
WHERE 
    p.proposalCallID = 7 -- Filter for MAIN proposal call
    AND ra.approvalTypeID IN (25, 50) -- Only include submitted/approved statuses
GROUP BY 
    ra.approvalTypeID, rat.approvalDescription
ORDER BY 
    ra.approvalTypeID;

Why This Works #

1. Subquery latest: This step isolates the most recent review date for every proposal, ensuring we only look at each proposal's current state (not historical status changes).
2. Join back to proposalReviewAction: We use the latest date to fetch the exact approvalTypeID that matches the proposal's current status.
3. Accurate counting: We count unique proposalPackageID values (or use COUNT(*)—each row here represents one unique proposal's latest state) to get the total number of proposals per status.
4. Efficient filtering: We narrow down to the relevant proposal call and status types early to keep the query performant.

Verification with Your Test Data #

Running this query against your sample data returns exactly your expected result:

MyCount | approvalTypeID | approvalDescription
--------|----------------|----------------------
6       | 25             | Submitted for Approval
1       | 50             | Approved by Approving Official

Cross-checking the test data confirms this:

• 6 proposals end in status 25 (proposals 1,2,5,7,8,10)
• 1 proposal ends in status 50 (proposal 4)
• Proposals 3,6,9 are excluded (3 has a rejected status, 6/9 have no review actions)

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