34,576
社区成员
发帖
与我相关
我的任务
分享
declare @t table(ID int,产品ID varchar(10),产量 decimal(10,2), 桶号 varchar(10) , 日期 date, 班次 varchar(10), 入库标记 int)
insert @t select '1','p111','10','A','2017-10-27','早班','0'
insert @t select '2','p111','15','B','2017-10-27','早班','0'
insert @t select '3','p111','25','A','2017-10-27','晚班','0'
insert @t select '4','p111','30','B','2017-10-27','晚班','1'
insert @t select '5','p111','10','C','2017-10-27','晚班','0'
insert @t select '6','p111','30','A','2017-10-28','早班','0'
insert @t select '7','p111','15','B','2017-10-28','早班','0'
insert @t select '8','p111','5','C','2017-10-27','早班','0'
declare @t2 table(id int,桶号 varchar(10),桶重 decimal(10,2))
insert @t2 select 1,'A',7.1
insert @t2 select 1,'B',6.1
insert @t2 select 1,'C',3.1
;with t as(select rid=row_number()over(order by 日期 ,班次 desc),* from @t)
select
产品id
,产量
,a.桶号
,日期
,班次
,当班产量=产量-isnull((select b.产量*(1-入库标记) from t b where rid=(select max(rid) from t c where a.桶号=c.桶号 and c.rid <a.rid)),isnull(t2.桶重,0))
from t a left join @t2 t2 on a.桶号 =t2.桶号
order by rid
产品id 产量 桶号 日期 班次 当班产量
p111 10.00 A 2017-10-27 早班 2.90
p111 15.00 B 2017-10-27 早班 8.90
p111 5.00 C 2017-10-27 早班 1.90
p111 25.00 A 2017-10-27 晚班 15.00
p111 30.00 B 2017-10-27 晚班 15.00
p111 10.00 C 2017-10-27 晚班 5.00
p111 30.00 A 2017-10-28 早班 5.00
p111 15.00 B 2017-10-28 早班 15.00
use Tempdb
go
--> --> 听雨停了-->生成测试数据
if not object_id(N'Tempdb..#tab') is null
drop table #tab
Go
Create table #tab([ID] int,[产品ID] nvarchar(24),[产量] int,[桶号] nvarchar(21),[日期] Date,[班次] nvarchar(22),[入库标记] int)
Insert #tab
select 1,N'p111',10,N'A','2017-10-27',N'早班',0 union all
select 2,N'p111',15,N'B','2017-10-27',N'早班',0 union all
select 3,N'p111',25,N'A','2017-10-27',N'晚班',0 union all
select 4,N'p111',30,N'B','2017-10-27',N'晚班',1 union all
select 5,N'p111',10,N'C','2017-10-27',N'晚班',0 union all
select 6,N'p111',30,N'A','2017-10-28',N'早班',0 union all
select 7,N'p111',15,N'B','2017-10-28',N'早班',0
GO
--测试数据结束
;WITH cte AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY 桶号 ORDER BY 日期 ASC, 班次 DESC) AS
rn
FROM #tab
)
SELECT a.ID,a.产品ID,a.产量,a.桶号,a.日期,a.班次,a.入库标记,
ISNULL(
CASE b.[入库标记]
WHEN 1 THEN a.产量
ELSE a.产量 -b.产量
END,
a.产量
) AS 当班产量
FROM cte a
LEFT JOIN cte b
ON a.桶号 = b.桶号
AND a.rn = b.rn + 1
ORDER BY
日期 ASC,
班次
declare @t table(ID int,产品ID varchar(10),产量 int, 桶号 varchar(10), 日期 date, 班次 varchar(10), 入库标记 int)
insert @t select '1','p111','10','A','2017-10-27','早班','0'
insert @t select '2','p111','15','B','2017-10-27','早班','0'
insert @t select '3','p111','25','A','2017-10-27','晚班','0'
insert @t select '4','p111','30','B','2017-10-27','晚班','1'
insert @t select '5','p111','10','C','2017-10-27','晚班','0'
insert @t select '6','p111','30','A','2017-10-28','早班','0'
insert @t select '7','p111','15','B','2017-10-28','早班','0'
select
产品id
,产量-isnull((select b.产量*(1-入库标记) from @t b where id=(select max(id) from @t c where a.桶号=c.桶号 and c.ID <a.ID)),0)
,桶号
,日期
,班次
from @t a
order by id
/*
产品id (无列名) 桶号 日期 班次
p111 10 A 2017-10-27 早班
p111 15 B 2017-10-27 早班
p111 15 A 2017-10-27 晚班
p111 15 B 2017-10-27 晚班
p111 10 C 2017-10-27 晚班
p111 5 A 2017-10-28 早班
p111 15 B 2017-10-28 早班
*/