闲来无事刷技术论坛，看到一个这样的问题：
我现在有个表，里面有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 intdeclare @CiZu nvarchar(100)declare @XLCZ intset @BL='A'set @XL=0while @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 ) Ainner join #LinShiBiao B on A.BH=B.SuiJiShudrop 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) Ainner 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
=====================================================================================
本文只代表本人的见解，可能存在错误，仅用于技术交流。如果你喜欢该文，可以扫下面的二维码打赏我（打赏敬请备注“博客园打赏”五字）。
