27,579
社区成员
发帖
与我相关
我的任务
分享
create table t1(name nvarchar(200),qty int)
create table t2(id int,name nvarchar(200),qty int)
insert into t1(name,qty) values
('a',50),
('b',100),
('c',6),
('d',80)
insert into t2(id,name,qty) values
(1,'a',50),
(2,'a',100),
(3,'b',20),
(4,'b',20),
(5,'b',90),
(6,'c',100),
(7,'d',20),
(8,'d',50)
;with cte as (
select rn=row_number() over(order by id),w.qq-t1.qty as qx,t1.name,t1.qty,w.qty as qty1,w.qq from t1,
(
select id,name,qty,(select sum(qty) from t2 where name=a.name and id<=a.id) as qq from t2 a
) w where t1.name=w.name --and w.qq-t1.qty<w.qty
)
select * from cte where qx<qty1
union all
select * from cte where rn in (select max(rn) from cte group by name) and qx<0
if not object_id(N'Tempdb..#tmp_1') is null
drop table #tmp_1
Go
Create table #tmp_1([NAME] nvarchar(50),[QTY] DECIMAL(18,2))
Insert #tmp_1
select N'a',50 union all
select N'b',100 union all
select N'c',60 union all
select N'd',80
Go
if not object_id(N'Tempdb..#tmp_2') is null
drop table #tmp_2
Go
Create table #tmp_2([ID] int ,[NAME] nvarchar(50),[QTY] DECIMAL(18,2))
Insert #tmp_2
select 1,'a',50 union all
select 2,'a',100 union all
select 3,'b',20 union all
select 4,'b',20 union all
select 5,'b',90 union all
select 6,'c',100 union all
select 7,'d',20 union all
select 8,'d',50
GO
--code
;WITH a AS (
SELECT id,NAME,b.qty ,IIF(total<=0,qty,qty-total) used_qty,total
,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY id DESC ) AS rno
FROM (
SELECT a.id,a.NAME,a.qty,SUM(a.qty) OVER(PARTITION BY a.NAME ORDER BY a.id) AS total
FROM (
SELECT id,NAME,qty from #tmp_2
UNION
SELECT 0,NAME,qty*(-1) from #tmp_1
) a
) b
WHERE b.qty>b.total
)
SELECT a.id,a.NAME,b.qty AS required_qty,a.qty,a.used_qty,IIF (a.rno=1 ,a.total,0) AS surplus_qty
FROM a
LEFT JOIN #tmp_1 b ON b.NAME=a.NAME
ORDER BY a.id
LZ试试--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([NAME] nvarchar(21),[QTY] int)
Insert #T1
select N'a',50 union all
select N'b',100 union all
select N'c',60 union all
select N'd',80
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([ID] int,[NAME] nvarchar(21),[QTY] int)
Insert #T2
select 1,N'a',50 union all
select 2,N'a',100 union all
select 3,N'b',20 union all
select 4,N'b',20 union all
select 5,N'b',90 union all
select 6,N'c',100 union all
select 7,N'd',20 union all
select 8,N'd',50
Go
--测试数据结束
;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY QTY) rn FROM #T1
),cteb AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY ID) rn FROM #T2
),ctec AS (
SELECT cteb.* ,
ctea.QTY - cteb.QTY AS tempqty ,
ctea.rn AS arn
FROM cteb
JOIN ctea ON ctea.NAME = cteb.NAME
AND ctea.rn = cteb.rn
UNION ALL
SELECT cteb.* ,
ctec.tempqty - cteb.QTY ,
ctec.rn
FROM cteb
JOIN ctec ON ctec.NAME = cteb.NAME
AND ctec.rn + 1 = cteb.rn
WHERE ctec.tempqty > 0
)
SELECT NAME ,
QTY
FROM ctec
UNION
SELECT #T1.NAME ,
t.qty - #T1.QTY
FROM #T1
JOIN ( SELECT NAME ,
SUM(QTY) AS qty
FROM ctec
GROUP BY NAME
HAVING MIN(tempqty) > 0
) t ON t.NAME = #T1.NAME
SELECT B.ID, B.NAME, QTY
FROM 表1 A,
(
SELECT ID, NAME, QTY,
QTY_SUM = SUM(QTY)OVER(PARTITION BY NAME ORDER BY ID)
FROM 表2
)B
WHERE A.NAME = B.NAME AND A.QTY >= B.QTY_SUM - B.QTY
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([NAME] nvarchar(50),[QTY] DECIMAL(18,2))
Insert #A
select N'a',50 union all
select N'b',280 union all
select N'c',60 union all
select N'd',80
Go
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([ID] int ,[NAME] nvarchar(50),[QTY] DECIMAL(18,2))
Insert #B
select 1,'a',50 union all
select 2,'a',100 union all
select 3,'b',20 union all
select 4,'b',70 union all
select 5,'b',20 union all
select 6,'b',90 union all
select 7,'c',50 union all
select 8,'c',100 union all
select 9,'d',20 union all
select 10,'d',50
Go
with cte_1
as
(
select B.*,A.QTY AS PICKUP_TOTAL,ROW_NUMBER() OVER (PARTITION BY A.NAME ORDER BY ID) AS SEQ
from #A A
join #B B ON A.NAME=B.NAME
),
cte_2
as
(
select *,cast(QTY as int) AS SUBTOTAL
from cte_1 where SEQ=1
union all
select A.*,cast(B.SUBTOTAL+A.QTY as int)
from cte_1 A
join cte_2 B ON A.NAME=B.NAME AND A.SEQ=B.SEQ+1
where B.SUBTOTAL+A.QTY<=A.PICKUP_TOTAL OR (B.SUBTOTAL<A.PICKUP_TOTAL AND B.SUBTOTAL+A.QTY>A.PICKUP_TOTAL)
)
select name ,qty,seq from cte_2
union all
select name,SUBTOTAL-PICKUP_TOTAL,SEQ+1 from cte_2 A
where not exists (select 1 from cte_2 where A.NAME=NAME and SEQ>A.SEQ)
and SUBTOTAL-PICKUP_TOTAL<0
order by NAME,seq
with a as (
select 'a' as name,50 as qty
union all
select 'b',100
union all
select 'c',60
union all
select 'd',80
),b as (
select 1 as id,'a' as name,50 as qty
union all
select 2,'a',100
union all
select 3,'b',20
union all
select 4,'b',20
union all
select 5,'b',90
union all
select 6,'c',100
union all
select 7,'d',20
union all
select 8,'d',50
),c as (
select *,row_number() over(partition by name order by id) as sn from b
),d as (
select c.*,a.qty as 需求,(case when c.qty>=a.qty then a.qty else c.qty end) as 出库,(case when c.qty>=a.qty then c.qty-a.qty else 0 end) as 剩余 from c left join a on c.name=a.name where sn=1
union all
select c.*,d.需求-d.出库,(case when c.qty>=d.需求-d.出库 then d.需求-d.出库 else c.qty end),(case when c.qty>=d.需求-d.出库 then c.qty-d.需求+d.出库 else 0 end) from c,d where c.name=d.name and c.sn=d.sn+1
)
select id,name,qty from (
select id,name,qty,sn from d where 出库>0
union all
select id,name,出库-需求,sn from d x where 需求>出库 and not exists(select top 1 1 from d where sn=x.sn+1 and name=x.name)
) a
order by name,sn