22,301
社区成员




create table #sp
(bh char(8) null default '',
kh char(2) null default '',
plh char(10) null default '',
shl int,
snn int IDENTITY (1, 1) NOT NULL
)
create table #ls
(bh char(8) null default '',
kh char(2) null default '',
shl int
)
insert into #sp
(bh,kh,plh,shl)
select '100001','1','10111',20
union
select '100001','1','10132',10
union
select '100001','1','10133',20
union
select '100001','1','10134',20
union
select '100001','2','10111',12
union
select '100001','2','10132',1
union
select '100001','2','10234',230
union
select '100001','2','10235',25
union
select '100002','1','10111',20
union
select '100002','1','10132',10
union
select '100002','1','10133',20
union
select '100002','1','10134',20
union
select '100002','2','10111',12
union
select '100002','2','10132',1
union
select '100002','2','10234',230
union
select '100002','2','10235',25
insert into #ls
(bh,kh,shl)
select '100001','1',50
union
select '100001','2',40
union
select '100002','1',30
union
select '100002','2',10
select * from #sp
select * from #ls
create table #sp
(bh char(8) null default '',
kh char(2) null default '',
plh char(10) null default '',
shl int,
snn int IDENTITY (1, 1) NOT NULL
)
create table #ls
(bh char(8) null default '',
kh char(2) null default '',
shl int
)
insert into #sp
(bh,kh,plh,shl)
select '100001','1','10111',20
union
select '100001','1','10132',10
union
select '100001','1','10133',20
union
select '100001','1','10134',20
union
select '100001','2','10111',12
union
select '100001','2','10132',1
union
select '100001','2','10234',230
union
select '100001','2','10235',25
union
select '100002','1','10111',20
union
select '100002','1','10132',10
union
select '100002','1','10133',20
union
select '100002','1','10134',20
union
select '100002','2','10111',12
union
select '100002','2','10132',1
union
select '100002','2','10234',230
union
select '100002','2','10235',25
insert into #ls
(bh,kh,shl)
select '100001','1',90
union
select '100001','2',40
union
select '100002','1',30
union
select '100002','2',10
---想要的结果为
-----------------------------------------------
100001 1 10111 0 1
100001 1 10132 0 2
100001 1 10133 0 3
100001 1 10134 -20 4
100001 2 10111 0 5
100001 2 10132 0 6
100001 2 10234 203 7
100001 2 10235 25 8
100002 1 10111 0 9
100002 1 10132 0 10
100002 1 10133 20 11
100002 1 10134 20 12
100002 2 10111 2 13
100002 2 10132 1 14
100002 2 10234 230 15
100002 2 10235 25 16
--因为查询中有null值未判断,现在修改为下面
select b.bh,b.kh,b.plh,sl=case when b.kcsl-ls.shl<=0 then 0
when b.kcsl-ls.shl>0 and
(select isnull(sum(shl),0) from sp
where sp.bh=b.bh and sp.kh=b.kh and sp.snn<b.snn)<ls.shl
then b.kcsl-ls.shl else b.shl end,b.snn
from
(select *,kcsl=(select sum(shl) from sp a
where a.bh=sp.bh and a.kh=sp.kh and a.snn<=sp.snn) from sp) b
left join ls
on ls.bh=b.bh and ls.kh=b.kh
--> 生成测试数据表: [sp]
IF OBJECT_ID('[sp]') IS NOT NULL
DROP TABLE [sp]
GO
CREATE TABLE [sp] ([bh] [int],[kh] [int],[plh] [int],[shl] [int],[snn] [int])
INSERT INTO [sp]
SELECT '100001','1','10111','20','1' UNION ALL
SELECT '100001','1','10132','10','2' UNION ALL
SELECT '100001','1','10133','20','3' UNION ALL
SELECT '100001','1','10134','20','4' UNION ALL
SELECT '100001','2','10111','12','5' UNION ALL
SELECT '100001','2','10132','1','6' UNION ALL
SELECT '100001','2','10234','230','7' UNION ALL
SELECT '100001','2','10235','25','8' UNION ALL
SELECT '100002','1','10111','20','9' UNION ALL
SELECT '100002','1','10132','10','10' UNION ALL
SELECT '100002','1','10133','20','11' UNION ALL
SELECT '100002','1','10134','20','12' UNION ALL
SELECT '100002','2','10111','12','13' UNION ALL
SELECT '100002','2','10132','1','14' UNION ALL
SELECT '100002','2','10234','230','15' UNION ALL
SELECT '100002','2','10235','25','16'
--> 生成测试数据表: [ls]
IF OBJECT_ID('[ls]') IS NOT NULL
DROP TABLE [ls]
GO
CREATE TABLE [ls] ([bh] [int],[kh] [int],[shl] [int])
INSERT INTO [ls]
SELECT '100001','1','50' UNION ALL
SELECT '100001','2','40' UNION ALL
SELECT '100002','1','30' UNION ALL
SELECT '100002','2','10'
--SELECT * FROM [sp]
--SELECT * FROM [ls]
-->SQL查询如下:
;WITH t AS
(
SELECT rn = ROW_NUMBER()OVER(ORDER BY bh, kh, [snn]),*
FROM sp
),t1 AS
(
SELECT *,
tmpsum = (
SELECT SUM(shl)
FROM t
WHERE bh = a.bh
AND kh = a.kh
AND rn <= a.rn
)
FROM t a
)
UPDATE a
SET a.shl = a.shl -CASE
WHEN a.shl -(a.tmpsum -b.shl) <= 0 THEN 0
WHEN a.tmpsum -b.shl <= 0 THEN a.shl
ELSE a.shl -(a.tmpsum -b.shl)
END
FROM t1 a
JOIN (SELECT bh,kh,SUM(shl) shl
FROM ls
GROUP BY bh,kh
) b
ON a.bh = b.bh
AND a.kh = b.kh
SELECT * FROM sp
/*
bh kh plh shl snn
----------- ----------- ----------- ----------- -----------
100001 1 10111 0 1
100001 1 10132 0 2
100001 1 10133 0 3
100001 1 10134 20 4
100001 2 10111 0 5
100001 2 10132 0 6
100001 2 10234 203 7
100001 2 10235 25 8
100002 1 10111 0 9
100002 1 10132 0 10
100002 1 10133 20 11
100002 1 10134 20 12
100002 2 10111 2 13
100002 2 10132 1 14
100002 2 10234 230 15
100002 2 10235 25 16
(16 行受影响)
*/
--这个应该可以
select b.bh,b.kh,b.plh,sl=case when b.kcsl-ls.shl<=0 and b.kcsl<b.zsl then 0
when b.kcsl-ls.shl<=0 and b.kcsl=b.zsl then b.kcsl-ls.shl
when b.kcsl-ls.shl>0 and
(select isnull(sum(shl),0) from sp
where sp.bh=b.bh and sp.kh=b.kh and sp.snn<b.snn)<ls.shl
then b.kcsl-ls.shl else b.shl end,b.snn
from
(select *,kcsl=(select sum(shl) from sp a
where a.bh=sp.bh and a.kh=sp.kh and a.snn<=sp.snn),
zsl=(select sum(shl) from sp a
where a.bh=sp.bh and a.kh=sp.kh) from sp) b
left join ls
on ls.bh=b.bh and ls.kh=b.kh
--猜一猜,忽略plh字段
select b.*,即时库存数量=ls.shl-b.kcsl from
(select *,kcsl=(select sum(shl) from sp a where a.bh=sp.bh and a.kh=sp.kh and a.snn<=sp.snn) from sp) b
left join ls
on ls.bh=b.bh and ls.kh=b.kh
create table #sp
(bh char(8) null default '',
kh char(2) null default '',
plh char(10) null default '',
shl int,
snn int IDENTITY (1, 1) NOT NULL
)
create table #ls
(bh char(8) null default '',
kh char(2) null default '',
shl int
)
insert into #sp
(bh,kh,plh,shl)
select '100001','1','10111',20
union
select '100001','1','10132',10
union
select '100001','1','10133',20
union
select '100001','1','10134',20
union
select '100001','2','10111',12
union
select '100001','2','10132',1
union
select '100001','2','10234',230
union
select '100001','2','10235',25
union
select '100002','1','10111',20
union
select '100002','1','10132',10
union
select '100002','1','10133',20
union
select '100002','1','10134',20
union
select '100002','2','10111',12
union
select '100002','2','10132',1
union
select '100002','2','10234',230
union
select '100002','2','10235',25
insert into #ls
(bh,kh,shl)
select '100001','1',50
union
select '100001','2',40
union
select '100002','1',30
union
select '100002','2',10
SELECT
a.snn,a.bh,a.kh,a.plh,a.shl,
庫存=CASE WHEN b.sshl>=a.sshl THEN 0 WHEN b.sshl>a.sshl-a.shl THEN b.sshl-(a.sshl-a.shl) ELSE a.shl END
FROM (select *,(SELECT SUM(shl) FROM #sp WHERE bh=a.bh AND kh=a.kh AND snn<=a.snn) AS sshl from #sp AS a) AS a
LEFT JOIN (SELECT bh,kh,SUM(shl) AS sshl FROM #ls GROUP BY bh,kh) AS b ON a.bh=b.bh AND a.kh=b.kh
/*
snn bh kh plh shl 庫存
1 100001 1 10111 20 0
2 100001 1 10132 10 0
3 100001 1 10133 20 0
4 100001 1 10134 20 20
5 100001 2 10111 12 0
6 100001 2 10132 1 0
7 100001 2 10234 230 27
8 100001 2 10235 25 25
9 100002 1 10111 20 0
10 100002 1 10132 10 0
11 100002 1 10133 20 20
12 100002 1 10134 20 20
13 100002 2 10111 12 10
14 100002 2 10132 1 1
15 100002 2 10234 230 230
16 100002 2 10235 25 25
*/
--因为查询中有null值未判断,现在修改为下面
select b.bh,b.kh,b.plh,sl=case when b.kcsl-ls.shl<=0 then 0
when b.kcsl-ls.shl>0 and
(select isnull(sum(shl),0) from sp
where sp.bh=b.bh and sp.kh=b.kh and sp.snn<b.snn)<ls.shl
then b.kcsl-ls.shl else b.shl end,b.snn
from
(select *,kcsl=(select sum(shl) from sp a
where a.bh=sp.bh and a.kh=sp.kh and a.snn<=sp.snn) from sp) b
left join ls
on ls.bh=b.bh and ls.kh=b.kh