单词随机连接三个词组

  闲来无事刷技术论坛，看到一个这样的问题：

我现在有个表，里面有100个不同的单词，每个单词对应有大概20个词组，我想通过sql，每个单词随机获取对应的3个词组，请问怎么写可以实现？

  感觉题材很新颖，角度很刁钻，从业至今还未碰到过类似需求，今日反正也是闲着，索性解答一番。首先当然是生成测试数据。

--单词表
create table [dbo].[dancibiao](
    [bh] [int] identity(1,1) not null  primary key,
    [danci] [nvarchar](100) not null
) 
--词组表
create table [dbo].[cizubiao](
    [bh] [int] identity(1,1) not null primary key,
    [dancibh] [int] not null,
    [cizu] [nvarchar](100) not null
) 

--插入测试数据。
--为了方便生成数据，没有真的使用单词和词组，而是选择了替代方案。
declare @bl nvarchar(100)
declare @xl int
declare @cizu nvarchar(100)
declare @xlcz int

set @bl='a'
set @xl=0

while @xl<100
    begin
        set @xl=@xl+1        
        set @bl=@bl+cast (@xl as nvarchar(100))
        set @xlcz=0
        set @cizu=@bl

        insert into dancibiao(danci) values(@bl)

        while @xlcz<20
            begin
                set @xlcz=@xlcz+1
                set @cizu=@cizu+'_'+ cast(@xlcz as nvarchar(100))

                insert into cizubiao(dancibh,cizu) values(@xl,@cizu)

                set @cizu=@bl
            end


        set @bl='a'
    end

--第一种解决方案：sql批处理
create table #linshibiao(
     suijishu int not null
)
insert into #linshibiao (suijishu) select cast(rand()*20+1 as int)
insert into #linshibiao (suijishu) select cast(rand()*20+1 as int)
insert into #linshibiao (suijishu) select cast(rand()*20+1 as int)

select a.danci,a.cizu  
from  (select row_number() over(partition by a.danci order by b.bh) as bh, a.danci,b.cizu  
       from dancibiao a
       inner join cizubiao b on a.bh=b.dancibh ) a
inner join #linshibiao b on a.bh=b.suijishu

drop table #linshibiao

--第二种解决方案
select a.danci,a.cizu  
from  (select row_number() over(partition by a.danci order by b.bh) as bh, a.danci,b.cizu  
       from dancibiao a
       inner join cizubiao b on a.bh=b.dancibh) a
inner join (select cast(rand()*20+1 as int) as bh,cast(rand()*20+1 as int) as bh2,cast(rand()*20+1 as int) as bh3) b on a.bh=b.bh2 or a.bh=b.bh3 or a.bh=b.bh

结果如图：

 

写作时间：2018-11-20

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

本文只代表本人的见解，可能存在错误，仅用于技术交流。如果你喜欢该文，可以扫下面的二维码打赏我（打赏敬请备注“博客园打赏”五字）。