The random relationship between SQLServer and Oracle corresponds to sqlserveroracle

The random relationship between SQLServer and Oracle corresponds to sqlserveroracle

The requirements are as follows:

Now we need to complete the singing fields in tb1. The condition is to search for songs sung by the same artist in tb2 and randomly fill in the Song Name field in tb1.
A singer sings more than one song, so tb2 is a collection of artist singing all the songs. The same singer in tb1 may appear several times.
In addition, you need to search for the same artist's song name in tb2, that is, the artist's song summary table, based on the tb1 artist name.
If the artist's name is the same, the singer's song name in tb2 should be randomly selected to fill in the tb1 field.

Tb1
Artist song name
A null
B null
C null
A null
S null
D null
E null

Tb2

Artist song name
A aa
A AB
B bb
B ba
B bbb
D dd
D d2
F ddd
C cc

Artist song name
A aa (the artist name in tb1 appears several times each time in tb2, as long as a random entry is filled)
A AB

B bb
D dd
C cc

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

I. Final SQL result

1. sqlserver implementation:

Create table tb1 (id varchar (60), -- the primary key yr varchar (20), ycgqm varchar (50) create table tb2 (id varchar (60 ), -- The table's primary key (which can not be) yr varchar (20), ycgqm varchar (50) 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', 'A '); 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, ycgqm) values (newid (), 'F', 'ddd '); insert into tb2 (id, yr, ycgqm) values (newid (), 'C', 'cc'); insert into tb2 (id, yr, ycgqm) values (newid (), 'A', 'ac '); update tb1set ycgqm = (select bycgqm from (select * from (select t. *, ROW_NUMBER () OVER (partition by anumyr order by bycgqm) AS tnum from (select B. *,. *, cast (anum as varchar (20) + ayr as anumyr from (select id as arid,. yr as ayr,. 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 implementation:

create table tb1(  yr varchar(20),  ycgqm varchar(50))create table tb2(  yr varchar(20),  ycgqm varchar(50))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,tnum      ) where anum=tnum   )  WHERE  arid=tb1.rowid)

II. Implementation ideas

The key to the whole idea is that multiple singers in tb1 need to randomly fill in the songs corresponding to the singers in tb2 without repeating them. For this, the first thought is random, rand, but this cannot be guaranteed not to be repeated. Thought of a Method

1. For tb1, each singer has multiple records according to the singer group, and the number is recorded in the singer order. That is, if the first singer has two records, the number is 1, 2, the second singer has three records, namely, 1, 2, 3, which corresponds to the first SQL

2. Perform Cartesian Product on tb1 and tb2 to form a matrix table (efficiency is worth considering. If the data volume is large, it must be discarded). Based on the results, group by artist, number the song in the singer's order.

3. Take records with the same sequence of singers and songs. This is because if one singer has three records in tb1, the number is 1, 2, and 3. After the number of songs is specified, each record corresponds to three songs, that is, after Cartesian Product, three records are generated, which are sorted by numbers 1, 2, and 3, and each record has the same sorting rules. So the first record takes the first song, the second record takes the second song, and so on, as long as the number of songs is large, it will certainly not repeat

4. Finally, no tb1 record is located based on the primary key or rowid to facilitate update.

3. I finally added a comment. Because the SQL statement is too long to be pasted in the comment, This is a random acquisition of the songs under the singer.

Update tb1set ycgqm = (select bycgqm from (select t3.id, (-- select ycgqm from (select B. *, ROW_NUMBER () OVER (partition by yr order by ycgqm) AS tnum from tb2 B) b2 where b2.yr = t3.yr and b2.tnum = t3.rndnum) as bycgqm from (-- select t2. *, cast (ceiling (rand (checksum (newid () * gqtotalnum) as int Based on the line number of the Song under the singer name in tb1) as rndnum from (select t. *, (select count (*) from tb2 where yr = t. yr) as gqtotalnum from tb1 t) t2) t3) t4 where t4.id = tb1.id)