單詞隨機連接三個片語

  閑來無事刷技術論壇，看到一個這樣的問題：

我現在有個表，裡面有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

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

本文只代表本人的見解，可能存在錯誤，僅用於技術交流。如果你喜歡該文，可以掃下麵的二維碼打賞我（打賞敬請備註“博客園打賞”五字）。