- the raw data in table 1
HU QTY CHARG
3 201006 m002 PH1900930004E
2 201006 m004 PH1900930004E

- the raw data in table 2
HU SERIAL_NUMBER
PH1900930004E A0001
PH1900930004E A0008
PH1900930004E A0003
PH1900930004E A0005
PH1900930004E A0000

- hope to get the results of the
HU SERIAL_NUMBER CHARG
PH1900930004E A0001 m002 201006
PH1900930004E A0008 m002 201006
PH1900930004E A0003 m002 201006
PH1900930004E A0005 m004 201006
PH1900930004E A0000 m004 201006

- logic is CHARG random distribution of table 1 to table 2, but the number on the right, such as table 1 201006 m002 corresponding qty is 3 only randomly assigned to three serial_number, relationship in two tables for HU fields, table 1 qty summed up the article number must be in table 2

CodePudding user response:

Row_number combination of a few times

CodePudding user response:

 
- this problem, in the first two years, 10 minutes someone will reply to you, now cold and cheerless, 

The create table t1 (hu varchar (20), qty int, charg varchar (20)) 
Go 
Insert into t1 values 
(' PH1900930004E, 3, '201006 m002), 
(' PH1900930004E ', 2, '201006 m004) 
Go 


The 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 the as m1 (
Select a t1. Hu, t1 charg, row_number () over (partition by hu order by charg) rn 
The from t1, master.. Spt_values SPT 
Where SPT. Type='p' and SPT. The number & gt; 0 and t1. Qty & gt; .=SPT number 
), 
M2 as (
The select t2. Hu, t2. Serial_number row_number () over (partition by hu order by hu) rn 
The from t2 
) 
Select the m2. Hu, m2 serial_number, m1, charg the from m1, m2 
Where m1. Hu=m2. Hu and m1. Rn=m2. Rn 
Go 
Drop table t1, t2 
Go