sqlserver 两个表关联1：n求随机取一条数据的sql语句实现！

问题描述：

现在要补齐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和上次SQL了，如果还有新规则，不行就两者自己综合一下。再写就没什么意义了。

 update tb1
set 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
)

答

谢谢各位热心大侠，这个sql困扰我两天了，还是没有搞定，所以才求助大家的，希望那个大侠会的话，能给我指点下迷津。万分感谢!

答

你这举得例子让人看不懂。你是要随机tb1中数据还是tb2中数据还是两个表中数据都要随机
初步思路，可以生成行编号，通过rand随机函数获取。

答

select top 1 t1.*, t2.* from table1 t1 join table2 t2 on t1.name = t2.name order by newid()

答

你到底是取数据还是更新数据？

答

先贴答案吧，sql应该是还能优化，自己再做优化吧。等周末有时间了把这SQL整理成博客中。

 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 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
)

答

上次那个是oracle的，忘记是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
)

select * from tb1

答

tb1 是dr_bob
tb2 是songlist

tb1 表结构是
id isrc [track title]

tb2 表结构
isrc artist

语句：
update dr_bob
set isrc =
(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.[track title] as ayr,a.isrc as aycgqm
,ROW_NUMBER() OVER(PARTITION BY [track title] ORDER BY [track title]) AS anum from dr_bob a
) a,( select id as brid, b.artist as byr,b.isrc as bycgqm from songlist b
) b where ayr = byr
) t
) t where anum=tnum

) t
WHERE arid=dr_bob.id
) where dr_bob.isrc=''

为什么我的这个出来的结果就不是我想要的啊

sqlserver 两个表关联1：n求随机取一条数据的sql语句实现！

相关推荐