Random relationship of SQL Server and Oracle

There are requirements as follows:

Now it's time to tb1 the song field. The condition is to go to Tb2 to find songs sung by the same artist, randomly populated with the song name field in TB1
A singer not only sings a song, so TB2 is a collection of all the songs the artist sings. The same singer in Tb1 may appear several times.
It is necessary to find the song name of the same artist in the TB2, which is the artist's song summary table, according to the tb1 artist's name.
Need to randomly take the name of the TB2 in the name of the artist in the same case. One by one tb1 fields in the TB1

Tb1
Artist Singing song name
A null
b NULL
C NULL
A null
s NULL
D NULL
e null

Tb2

Artist Singing song name
A AA
A AB
b bb
b ba
b BBB
D DD
D D2
F DDD
C CC

Artist Singing song name
A AA (the artist name in TB1 will appear several times each time in TB2, as long as a random bar to fill)
A AB

b bb
D DD
C CC

=========================================================

One, the final SQL results

1, the implementation of SQL Server:

The CREATE TABLE TB1 (ID varchar),--requires the primary key of the table yr varchar (YCGQM), the CREATE TABLE TB2 (ID varchar),--table's primary key (can NO) yr varchar (ycgqm varchar) insert into TB1 (ID,YR,YCGQM) VALUES (NEWID (), ' a ', null); INSERT INTO TB1 (ID,YR,YCGQM VALUES (NEWID (), ' B ', null), insert into TB1 (ID,YR,YCGQM) VALUES (NEWID (), ' E ', null), insert into TB1 (ID,YR,YCGQM) VALUES ( NEWID (), ' a ', null), insert into TB1 (ID,YR,YCGQM) VALUES (NEWID (), ' s ', null), insert into TB1 (ID,YR,YCGQM) VALUES (NEWID (), ' d ', null), insert into TB1 (ID,YR,YCGQM) VALUES (NEWID (), ' E ', null), insert into TB1 (ID,YR,YCGQM) VALUES (NEWID (), ' a ', null Insert into TB2 (ID,YR,YCGQM) VALUES (NEWID (), ' A ', ' AA '), insert into TB2 (ID,YR,YCGQM) VALUES (NEWID (), ' A ', ' ab '), insert Into TB2 (ID,YR,YCGQM) VALUES (NEWID (), ' B ', ' BB '), insert into TB2 (ID,YR,YCGQM) VALUES (NEWID (), ' B ', ' BA '), insert into TB2 ( ID,YR,YCGQM) VALUES (NEWID (), ' B ', ' BBB '), insert into TB2 (ID,YR,YCGQM) VALUES (NEWID (), ' d ', ' DD '), insert into TB2 (Id,yr, YCGQM) VALUES (NEWID (), ' d ', ' D2 '); insert into TB2 (ID,YR,YCGQMVALUES (NEWID (), ' f ', ' ddd '), insert into TB2 (ID,YR,YCGQM) VALUES (NEWID (), ' C ', ' CC ') and insert into TB2 (ID,YR,YCGQM) values  (NEWID (), ' A ', ' AC '), Update Tb1set ycgqm= (select Bycgqm from (select T.*, Row_number () Over (PARTITION by Anumyr ORDER by BYCGQM) as Tnum from (select B.*,a.*,cast (anum as varchar) + Ayr as an Umyr from (select ID as arid,a.yr as ayr,a.ycgqm as AYCGQM, Row_number () over (PARTITION by yr ORDER by yr)               As Anum from Tb1 a) A, (select ID as Brid, b.yr as byr,b.ycgqm as BYCGQM from TB2 b b where Ayr = Byr) t) (where anum=tnum) Twhere arid=tb1.id)

2, Oracle's implementation:

Create table tb1 (yr varchar, ycgqm varchar) CREATE TABLE TB2 (yr varchar (), YCGQM varchar ()) SELECT * FROM Tb1insert into TB1 (YR,YCGQM) VALUES (' a ', null), insert into TB1 (YR,YCGQM) VALUES (' B ', null); INSERT into TB1 (YR,YCGQM) VALUES (' E ', null), insert into TB1 (YR,YCGQM) VALUES (' a ', null), insert into TB1 (YR,YCGQM) VALUES (' s ', null); INSERT INTO TB1 (YR,YCGQM) VALUES (' d ', null), insert into TB1 (YR,YCGQM) VALUES (' E ', null), insert into TB1 (YR,YCGQM) VALUES (' a ', null); Insert into TB2 (YR,YCGQM) VALUES (' A ', ' AA '), insert into TB2 (YR,YCGQM) VALUES (' A ', ' ab '), insert into TB2 (YR,YCGQM) VALUES ( ' B ', ' BB '), insert into TB2 (YR,YCGQM) VALUES (' B ', ' BA '), insert into TB2 (YR,YCGQM) VALUES (' B ', ' BBB '); insert into TB2 (yr, YCGQM) VALUES (' d ', ' DD '), insert into TB2 (YR,YCGQM) VALUES (' d ', ' D2 '), insert into TB2 (YR,YCGQM) VALUES (' F ', ' ddd '); insert      Into TB2 (YR,YCGQM) VALUES (' C ', ' cc '), insert into TB2 (YR,YCGQM) VALUES (' A ', ' AC '), Update Tb1set ycgqm= (select Bycgqm from ( SELECT * FROM (select RowNum r,t.*, (SelecT Count (*) from TB1 where Tb1.yr=t.ayr) as CNT, Row_number () over (PARTITION by Anumyr ORDER by BYCGQM) as Tnum from (Select B.*,a.*,anum | | Ayr as Anumyr from (select rowID as Arid,rownum | |       ' A ' as ra,a.yr as ayr,a.ycgqm as AYCGQM, Row_number () over (PARTITION by yr ORDER by yr) as Anum from Tb1 a ) A, (select rowID as Brid, rownum | | ' B ' as RB, b.yr as byr,b.ycgqm as bycgqm from TB2 b) b where Ayr = Byr ORDER by Ayr) T ORDER by Byr,anum,tnu m) where Anum=tnum) where Arid=tb1.rowid)

Second, the realization of ideas

The whole idea is that many singers in tb1 need to fill in the songs of the singers in the TB2 randomly, and not repeat them. For this, the first thought was random, Rand, but there was no guarantee of not repeating. So I thought of the method

1, the TB1 in accordance with the singer group, each singer has a number of records, according to the number of records in the singer, that is, the first singer if there are 2 records, then 1, 2, the second singer has 3 records, then three-in-one, which corresponds to the first layer of SQL

2, to TB1 and TB2 do Cartesian product, form matrix table (efficiency is worth considering, if the data is large, that must be discarded), according to the results, by the singer Group, according to the song in the singer sequence number.

3, take the singer sequence and the song order of the same record. This is because if there are 1 singers, there are 3 records in the TB1, then the number is three-way, according to the song number, each record corresponds to 3 songs, that is, the Cartesian product generates 3 records, this record number is a three-way order, and each record number collation is the same. So the first record takes the first song, the second record takes the second song, and so on, so long as the number of songs is certainly not repeated

4, according to the primary key or ROWID final positioning no TB1 record location, easy to update

Random relationship of SQL Server and Oracle