根据分类,每类随机抽取N个（N对每个分类都不一样,SQL问题）

喜欢ヅ旅行 2022-04-01 10:29 450阅读 0赞

原问题：

![None.gif][] 根据分类,每类随机抽取N个（N对每个分类都不一样）  
![None.gif][]  
![None.gif][]根据计划播放类别表，从音乐分类表中随机选出对应类的某一首歌，以生成播放队列(最好重复性少，或没有重复)  
![None.gif][]  
![None.gif][] \-- 实际情况音乐分类表约有9万条记录，计划播放类别表约有800条记录   
![None.gif][]   
![None.gif][] \-- 音乐分类表   
![None.gif][] create  table  music\_category(id  **int**  identity ( **1** , **1** ),musicid  **int** ,songcategoryid  **int** )  
![None.gif][] \-- 计划播放类别表   
![None.gif][] create  table  plans(id  **int**  identity ( **1** , **1** ),songcategoryid  **int** )  
![None.gif][]  
![None.gif][] insert  music\_category(musicid,songcategoryid)  
![None.gif][] select  **1** , **1**   
![None.gif][] union  all  select  **2** , **1**   
![None.gif][] union  all  select  **3** , **1**   
![None.gif][] union  all  select  **4** , **1**   
![None.gif][] union  all  select  **5** , **1**   
![None.gif][] union  all  select  **6** , **1**   
![None.gif][] union  all  select  **7** , **2**   
![None.gif][] union  all  select  **8** , **2**   
![None.gif][] union  all  select  **9** , **2**   
![None.gif][] union  all  select  **10** , **2**   
![None.gif][] union  all  select  **11** , **3**   
![None.gif][] union  all  select  **12** , **3**   
![None.gif][] union  all  select  **13** , **4**   
![None.gif][] union  all  select  **14** , **4**   
![None.gif][] union  all  select  **15** , **5**   
![None.gif][] union  all  select  **16** , **5**   
![None.gif][] union  all  select  **17** , **5**   
![None.gif][] union  all  select  **18** , **5**   
![None.gif][] union  all  select  **19** , **6**   
![None.gif][] union  all  select  **20** , **7**   
![None.gif][] union  all  select  **21** , **8**   
![None.gif][] union  all  select  **22** , **8**   
![None.gif][] union  all  select  **23** , **8**   
![None.gif][]  
![None.gif][] select  \*  from  music\_category  order  by  songcategoryid,musicid  
![None.gif][]  
![None.gif][] insert  plans(songcategoryid)  
![None.gif][] select  **1**   
![None.gif][] union  all  select  **1**   
![None.gif][] union  all  select  **3**   
![None.gif][] union  all  select  **1**   
![None.gif][] union  all  select  **5**   
![None.gif][] union  all  select  **6**   
![None.gif][] union  all  select  **4**   
![None.gif][] union  all  select  **2**   
![None.gif][] union  all  select  **1**   
![None.gif][] union  all  select  **4**   
![None.gif][] union  all  select  **2**   
![None.gif][] union  all  select  **2**   
![None.gif][] union  all  select  **7**   
![None.gif][] union  all  select  **4**   
![None.gif][] union  all  select  **7**   
![None.gif][] union  all  select  **2**   
![None.gif][] union  all  select  **5**   
![None.gif][] union  all  select  **5**   
![None.gif][]  
![None.gif][] select  \*  from  plans  order  by  id  
![None.gif][]  
![None.gif][] \-- drop table plans,music\_category   
![None.gif][]

自己写的一条解决方法：

