SQL CASE WHEN 查询 分配

[gSmilling]

测试数据

create table #t(salordcode varchar(10), cgcode varchar(10),sqtp int,purchasecode varchar(10),fqtp int,tobeqtp int,aqtp int,bqtp int,stock int,planqtp int,)
 
insert into #t(salordcode,cgcode,sqtp,purchasecode,fqtp,tobeqtp,aqtp,bqtp,stock,planqtp)
 select '10001','503200',500,'110249',99,401,1500,0,1500,1 union all
 select '10002','503200',500,'110249',0,500,1500,0,1500,1 union all
 select '10003','503200',600,'110249',0,600,1500,0,1500,1 

想要从图1
转换到图2的效果

应该用case when 来写还是其他什么方法？

分配规则当stock<sum(tobeqtp)时，优先分配给salordcode小的行。

[二月十六]

planqtp哪行什么意思？

[唐诗三百首]

需提供数据处理的逻辑.

[RINK_1]

猜一个，是不是根据salordcode升序来分配stock，如果当前tobeqtp被完全分配，则planqtp显示0，反之为1.

 
 select *,
 case when subtotal<=stock then 0 else 1 end as planqtp_new
 from #t A
 outer apply (select SUM(tobeqtp) as subtotal from #t where cgcode=A.cgcode and salordcode<=A.salordcode) as B

[中国风]

e.g.

 
 SELECT
 *,CASE WHEN sumtobeqtp<stock THEN 0 ELSE 1 END AS planqtp2
 FROM 
 (
 SELECT
 *,SUM(tobeqtp)OVER(PARTITION BY cgcode ORDER BY salordcode) AS sumtobeqtp
FROM #t
) AS t
/*
salordcode	cgcode	sqtp	purchasecode	fqtp	tobeqtp	aqtp	bqtp	stock	planqtp	sumtobeqtp	planqtp2
10001	503200	500	110249	99	401	1500	0	1500	1	401	0
10002	503200	500	110249	0	500	1500	0	1500	1	901	0
10003	503200	600	110249	0	600	1500	0	1500	1	1501	1
*/

[gSmilling]

引用 4 楼 roy_88 的回复:

e.g.

 
 SELECT
 *,CASE WHEN sumtobeqtp<stock THEN 0 ELSE 1 END AS planqtp2
 FROM 
 (
 SELECT
 *,SUM(tobeqtp)OVER(PARTITION BY cgcode ORDER BY salordcode) AS sumtobeqtp
FROM #t
) AS t
/*
salordcode	cgcode	sqtp	purchasecode	fqtp	tobeqtp	aqtp	bqtp	stock	planqtp	sumtobeqtp	planqtp2
10001	503200	500	110249	99	401	1500	0	1500	1	401	0
10002	503200	500	110249	0	500	1500	0	1500	1	901	0
10003	503200	600	110249	0	600	1500	0	1500	1	1501	1
*/

我用你写的这个代码测试就报错了，我没发现那里出错了

[gSmilling]

[gSmilling]

@中国风 伙计我上面那个问题帮我看看。

[吉普赛的歌]

引用 7 楼 gSmilling 的回复:

@中国风 伙计我上面那个问题帮我看看。

伙计？你请教人家还是叫人家大神比较合适

[中国风]

引用 7 楼 gSmilling 的回复:

@中国风 伙计我上面那个问题帮我看看。

CSDN的私信是个BUG，最好自己顶贴，能看到时会回复你。 你的SQL版本不支持语法，改为 e.g.

 SELECT
 *,CASE WHEN sumtobeqtp<stock THEN 0 ELSE 1 END AS planqtp2
 FROM 
 (
 SELECT
 *,(SELECT SUM(tobeqtp) FROM #t WHERE cgcode=t1.cgcode AND salordcode<=t1.salordcode) AS sumtobeqtp
FROM #t AS t1
) AS t

[gSmilling]

感谢中国风大神，根据你重新写的代码做了一些小改动实现了最终目的，非常感谢。