我将至少假设使用SQL Server2005。将来，请使用您需要支持的最低版本指定/标记问题。
CREATE TABLE #x( TransactionDate SMALLDATETIME, Item CHAr(1), RecQty INT, IssueQty INT);INSERT #x SELECt '20110601','A',10,0UNIOn ALL SELECT '20110602','A',15,0UNIOn ALL SELECT '20110603','A',20,0UNIOn ALL SELECT '20110604','A',0,20UNIOn ALL SELECT '20110604','A',0,20;DECLARE @StartDate SMALLDATETIME = '20110601', @Date SMALLDATETIME = '20110602';WITH x(Item, prevR, prevI, curR, curI) AS( SELECT Item, SUM(CASE WHEN TransactionDate < @Date THEN RecQty ELSE 0 END), SUM(CASE WHEN TransactionDate < @Date THEN IssueQty ELSE 0 END), SUM(CASE WHEN TransactionDate = @Date THEN RecQty ELSE 0 END), SUM(CASE WHEN TransactionDate = @Date THEN IssueQty ELSE 0 END) FROM #x WHERe TransactionDate BETWEEN @StartDate AND @Date GROUP BY Item)SELECt Item, Opening = prevR - prevI, RecQty = curR, IssueQty = curI, BalanceQty = (prevR - prevI) + (curR - curI)FROM x;DROP TABLE #x;
