In the technical forum, I saw a question like this:

Now I have a table. There are 100 different words in it. Each word corresponds to about 20 phrases. I want to get 3 phrases randomly for each word through sql. How can I write it?

I feel that the subject matter is very novel and the angle is very tricky. I haven't met similar needs in the industry so far. I'm still idle today, so I just need to answer. The first, of course, is to generate test data.

--Word list
CREATE TABLE [dbo].[DanCiBiao](
    [BH] [int] IDENTITY(1,1) NOT NULL  primary key,
    [DanCi] [nvarchar](100) NOT NULL
) 
--Word list
CREATE TABLE [dbo].[CiZuBiao](
    [BH] [int] IDENTITY(1,1) NOT NULL primary key,
    [DanCiBH] [int] NOT NULL,
    [CiZu] [nvarchar](100) NOT NULL
) 

--Insert test data.
--In order to facilitate the generation of data, instead of using words and phrases, we chose an alternative.
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

--The first solution: SQL Batch processing
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

--Second solution
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

The results are as follows:

Writing time: November 20, 2018

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

This article only represents my opinion, may be wrong, and is only used for technical exchange. If you like this article, you can scan the QR code below to give me a reward (please note the five words "blog Garden reward").