有2张表,分别是进货表和消耗表，每次只从一个供应商取货，消耗完库存在新进货
进货表是
MaterialName Specision weight Time Supplier
柴油 0# 10 2014-09-03 港口
柴油 0# 10 2014-09-12 港口
柴油 0# 20 2014-09-26 中石化
柴油 0# 10 2014-10-11 中石化
柴油 0# 15 2014-10-26 港口
消耗表是
OilDate Oilstate Oil Specision
2014-09-08 柴油 5 0#
2014-09-09 柴油 5 0#
2014-09-14 柴油 10 0#
2014-09-28 柴油 18 0#
2014-10-09 柴油 2 0#
2014-10-15 柴油 10 0#
2014-10-27 柴油 10 0#
我想得到的是表(WEIGHT表示进货量,OIL表示消耗量)
DATE SPECISION WEIGHT OIL SUPPLIER
2014-09 0# 20 20 港口
2014-09 0# 20 18 中石化
2014-10 0# 15 10 港口
2014-10 0# 10 12 中石化
请问这个应该怎么实现
------解决思路----------------------
if OBJECT_ID('inweight') is not null
drop table inweight
create table inweight
(
materialName varchar(20),
Specision varchar(20),
weight int ,
[time] date,
Supplier varchar(20)
)
if OBJECT_ID('outweight') is not null
drop table outweight
create table outweight
(
OilDate date,
Oilstate varchar(20),
Oil int ,
Specision varchar(20)
)
insert into inweight
select '柴油','0#',10,'2014-09-03','港口' union
select '柴油','0#',10,'2014-09-12','港口' union
select '柴油','0#',20,'2014-09-26','中石化' union
select '柴油','0#',10,'2014-10-11','中石化' union
select '柴油','0#',15,'2014-10-26','港口'
insert into outweight
select '2014-09-08','柴油',5,'0# ' union
select '2014-09-09','柴油',5,'0# ' union
select '2014-09-14','柴油',10,'0# ' union
select '2014-09-28','柴油',18,'0# ' union
select '2014-10-09','柴油',2,'0# ' union
select '2014-10-15','柴油',10,'0# ' union
select '2014-10-27','柴油',10,'0# ' 默默的帮顶
------解决思路----------------------
只跟你把这个求出来了
select t1.*,t2.lastday from
(
select CONVERT(varchar(7),[Time],121) as yearmonth,Supplier,SUM([weight]) as weight
from inweight group by CONVERT(varchar(7),[Time],121),Supplier
) t1
inner join (
select CONVERT(varchar(7),[Time],121) yearmonth,Supplier,MAX([Time]) as lastday
from inweight group by CONVERT(varchar(7),[Time],121),Supplier
) t2 on t1.yearmonth=t2.yearmonth and t1.Supplier=t2.Supplier
结果
------解决思路----------------------
你的业务都已经实际发生了，没有按进出明细算的必要，直接按期望结果统计好了。
-- 进货按规格、月份、供应商统计
-- 又：specision 没这个单词，只有 specification
WITH indetail AS (
SELECT Convert(varchar(7),time,120) date,
specision,
weight,
supplier
FROM inweight
)
,insum AS (
SELECT date,
specision,
SUM(weight) weight,
supplier,
ROW_NUMBER() OVER(PARTITION BY specision ORDER BY date, supplier) specNo,
ROW_NUMBER() OVER(PARTITION BY specision,date ORDER BY supplier) monthNo
FROM indetail
GROUP BY specision,date,supplier
)
-- 消耗按规格、月份统计
,outdetail AS (
SELECT Convert(varchar(7),oildate,120) date,
specision,
oil
FROM outweight
)
,outsum AS (
SELECT date,
specision,
SUM(oil) oil,
1 monthNo
FROM outdetail
GROUP BY specision,date
)
-- 因为下面result递归部分不能用 LEFT JOIN，所以进货、消耗要先拼起来
,allsum AS (
SELECT i.date,
i.specision,
i.weight,
ISNULL(o.oil,0) oil,
i.supplier,
i.specNo,
i.monthNo
FROM insum i
LEFT JOIN outsum o
ON o.specision = i.specision
AND o.date = i.date
AND o.monthNo = i.monthNo
)
--SELECT * FROM allsum
,result AS (
-- 每个规格的第一条
SELECT s.date,
s.specision,
s.weight,
CASE WHEN s.weight - s.oil < 0 THEN
s.weight
ELSE
s.oil
END oil,
s.supplier,
s.specNo,
s.monthNo,
s.weight - s.oil stock, -- 负数表示剩余消耗量
Convert(nvarchar(15),NULL) msg
FROM allsum s
WHERE s.specNo = 1
-- 同规格、下月，进货、消耗一同算
UNION ALL
SELECT s.date,
s.specision,
s.weight,
CASE WHEN (r.stock + s.weight - s.oil) < 0 THEN
r.stock + s.weight
ELSE
ABS(r.stock) + s.oil
END oil,
s.supplier,
s.specNo,
s.monthNo,
r.stock + s.weight - s.oil stock,
CASE WHEN (r.stock < 0) and (s.monthNo = 1) THEN
Convert(nvarchar(15),N'上月底不应该有剩余消耗量')
ELSE
Convert(nvarchar(15),NULL)
END msg
FROM result r
JOIN allsum s
ON s.specision = r.specision
AND s.specNo = r.specNo + 1
)
SELECT * FROM result
date specision weight oil supplier specNo monthNo stock msg
------- -------------------- ----------- ----------- -------------------- -------------------- -------------------- ----------- ---------------
2014-09 0# 20 20 港口 1 1 -18 NULL
2014-09 0# 20 18 中石化 2 2 2 NULL
2014-10 0# 15 17 港口 3 1 -5 NULL
2014-10 0# 10 5 中石化 4 2 5 NULL