合计入库查询的SQL语句
求助:合计入库查询的SQL语句
由表一的入库表,得到每个产品每日的合计入库数量或每日的对应尾数:
入库表
产品编码 报表日期 下单数量 当日入库
01-4045-17 2012-9-2 100 20
01-4045-11 2012-9-3 140 21
01-4045-17 2012-9-3 100 78
01-4045-10 2012-9-4 140 61
01-4045-13 2012-9-4 80 10
01-4045-17 2012-9-4 100 2
01-4045-10 2012-9-8 140 1
01-4045-13 2012-9-8 80 14
01-4045-27 2012-9-8 2000 480
01-4045-22 2012-9-9 400 128
01-4045-27 2012-9-9 2000 240
01-4045-27 2012-9-10 2000 520
01-4045-10 2012-9-11 140 45
01-4045-22 2012-9-11 400 152
01-4045-28 2012-9-11 160 100
分析查询
产品编码 报表日期 下单数量 当日入库 合计入库 当日尾数
01-4045-17 2012-9-2 100 20 20 80
01-4045-11 2012-9-3 140 21 21 119
01-4045-17 2012-9-3 100 78 98 2
01-4045-10 2012-9-4 140 61 61 79
01-4045-13 2012-9-4 80 10 10 70
01-4045-17 2012-9-4 100 2 100 0
01-4045-10 2012-9-8 140 1 1 139
01-4045-13 2012-9-8 80 14 24 56
01-4045-27 2012-9-8 2000 480 480 1520
01-4045-22 2012-9-9 400 128 128 272
01-4045-27 2012-9-9 2000 240 720 1280
01-4045-27 2012-9-10 2000 520 1240 760
01-4045-10 2012-9-11 140 45 45 95
01-4045-22 2012-9-11 400 152 280 120
01-4045-28 2012-9-11 160 100 100 60
------解决方案--------------------
由表一的入库表,得到每个产品每日的合计入库数量或每日的对应尾数:
入库表
产品编码 报表日期 下单数量 当日入库
01-4045-17 2012-9-2 100 20
01-4045-11 2012-9-3 140 21
01-4045-17 2012-9-3 100 78
01-4045-10 2012-9-4 140 61
01-4045-13 2012-9-4 80 10
01-4045-17 2012-9-4 100 2
01-4045-10 2012-9-8 140 1
01-4045-13 2012-9-8 80 14
01-4045-27 2012-9-8 2000 480
01-4045-22 2012-9-9 400 128
01-4045-27 2012-9-9 2000 240
01-4045-27 2012-9-10 2000 520
01-4045-10 2012-9-11 140 45
01-4045-22 2012-9-11 400 152
01-4045-28 2012-9-11 160 100
分析查询
产品编码 报表日期 下单数量 当日入库 合计入库 当日尾数
01-4045-17 2012-9-2 100 20 20 80
01-4045-11 2012-9-3 140 21 21 119
01-4045-17 2012-9-3 100 78 98 2
01-4045-10 2012-9-4 140 61 61 79
01-4045-13 2012-9-4 80 10 10 70
01-4045-17 2012-9-4 100 2 100 0
01-4045-10 2012-9-8 140 1 1 139
01-4045-13 2012-9-8 80 14 24 56
01-4045-27 2012-9-8 2000 480 480 1520
01-4045-22 2012-9-9 400 128 128 272
01-4045-27 2012-9-9 2000 240 720 1280
01-4045-27 2012-9-10 2000 520 1240 760
01-4045-10 2012-9-11 140 45 45 95
01-4045-22 2012-9-11 400 152 280 120
01-4045-28 2012-9-11 160 100 100 60
------解决方案--------------------
- SQL code
---------------------------- -- 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 all select '01-4045-11','2012-9-3',140,21 union all select '01-4045-17','2012-9-3',100,78 union all select '01-4045-10','2012-9-4',140,61 union all select '01-4045-13','2012-9-4',80,10 union all select '01-4045-17','2012-9-4',100,2 union all select '01-4045-10','2012-9-8',140,1 union all select '01-4045-13','2012-9-8',80,14 union all select '01-4045-27','2012-9-8',2000,480 union all select '01-4045-22','2012-9-9',400,128 union all select '01-4045-27','2012-9-9',2000,240 union all select '01-4045-27','2012-9-10',2000,520 union all select '01-4045-10','2012-9-11',140,45 union all select '01-4045-22','2012-9-11',400,152 union all select '01-4045-28','2012-9-11',160,100 go select [产品编码], [报表日期], [下单数量], [当日入库], 合计入库=(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 80 4045-01-11 00:00:00.000 2012-09-03 00:00:00.000 140 21 21 119 4045-01-17 00:00:00.000 2012-09-03 00:00:00.000 100 78 98 2 4045-01-10 00:00:00.000 2012-09-04 00:00:00.000 140 61 61 79 4045-01-13 00:00:00.000 2012-09-04 00:00:00.000 80 10 10 70 4045-01-17 00:00:00.000 2012-09-04 00:00:00.000 100 2 100 0 4045-01-10 00:00:00.000 2012-09-08 00:00:00.000 140 1 62 78 4045-01-13 00:00:00.000 2012-09-08 00:00:00.000 80 14 24 56 4045-01-27 00:00:00.000 2012-09-08 00:00:00.000 2000 480 480 1520 4045-01-22 00:00:00.000 2012-09-09 00:00:00.000 400 128 128 272 4045-01-27 00:00:00.000 2012-09-09 00:00:00.000 2000 240 720 1280 4045-01-27 00:00:00.000 2012-09-10 00:00:00.000 2000 520 1240 760 4045-01-10 00:00:00.000 2012-09-11 00:00:00.000 140 45 107 33 4045-01-22 00:00:00.000 2012-09-11 00:00:00.000 400 152 280 120 4045-01-28 00:00:00.000 2012-09-11 00:00:00.000 160 100 100 60 */