SQLServer和Oracle的隨機關係對應，sqlserveroracle

SQLServer和Oracle的隨機關係對應，sqlserveroracle

有需求如下：

現在要補齊tb1中演唱歌曲欄位。條件是去tb2中尋找相同藝人演唱過的歌曲，隨機填充到tb1中的歌曲名欄位
一個歌手不止演唱一首歌，所以tb2中是藝人演唱所有歌曲的集合。tb1中同一個歌手可能出現好幾次
補齊時候需根據tb1中藝人名稱去tb2也就是藝人歌曲匯總表中尋找相同藝人演唱的歌曲名稱。
需要在藝人名相同情況下隨機取tb2中演唱歌曲名去一一補齊tb1中的欄位 tb1

tb1
藝人 演唱歌曲名
a null
b null
c null
a null
s null
d null
e null

tb2

藝人 演唱歌曲名 
a aa
a ab
b bb
b ba
b bbb
d dd
d d2
f ddd
c cc

藝人 演唱歌曲名稱
a aa （tb1中的藝人名會出現好幾次每次在tb2中，只要隨機的一條來填充）
a ab

b bb
d dd
c cc

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

一、最終SQL結果

1、sqlserver的實現：

create table tb1(  id varchar(60),--需要表的主鍵  yr varchar(20),  ycgqm varchar(50))create table tb2(  id varchar(60),--表的主鍵(可以沒有)  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','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,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.*,a.*,cast(anum as varchar(20))+ ayr as anumyr 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       ) t where anum=tnum   ) tWHERE  arid=tb1.id)

2、oracle的實現：

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)

二、實現思路

整個思路關鍵在於tb1中的多個歌手需要隨機填寫tb2中的歌手對應的歌曲，而且不重複。對於這點，第一想到隨機,rand，但這沒法保證不重複。於是想到方法

1、對tb1中根據歌手分組，每個歌手有多條記錄，則按歌手內記錄順序編號，也就是第一個歌手如果有2條記錄，則為1,2，第二個歌手有3條記錄，則為1,2,3，這也就是對應第一層SQL

2、對tb1和tb2做笛卡爾積，形成矩陣表（效率是值得斟酌的，如果資料量大，那必須拋棄了），根據結果，按歌手分組，按歌曲在歌手內順序編號。

3、取歌手順序和歌曲順序相等的記錄。這是因為如果有1個歌手，在tb1中有3條記錄，那麼編號是1,2,3，按歌曲編號後，每條記錄對應3個歌曲，也就是笛卡爾積後產生3條記錄，這記錄編號也是1,2,3排序，而且每條記錄的編號定序是一樣的。所以第一條記錄取第一首歌曲，第二條記錄取第二首歌曲，依次類推，只要歌曲數多肯定不會重複

4、根據主鍵或者rowid最終定位沒條tb1的記錄位置，便於update

三、最後補充一種評論中的思路，因為SQL太長，評論中不讓貼，這個是隨機擷取歌手下的歌曲

update tb1set ycgqm=(select bycgqm from (    select t3.id,    (--根據tb1中產生的隨機數取歌曲     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 (--根據tb1中取歌手名稱下哪首歌的行號                select t2.*,cast(ceiling(rand(checksum(newid()))*gqtotalnum) as int) 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)