您好：
若有兩個
左邊是產品 累加
右邊是 供應商排序後累加

請問該如何於SQL 中，分配左邊的累加， 參考右邊的排序累加 ?
有試過

CREATE TABLE PRD (
 SN varchar(8),
 DATETIMEX DATETIME,
 WET numeric(18,3)

)

INSERT INTO PRD VALUES('A001','2023-09-24 01:00:02.000',18.01  ),
('A002','2023-09-24 01:30:02.000' ,18.02 ),
('A003','2023-09-24 02:00:02.000' ,18.03 ),
('A004','2023-09-24 02:30:02.000'  ,5.01),
('A005','2023-09-24 03:00:02.000' ,5.02 ),
('A006','2023-09-24 03:30:02.000'  ,18.01),
('A007','2023-09-24 04:00:02.000'  ,18.02),
('A008','2023-09-24 04:30:02.000'  ,18.00),
('A009','2023-09-24 05:00:02.000'  ,18.03),
('A010','2023-09-24 05:30:02.000'  ,18.04)


SELECT * FROM (
		SELECT *,
		ROW_NUMBER() OVER(order by DATETIMEX) AS RW, --排序
		sum(wet) OVER(ORDER BY DATETIMEX) sum_MORE  --排序累加
		FROM prd
)A ORDER BY  RW


create table sup(
sn varchar(10),
supno VARCHAR(10),
WET numeric(18,3)
)

INSERT into sup VALUES('S001','C01',54.0),('S002','F01',46.3),('S003','D01',57.9)


drop TABLE sup

SELECT *, 
ROW_NUMBER( ) OVER(ORDER BY　sn) rw,
SUM(WET) OVER(order by sn) WET_T
from SUP


;WITH AA AS(
	SELECT * FROM (
			SELECT *,
			ROW_NUMBER() OVER(order by DATETIMEX) AS RW, --排序
			sum(wet) OVER(ORDER BY DATETIMEX) sum_MORE  --排序累加
			FROM prd
	)A 
),BB AS (
	SELECT *, 
	ROW_NUMBER( ) OVER(ORDER BY　sn) rw,
	SUM(WET) OVER(order by sn) WET_T
	from SUP

)
select * from AA CROSS APPLY (SELECT * from  BB WHERE AA.sum_more<=BB.WET_T ) B2

但這樣累加，就會重複出現

2.另外，為求分配較完整
因為左(PRD)累加，並不一定右邊(SUP)累加
所以是否可以做到 比對右邊時，
右邊排序單數 ， 左邊超過 右邊
右邊排序復數 ， 左邊不超過 右邊

謝謝