![None.gif][] 楼主可以参考一下方法，该方法有时候会重复，有时间我再优化优化（借用Yang\_(扬帆破浪) ( ) 大哥的建表数据）：  
![None.gif][]  
![None.gif][] \-- 音乐分类表   
![None.gif][] SET  NOCOUNT  ON   
![None.gif][] create  table  music\_category(id  **int**  identity ( **1** , **1** ),musicid  **int** ,songcategoryid  **int** )  
![None.gif][] \-- 计划播放类别表   
![None.gif][] create  table  plans(id  **int**  identity ( **1** , **1** ),songcategoryid  **int** )  
![None.gif][]  
![None.gif][] insert  music\_category(musicid,songcategoryid)  
![None.gif][] select  **1** , **1**   
![None.gif][] union  all  select  **2** , **1**   
![None.gif][] union  all  select  **3** , **1**   
![None.gif][] union  all  select  **4** , **1**   
![None.gif][] union  all  select  **5** , **1**   
![None.gif][] union  all  select  **6** , **1**   
![None.gif][] union  all  select  **7** , **2**   
![None.gif][] union  all  select  **8** , **2**   
![None.gif][] union  all  select  **9** , **2**   
![None.gif][] union  all  select  **10** , **2**   
![None.gif][] union  all  select  **11** , **3**   
![None.gif][] union  all  select  **12** , **3**   
![None.gif][] union  all  select  **13** , **4**   
![None.gif][] union  all  select  **14** , **4**   
![None.gif][] union  all  select  **15** , **5**   
![None.gif][] union  all  select  **16** , **5**   
![None.gif][] union  all  select  **17** , **5**   
![None.gif][] union  all  select  **18** , **5**   
![None.gif][] union  all  select  **19** , **6**   
![None.gif][] union  all  select  **20** , **7**   
![None.gif][] union  all  select  **21** , **8**   
![None.gif][] union  all  select  **22** , **8**   
![None.gif][] union  all  select  **23** , **8**   
![None.gif][] insert  plans(songcategoryid)  
![None.gif][] select  **1**   
![None.gif][] union  all  select  **1**   
![None.gif][] union  all  select  **3**   
![None.gif][] union  all  select  **1**   
![None.gif][] union  all  select  **5**   
![None.gif][] union  all  select  **6**   
![None.gif][] union  all  select  **4**   
![None.gif][] union  all  select  **2**   
![None.gif][] union  all  select  **1**   
![None.gif][] union  all  select  **4**   
![None.gif][] union  all  select  **2**   
![None.gif][] union  all  select  **2**   
![None.gif][] union  all  select  **7**   
![None.gif][] union  all  select  **4**   
![None.gif][] union  all  select  **7**   
![None.gif][] union  all  select  **2**   
![None.gif][] union  all  select  **5**   
![None.gif][] union  all  select  **5**   
![None.gif][]  
![None.gif][] DECLARE  @EXECUTE\_SQL  **nvarchar** ( **4000** )  
![None.gif][] \-- 构造结果表   
![None.gif][] SELECT  \* , CAST ( NULL  as  **int** ) musicid  INTO  \#T  FROM  plans  
![None.gif][] SELECT  MIN ( \[ ID \] )  AS  \[ ID \]  INTO  \#ID  FROM  \#T  WHERE  musicid  IS  NULL  GROUP  BY  songcategoryid  
![None.gif][] WHILE  EXISTS ( SELECT  **1**  FROM  \#ID)  
![None.gif][] BEGIN   
![None.gif][] \-- 分组统计，根据各组随机得到音乐UPDATE到结果表\#T中   
![None.gif][]  UPDATE  A  SET  A.musicid = B.musicid  
![None.gif][] FROM  \#T  AS  A  INNER  JOIN   
![None.gif][] ( SELECT  songcategoryid, MIN (musicid)  AS  musicid  FROM  music\_category  AS  A1  
![None.gif][] WHERE   
![None.gif][] A1.musicid  in ( SELECT  top  **1**  B1.musicid  FROM  music\_category  AS  B1  WHERE   
![None.gif][] B1.songcategoryid = A1.songcategoryid  
![None.gif][] AND  NOT  EXISTS ( SELECT  **1**  FROM  \#T  AS  C1  WHERE  C1.musicid = B1.musicid)  
![None.gif][] ORDER  BY  NEWID ()  DESC )  
![None.gif][] AND  NOT  EXISTS ( SELECT  **1**  FROM  \#T  AS  B1  WHERE  B1.musicid = A1.musicid)  
![None.gif][] GROUP  BY  songcategoryid  
![None.gif][] )  AS  B  ON  B.songcategoryid = A.songcategoryid  AND  A.musicid  IS  NULL   
![None.gif][] WHERE  A.ID  IN ( SELECT  MIN ( \[ ID \] )  FROM  \#T  WHERE  musicid  IS  NULL  GROUP  BY  songcategoryid)  
![None.gif][] \-- 这里主要是考虑分组后UPDATE后，可能还所有一些（必定存在重复的）音乐   
![None.gif][]  \-- 这一段不用考虑唯一性，只要随机就ok。    
![None.gif][]  \-- 不要下面语句可能会死循环   
![None.gif][]  IF  NOT  EXISTS ( SELECT  **1**  FROM  \#ID  AS  A  WHERE  NOT  EXISTS ( SELECT  **1**  FROM  \#T  AS  B  WHERE  B. \[ id \] = A. \[ ID \]  AND  B.musicid  IS  NULL ))  
![None.gif][] BEGIN   
![None.gif][] PRINT  **1**   
![None.gif][] UPDATE  A  SET  A.musicid = B.musicid  
![None.gif][] FROM  \#T  AS  A  INNER  JOIN   
![None.gif][] ( SELECT  songcategoryid, MIN (musicid)  AS  musicid  FROM  music\_category  AS  A1  
![None.gif][] WHERE  A1.musicid  in ( SELECT  top  **1**  B1.musicid  FROM  music\_category  AS  B1  WHERE   
![None.gif][] B1.songcategoryid = A1.songcategoryid  
![None.gif][] ORDER  BY  NEWID ()  DESC )  
![None.gif][] GROUP  BY  songcategoryid  
![None.gif][] )  AS  B  ON  B.songcategoryid = A.songcategoryid  AND  A.musicid  IS  NULL   
![None.gif][] WHERE  A.ID  IN ( SELECT  MIN ( \[ ID \] )  FROM  \#T  WHERE  musicid  IS  NULL  GROUP  BY  songcategoryid)  
![None.gif][] END   
![None.gif][] TRUNCATE  TABLE  \#ID  
![None.gif][] INSERT  INTO  \#ID  SELECT  MIN ( \[ ID \] )  FROM  \#T  WHERE  musicid  IS  NULL  GROUP  BY  songcategoryid  
![None.gif][]  
![None.gif][] END   
![None.gif][] SELECT  \*  FROM  \#T  \-- ORDER BY songcategoryid   
![None.gif][] DROP  TABLE  \#T,\#ID  
![None.gif][] drop  table  plans,music\_category  
![None.gif][]  
![None.gif][]  
![ExpandedBlockStart.gif][]![ContractedBlock.gif][] /\*\*/ /\*  
![InBlock.gif][]结果：  
![InBlock.gif][]id songcategoryid musicid  
![InBlock.gif][]1 1 1  
![InBlock.gif][]2 1 5  
![InBlock.gif][]3 3 11  
![InBlock.gif][]4 1 2  
![InBlock.gif][]5 5 16  
![InBlock.gif][]6 6 19  
![InBlock.gif][]7 4 13  
![InBlock.gif][]8 2 8  
![InBlock.gif][]9 1 3  
![InBlock.gif][]10 4 14  
![InBlock.gif][]11 2 7  
![InBlock.gif][]12 2 10  
![InBlock.gif][]13 7 20  
![InBlock.gif][]14 4 14  
![InBlock.gif][]15 7 20  
![InBlock.gif][]16 2 9  
![InBlock.gif][]17 5 15  
![InBlock.gif][]18 5 17  
![InBlock.gif][]  
![ExpandedBlockEnd.gif][]\*/   
![None.gif][]

随机出现重复的可能性小，当然还可以优化一下。  
有时间再看怎么样优化，把随机性控制到最小。

[None.gif]: /images/20220401/49bccd4db53b4aadb013372e1f20ad36.png
[ExpandedBlockStart.gif]: https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif
[ContractedBlock.gif]: /images/20220401/5612900497354a7e8fb14ead6c6741ef.png
[InBlock.gif]: /images/20220401/39ab396a82054d67902703981a9b2e82.png
[ExpandedBlockEnd.gif]: /images/20220401/31fd1f04559741ac87a51f24fa02c0b7.png