SQL 如何根据出库表时间统计剩余库存
SQL 怎么根据出库表时间统计剩余库存
库存表 kc 出库表 ck
结构如下： 结构如下：
添加时间 货物件数 货物吨重 出库时间 货物件数 货物吨重
dates js dz dates js dz
2014-10-01 300 30 2014-10-01 100 10
2014-10-01 200 20 2014-10-02 100 10
2014-10-02 200 20 2014-10-02 100 10
2014-10-02 300 30 2014-10-03 100 10
2014-10-05 300 30 2014-10-04 100 10
2014-10-05 500 50 2014-10-05 100 10
2014-10-05 100 10
2014-10-06 100 10
2014-10-07 100 10
2014-10-07 100 10
我想要的结果应该是这样才对
剩余库存查询结果
dates year month day js dz
2014-10-01 2014 10 1 400 40 //一号出库时库存为500件，50吨
2014-10-02 2014 10 1 700 70 //二号出库时库存为900件，90吨(400加当天入库的500)
2014-10-03 2014 10 1 600 60 //以下同理
2014-10-04 2014 10 1 500 50
2014-10-05 2014 10 1 800 80
2014-10-06 2014 10 1 700 70
2014-10-07 2014 10 1 500 50
我的sql是这样的
select b.dates dates,b.year year,b.month month,b.day day,a.js-b.js js,a.dz-b.dz dz from
(select datepart(year,dates) year,datepart(month,dates) month,datepart(day,adddates) day,sum(js) js,sum(dz) dz
from kc group by datepart(year,adddates),datepart(month,adddates),datepart(day,adddates)) a
right join
(select datepart(year,dates) year,datepart(month,dates) month,datepart(day,adddates) day,sum(js) js,sum(dz) dz
from ck where 1=1 and year(dates)=2014 and month(dates)=10 group by datepart(year,dates),datepart(month,dates),datepart(day,dates)) b
on a.month=b.month and a.day=b.day and a.year = b.year
这样查出来是不对的。。请大家帮我想想。。怎么样才能根据出库表的当天出库的数据算出相应的剩余库存
------解决思路----------------------
你的查询SQL中子查询的聚合是做了当天的聚合，并不是直到当天的累计，即sum(当天的发生量)，并非sum(开始-->当天累计发生量)
------解决思路----------------------
------解决思路----------------------
库存表应该做成永久表，新增入库、出库记录时立即更新。
递归计算库存不可取，数据库量大了速度很慢。
库存表 kc 出库表 ck
结构如下： 结构如下：
添加时间 货物件数 货物吨重 出库时间 货物件数 货物吨重
dates js dz dates js dz
2014-10-01 300 30 2014-10-01 100 10
2014-10-01 200 20 2014-10-02 100 10
2014-10-02 200 20 2014-10-02 100 10
2014-10-02 300 30 2014-10-03 100 10
2014-10-05 300 30 2014-10-04 100 10
2014-10-05 500 50 2014-10-05 100 10
2014-10-05 100 10
2014-10-06 100 10
2014-10-07 100 10
2014-10-07 100 10
我想要的结果应该是这样才对
剩余库存查询结果
dates year month day js dz
2014-10-01 2014 10 1 400 40 //一号出库时库存为500件，50吨
2014-10-02 2014 10 1 700 70 //二号出库时库存为900件，90吨(400加当天入库的500)
2014-10-03 2014 10 1 600 60 //以下同理
2014-10-04 2014 10 1 500 50
2014-10-05 2014 10 1 800 80
2014-10-06 2014 10 1 700 70
2014-10-07 2014 10 1 500 50
我的sql是这样的
select b.dates dates,b.year year,b.month month,b.day day,a.js-b.js js,a.dz-b.dz dz from
(select datepart(year,dates) year,datepart(month,dates) month,datepart(day,adddates) day,sum(js) js,sum(dz) dz
from kc group by datepart(year,adddates),datepart(month,adddates),datepart(day,adddates)) a
right join
(select datepart(year,dates) year,datepart(month,dates) month,datepart(day,adddates) day,sum(js) js,sum(dz) dz
from ck where 1=1 and year(dates)=2014 and month(dates)=10 group by datepart(year,dates),datepart(month,dates),datepart(day,dates)) b
on a.month=b.month and a.day=b.day and a.year = b.year
这样查出来是不对的。。请大家帮我想想。。怎么样才能根据出库表的当天出库的数据算出相应的剩余库存
------解决思路----------------------
你的查询SQL中子查询的聚合是做了当天的聚合，并不是直到当天的累计，即sum(当天的发生量)，并非sum(开始-->当天累计发生量)
;with cte1 as
(
select year,month,day,
(select sum(js) from kc b
where convert(datetime,ltrim(b.year)+'-'+ltrim(b.month)+'-'+ltrim(b.day)) <= convert(datetime,ltrim(a.year)+'-'+ltrim(a.month)+'-'+ltrim(a.day))
) as sumjs,
(......同上) as sumdz
from kc a
group by a.year,a.month,a.day
),cte2 as
(
--.......同上，以出库表做累计
)
--两个表按year month day连接做相减得到剩余库存。
------解决思路----------------------
--你原先的库存表明明是入库表，库存要这样算
WITH rk (dates,js,dz) AS ( --入库明细表
SELECT Convert(datetime,'2014-10-01',120),300,30 UNION ALL
SELECT Convert(datetime,'2014-10-01',120),200,20 UNION ALL
SELECT Convert(datetime,'2014-10-02',120),200,20 UNION ALL
SELECT Convert(datetime,'2014-10-02',120),300,30 UNION ALL
SELECT Convert(datetime,'2014-10-05',120),300,30 UNION ALL
SELECT Convert(datetime,'2014-10-05',120),500,50
)
,ck (dates,js,dz) AS ( --出库明细表
SELECT Convert(datetime,'2014-10-01',120),100,10 UNION ALL
SELECT Convert(datetime,'2014-10-02',120),100,10 UNION ALL
SELECT Convert(datetime,'2014-10-02',120),100,10 UNION ALL
SELECT Convert(datetime,'2014-10-03',120),100,10 UNION ALL
SELECT Convert(datetime,'2014-10-04',120),100,10 UNION ALL
SELECT Convert(datetime,'2014-10-05',120),100,10 UNION ALL
SELECT Convert(datetime,'2014-10-05',120),100,10 UNION ALL
SELECT Convert(datetime,'2014-10-06',120),100,10 UNION ALL
SELECT Convert(datetime,'2014-10-07',120),100,10 UNION ALL
SELECT Convert(datetime,'2014-10-07',120),100,10
)
,rks AS ( --入库合计
SELECT Convert(datetime,Convert(varchar(10),dates,120),120) dates, -- 假定明细中有时分秒，截取年月日
SUM(js) js,
SUM(dz) dz
FROM rk
GROUP BY dates
)
,cks AS ( --出库合计
SELECT Convert(datetime,Convert(varchar(10),dates,120),120) dates,
SUM(js) js,
SUM(dz) dz
FROM ck
GROUP BY dates
)
,crk AS ( --出入库
SELECT ROW_NUMBER() OVER(ORDER BY ISNULL(rks.dates,cks.dates)) rn,
ISNULL(rks.dates,cks.dates) dates,
ISNULL(rks.js,0) rk_js,
ISNULL(rks.dz,0) rk_dz,
ISNULL(cks.js,0) ck_js,
ISNULL(cks.dz,0) ck_dz
FROM rks
FULL JOIN cks
ON cks.dates = rks.dates
)
,kc AS ( --库存表（递归）
SELECT rn,
dates,
rk_js - ck_js AS js,
rk_dz - ck_dz AS dz
FROM crk
WHERE rn = 1
UNION ALL
SELECT crk.rn,
crk.dates,
kc.js + crk.rk_js - crk.ck_js AS js,
kc.dz + crk.rk_dz - crk.ck_dz AS dz
FROM kc
JOIN crk
ON crk.rn = kc.rn + 1
)
SELECT dates,
Year(dates) year,
Month(dates) month,
Day(dates) day,
js,
dz
FROM kc
dates year month day js dz
----------------------- ----------- ----------- ----------- ----------- -----------
2014-10-01 00:00:00.000 2014 10 1 400 40
2014-10-02 00:00:00.000 2014 10 2 700 70
2014-10-03 00:00:00.000 2014 10 3 600 60
2014-10-04 00:00:00.000 2014 10 4 500 50
2014-10-05 00:00:00.000 2014 10 5 1100 110
2014-10-06 00:00:00.000 2014 10 6 1000 100
2014-10-07 00:00:00.000 2014 10 7 800 80
------解决思路----------------------
库存表应该做成永久表，新增入库、出库记录时立即更新。
递归计算库存不可取，数据库量大了速度很慢。