逻辑题，比较急，难受

[听雨停了]

--原始数据表1
HU QTY CHARG
PH1900930004E 3 201006M002
PH1900930004E 2 201006M004

--原始数据表2
HU SERIAL_NUMBER
PH1900930004E A0001
PH1900930004E A0008
PH1900930004E A0003
PH1900930004E A0005
PH1900930004E A0000

--希望得到的结果
HU SERIAL_NUMBER CHARG
PH1900930004E A0001 201006M002
PH1900930004E A0008 201006M002
PH1900930004E A0003 201006M002
PH1900930004E A0005 201006M004
PH1900930004E A0000 201006M004

--逻辑就是把表1的CHARG随机分配到表2里面，但是数量得对上，比如表1的201006M002对应的qty是3则只随机分配3个serial_number,两个表的关联关系为HU字段，表1的QTY合计一定是表2的条数

[砸死牛顿的苹果]

row_number的几次组合

[卖水果的net]

-- 这个问题，在 前两年，10 分钟就会有人回复你，现在冷清了。

create table t1(hu varchar(20), qty int, charg varchar(20))
go
insert into t1 values
('PH1900930004E', 3, '201006M002'),
('PH1900930004E', 2, '201006M004')
go


create table t2(hu varchar(20), serial_number varchar(20))
go
insert into t2 values
('PH1900930004E', 'A0001'),
('PH1900930004E', 'A0008'),
('PH1900930004E', 'A0003'),
('PH1900930004E', 'A0005'),
('PH1900930004E', 'A0000')
go
with m1 as(
select t1.hu, t1.charg, row_number() over(partition by hu order by charg) rn 
from t1,master..spt_values spt 
where spt.type = 'p' and spt.number > 0 and t1.qty >= spt.number
),
m2 as (
select t2.hu, t2.serial_number, row_number() over(partition by hu order by hu) rn
from t2
)
select m2.hu, m2.serial_number, m1.charg from m1, m2
where m1.hu = m2.hu and m1.rn = m2.rn
go
drop table t1, t2
go

[中国风]

e.g.

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([HU] nvarchar(113),[QTY] bigint,[CHARG] nvarchar(110))
Insert #T1
select N'PH1900930004E',3,N'201006M002' union all
select N'PH1900930004E',2,N'201006M004'

--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([HU] nvarchar(113),[SERIAL_NUMBER] nvarchar(105))
Insert #T2
select N'PH1900930004E',N'A0001' union all
select N'PH1900930004E',N'A0008' union all
select N'PH1900930004E',N'A0003' union all
select N'PH1900930004E',N'A0005' union all
select N'PH1900930004E',N'A0000'
GO
SELECT t2.HU,t2.SERIAL_NUMBER,t1.CHARG
FROM (SELECT *
			,SUM(QTY) OVER (PARTITION BY HU
							ORDER BY HU
							ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SumQty
	  FROM #T1) AS t1 INNER JOIN (SELECT *
										,ROW_NUMBER() OVER (PARTITION BY HU ORDER BY NEWID()) AS RN
								  FROM #T2) AS t2 ON t1.HU=t2.HU AND t2.RN>t1.SumQty-t1.QTY AND t2.RN<=t1.SumQty;
/*
HU	SERIAL_NUMBER	CHARG
PH1900930004E	A0001	201006M002
PH1900930004E	A0003	201006M002
PH1900930004E	A0005	201006M002
PH1900930004E	A0000	201006M004
PH1900930004E	A0008	201006M004
*/

[砸死牛顿的苹果]

引用三楼的 :

ORDER BY HU
                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

这里是否也可以写成

ORDER BY CHARG

结果一致

[xiaoxiangqing]

还有这种需求？

[听雨停了]

感谢各位老铁