单词随机连接三个词组

来源：cnblogs　　作者：刀奈特程序员　　时间：2018/11/20 17:38:15　　对本文有异议

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

我现在有个表，里面有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