求助：合计入库查询的SQL语句
由表一的入库表，得到每个产品每日的合计入库数量或每日的对应尾数：
入库表
产品编码报表日期下单数量当日入库
01-4045-172012-9-210020
01-4045-112012-9-314021
01-4045-172012-9-310078
01-4045-102012-9-414061
01-4045-132012-9-48010
01-4045-172012-9-41002
01-4045-102012-9-81401
01-4045-132012-9-88014
01-4045-272012-9-82000480
01-4045-222012-9-9400128
01-4045-272012-9-92000240
01-4045-272012-9-102000520
01-4045-102012-9-1114045
01-4045-222012-9-11400152
01-4045-282012-9-11160100
分析查询
产品编码报表日期下单数量当日入库合计入库当日尾数
01-4045-172012-9-2100202080
01-4045-112012-9-31402121119
01-4045-172012-9-310078982
01-4045-102012-9-4140616179
01-4045-132012-9-480101070
01-4045-172012-9-410021000
01-4045-102012-9-814011139
01-4045-132012-9-880142456
01-4045-272012-9-820004804801520
01-4045-222012-9-9400128128272
01-4045-272012-9-920002407201280
01-4045-272012-9-1020005201240760
01-4045-102012-9-11140454595
01-4045-222012-9-11400152280120
01-4045-282012-9-1116010010060
[解决办法]
------------------------------ Author :TravyLee(物是人非事事休，欲语泪先流！)-- Date :2012-09-17 10:35:11-- Version:-- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) -- Oct 14 2005 00:33:37 -- Copyright (c) 1988-2005 Microsoft Corporation-- Developer Edition on Windows NT 6.1 (Build 7601: Service Pack 1)--------------------------------> 测试数据：[入库表]if object_id('[入库表]') is not null drop table [入库表]go create table [入库表]([产品编码] datetime,[报表日期] datetime,[下单数量] int,[当日入库] int)insert [入库表]select '01-4045-17','2012-9-2',100,20 union allselect '01-4045-11','2012-9-3',140,21 union allselect '01-4045-17','2012-9-3',100,78 union allselect '01-4045-10','2012-9-4',140,61 union allselect '01-4045-13','2012-9-4',80,10 union allselect '01-4045-17','2012-9-4',100,2 union allselect '01-4045-10','2012-9-8',140,1 union allselect '01-4045-13','2012-9-8',80,14 union allselect '01-4045-27','2012-9-8',2000,480 union allselect '01-4045-22','2012-9-9',400,128 union allselect '01-4045-27','2012-9-9',2000,240 union allselect '01-4045-27','2012-9-10',2000,520 union allselect '01-4045-10','2012-9-11',140,45 union allselect '01-4045-22','2012-9-11',400,152 union allselect '01-4045-28','2012-9-11',160,100goselect [产品编码], [报表日期], [下单数量], [当日入库], 合计入库=(select sum([当日入库]) from [入库表] b where a.[产品编码]=b.[产品编码] and a.[报表日期]>=b.[报表日期]), 当日尾数=[下单数量]-(select sum([当日入库]) from [入库表] b where a.[产品编码]=b.[产品编码] and a.[报表日期]>=b.[报表日期])from [入库表] a/*4045-01-17 00:00:00.000 2012-09-02 00:00:00.000 100 20 20 804045-01-11 00:00:00.000 2012-09-03 00:00:00.000 140 21 21 1194045-01-17 00:00:00.000 2012-09-03 00:00:00.000 100 78 98 24045-01-10 00:00:00.000 2012-09-04 00:00:00.000 140 61 61 794045-01-13 00:00:00.000 2012-09-04 00:00:00.000 80 10 10 704045-01-17 00:00:00.000 2012-09-04 00:00:00.000 100 2 100 04045-01-10 00:00:00.000 2012-09-08 00:00:00.000 140 1 62 784045-01-13 00:00:00.000 2012-09-08 00:00:00.000 80 14 24 564045-01-27 00:00:00.000 2012-09-08 00:00:00.000 2000 480 480 15204045-01-22 00:00:00.000 2012-09-09 00:00:00.000 400 128 128 2724045-01-27 00:00:00.000 2012-09-09 00:00:00.000 2000 240 720 12804045-01-27 00:00:00.000 2012-09-10 00:00:00.000 2000 520 1240 7604045-01-10 00:00:00.000 2012-09-11 00:00:00.000 140 45 107 334045-01-22 00:00:00.000 2012-09-11 00:00:00.000 400 152 280 1204045-01-28 00:00:00.000 2012-09-11 00:00:00.000 160 100 100 60*/
[解决办法]
--> 测试数据：[入库表]IF OBJECT_ID('[入库表]') IS NOT NULL DROP TABLE [入库表]GO CREATE TABLE [入库表]([产品编码] DATETIME,[报表日期] DATETIME,[下单数量] INT,[当日入库] INT)INSERT [入库表]SELECT '01-4045-17','2012-9-2',100,20 UNION ALLSELECT '01-4045-11','2012-9-3',140,21 UNION ALLSELECT '01-4045-17','2012-9-3',100,78 UNION ALLSELECT '01-4045-10','2012-9-4',140,61 UNION ALLSELECT '01-4045-13','2012-9-4',80,10 UNION ALLSELECT '01-4045-17','2012-9-4',100,2 UNION ALLSELECT '01-4045-10','2012-9-8',140,1 UNION ALLSELECT '01-4045-13','2012-9-8',80,14 UNION ALLSELECT '01-4045-27','2012-9-8',2000,480 UNION ALLSELECT '01-4045-22','2012-9-9',400,128 UNION ALLSELECT '01-4045-27','2012-9-9',2000,240 UNION ALLSELECT '01-4045-27','2012-9-10',2000,520 UNION ALLSELECT '01-4045-10','2012-9-11',140,45 UNION ALLSELECT '01-4045-22','2012-9-11',400,152 UNION ALLSELECT '01-4045-28','2012-9-11',160,100--------------开始查询--------------------------SELECT *,合计入库=(SELECT SUM([当日入库]) FROM [入库表] WHERE [产品编码]=t.[产品编码] AND [报表日期]<=t.[报表日期]),当日尾数=[下单数量]-(SELECT SUM([当日入库]) FROM [入库表] WHERE [产品编码]=t.[产品编码] AND [报表日期]<=t.[报表日期])FROM [入库表] AS t----------------结果----------------------------/* 产品编码 报表日期 下单数量 当日入库 合计入库 当日尾数4045-01-17 00:00:00.000 2012-09-02 00:00:00.000 100 20 20 804045-01-11 00:00:00.000 2012-09-03 00:00:00.000 140 21 21 1194045-01-17 00:00:00.000 2012-09-03 00:00:00.000 100 78 98 24045-01-10 00:00:00.000 2012-09-04 00:00:00.000 140 61 61 794045-01-13 00:00:00.000 2012-09-04 00:00:00.000 80 10 10 704045-01-17 00:00:00.000 2012-09-04 00:00:00.000 100 2 100 04045-01-10 00:00:00.000 2012-09-08 00:00:00.000 140 1 62 784045-01-13 00:00:00.000 2012-09-08 00:00:00.000 80 14 24 564045-01-27 00:00:00.000 2012-09-08 00:00:00.000 2000 480 480 15204045-01-22 00:00:00.000 2012-09-09 00:00:00.000 400 128 128 2724045-01-27 00:00:00.000 2012-09-09 00:00:00.000 2000 240 720 12804045-01-27 00:00:00.000 2012-09-10 00:00:00.000 2000 520 1240 7604045-01-10 00:00:00.000 2012-09-11 00:00:00.000 140 45 107 334045-01-22 00:00:00.000 2012-09-11 00:00:00.000 400 152 280 1204045-01-28 00:00:00.000 2012-09-11 00:00:00.000 160 100 100 60*/