SQL计算库存异动（出库量）时重复统计问题排查与解决

Hey there! Let's figure out why your outbound quantity is being double-counted here. The core issue is unintended cartesian products from the LEFT JOIN—let me break this down step by step.

Why the Overcount Happens #

Your current query joins each record from readYesterday (2018-04-26) to matching records in readToday (2018-04-27) on ArticleCode and InboundDossier. If there are multiple readToday records for the same ArticleCode + InboundDossier pair, each readYesterday row will be duplicated once for every matching readToday row.

When you run SUM(readYesterday.TotalStock), you're summing that duplicated value multiple times instead of just once. This inflates your "TotalStockYesterday" and throws off the outbound calculation (TotalStockYesterday - TotalStockToday).

How to Verify the Issue #

Run this simplified query first to see the raw joined data—you'll spot duplicate readYesterday rows paired with different readToday entries:

SELECT readYesterday.*, readToday.*
FROM ArticleReads readYesterday 
LEFT JOIN ArticleReads readToday 
    ON readToday.ArticleCode = readYesterday.ArticleCode 
    AND readToday.InboundDossier = readYesterday.InboundDossier 
    AND readToday.ReportDate = DATEADD(DAY, 1, readYesterday.ReportDate)
WHERE readYesterday.ArticleCode ='ART01234' 
AND readYesterday.ReportDate = '2018-04-26'

Fixes to Resolve the Overcount #

Fix 1: Aggregate readToday First Before Joining

Pre-aggregate the readToday data so each ArticleCode + InboundDossier pair has a single summed value. This ensures each readYesterday row only joins to one aggregated readToday row, eliminating duplicates:

SELECT 
    readYesterday.ArticleCode, 
    readTodayAgg.ArticleCode AS ArticleCodeToday,
    readYesterday.ReportDate, 
    ISNULL(readTodayAgg.TotalStockToday, 0) AS TotalStockToday, 
    SUM(readYesterday.TotalStock) AS TotalStockYesterday, 
    SUM(readYesterday.TotalStock) - ISNULL(readTodayAgg.TotalStockToday, 0) AS Outbound 
FROM ArticleReads readYesterday 
LEFT JOIN (
    -- Aggregate today's stock per ArticleCode + InboundDossier first
    SELECT 
        ArticleCode, 
        InboundDossier,
        SUM(TotalStock) AS TotalStockToday
    FROM ArticleReads
    WHERE ReportDate = DATEADD(DAY, 1, '2018-04-26')
      AND ArticleCode ='ART01234'
      AND ReportDate <> CONVERT(DATE, GETDATE())
    GROUP BY ArticleCode, InboundDossier
) readTodayAgg 
    ON readTodayAgg.ArticleCode = readYesterday.ArticleCode 
    AND readTodayAgg.InboundDossier = readYesterday.InboundDossier
WHERE readYesterday.ArticleCode ='ART01234' 
AND readYesterday.ReportDate = '2018-04-26'
GROUP BY readYesterday.ArticleCode, readYesterday.ReportDate, readTodayAgg.ArticleCode, readTodayAgg.TotalStockToday
ORDER BY readYesterday.ReportDate ASC

Fix 2: Aggregate Both Days Separately (Simpler for Daily Totals)

If you only need the total outbound for the article on that date (not broken down by InboundDossier), aggregate each day's stock independently and calculate the difference. This avoids joins entirely:

WITH YesterdayStock AS (
    SELECT 
        ArticleCode,
        SUM(TotalStock) AS TotalStockYesterday
    FROM ArticleReads
    WHERE ArticleCode ='ART01234' 
      AND ReportDate = '2018-04-26'
    GROUP BY ArticleCode
),
TodayStock AS (
    SELECT 
        ArticleCode,
        SUM(TotalStock) AS TotalStockToday
    FROM ArticleReads
    WHERE ArticleCode ='ART01234' 
      AND ReportDate = DATEADD(DAY, 1, '2018-04-26')
      AND ReportDate <> CONVERT(DATE, GETDATE())
    GROUP BY ArticleCode
)
SELECT 
    y.ArticleCode,
    t.ArticleCode AS ArticleCodeToday,
    '2018-04-26' AS ReportDate,
    ISNULL(t.TotalStockToday, 0) AS TotalStockToday,
    y.TotalStockYesterday,
    y.TotalStockYesterday - ISNULL(t.TotalStockToday, 0) AS Outbound
FROM YesterdayStock y
LEFT JOIN TodayStock t ON y.ArticleCode = t.ArticleCode
ORDER BY ReportDate ASC

内容的提问来源于stack exchange，提问作者Barnabás Kriszt