SQLServer和Oracle的随机关系对应 - 数据库编程

有需求如下：

现在要补齐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 tb1
set 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   
) t
WHERE  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 tb1
insert 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 tb1
set 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 rowi

SQLServer和Oracle的随机关系对应(一)