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

原问题：

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

根据计划播放类别表，从音乐分类表中随机选出对应类的某一首歌，以生成播放队列(最好重复性少，或没有重复)

-- 实际情况音乐分类表约有9万条记录，计划播放类别表约有800条记录

-- 音乐分类表
create   table  music_category(id  int   identity ( 1 , 1 ),musicid  int ,songcategoryid  int )
-- 计划播放类别表
create   table  plans(id  int   identity ( 1 , 1 ),songcategoryid  int )

insert  music_category(musicid,songcategoryid)
select   1 , 1
union   all   select   2 , 1
union   all   select   3 , 1
union   all   select   4 , 1
union   all   select   5 , 1
union   all   select   6 , 1
union   all   select   7 , 2
union   all   select   8 , 2
union   all   select   9 , 2
union   all   select   10 , 2
union   all   select   11 , 3
union   all   select   12 , 3
union   all   select   13 , 4
union   all   select   14 , 4
union   all   select   15 , 5
union   all   select   16 , 5
union   all   select   17 , 5
union   all   select   18 , 5
union   all   select   19 , 6
union   all   select   20 , 7
union   all   select   21 , 8
union   all   select   22 , 8
union   all   select   23 , 8

select   *   from  music_category  order   by  songcategoryid,musicid

insert  plans(songcategoryid)
select   1
union   all   select   1
union   all   select   3
union   all   select   1
union   all   select   5
union   all   select   6
union   all   select   4
union   all   select   2
union   all   select   1
union   all   select   4
union   all   select   2
union   all   select   2
union   all   select   7
union   all   select   4
union   all   select   7
union   all   select   2
union   all   select   5
union   all   select   5

select   *   from  plans  order   by  id

-- drop table plans,music_category

自己写的一条解决方法：

楼主可以参考一下方法，该方法有时候会重复，有时间我再优化优化（借用Yang_(扬帆破浪) ( ) 大哥的建表数据）：

-- 音乐分类表
SET  NOCOUNT  ON
create   table  music_category(id  int   identity ( 1 , 1 ),musicid  int ,songcategoryid  int )
-- 计划播放类别表
create   table  plans(id  int   identity ( 1 , 1 ),songcategoryid  int )

insert  music_category(musicid,songcategoryid)
select   1 , 1
union   all   select   2 , 1
union   all   select   3 , 1
union   all   select   4 , 1
union   all   select   5 , 1
union   all   select   6 , 1
union   all   select   7 , 2
union   all   select   8 , 2
union   all   select   9 , 2
union   all   select   10 , 2
union   all   select   11 , 3
union   all   select   12 , 3
union   all   select   13 , 4
union   all   select   14 , 4
union   all   select   15 , 5
union   all   select   16 , 5
union   all   select   17 , 5
union   all   select   18 , 5
union   all   select   19 , 6
union   all   select   20 , 7
union   all   select   21 , 8
union   all   select   22 , 8
union   all   select   23 , 8
insert  plans(songcategoryid)
select   1
union   all   select   1
union   all   select   3
union   all   select   1
union   all   select   5
union   all   select   6
union   all   select   4
union   all   select   2
union   all   select   1
union   all   select   4
union   all   select   2
union   all   select   2
union   all   select   7
union   all   select   4
union   all   select   7
union   all   select   2
union   all   select   5
union   all   select   5

DECLARE   @EXECUTE_SQL   nvarchar ( 4000 )
-- 构造结果表
SELECT   * , CAST ( NULL    as   int ) musicid  INTO  #T  FROM  plans
SELECT   MIN ( [ ID ] )  AS   [ ID ]   INTO  #ID   FROM  #T  WHERE  musicid  IS   NULL   GROUP   BY  songcategoryid
WHILE   EXISTS ( SELECT   1   FROM  #ID)
     BEGIN     
         -- 分组统计，根据各组随机得到音乐UPDATE到结果表#T中
         UPDATE  A  SET  A.musicid = B.musicid
             FROM  #T  AS  A  INNER   JOIN  
            ( SELECT  songcategoryid, MIN (musicid)  AS  musicid  FROM  music_category  AS  A1 
                     WHERE  
                        A1.musicid  in ( SELECT   top    1   B1.musicid  FROM  music_category  AS  B1  WHERE                                    
                                 B1.songcategoryid = A1.songcategoryid
                                  AND   NOT   EXISTS ( SELECT   1   FROM  #T  AS  C1  WHERE  C1.musicid = B1.musicid)    
                                 ORDER   BY   NEWID ()  DESC )
                         AND   NOT   EXISTS ( SELECT   1   FROM  #T  AS  B1  WHERE  B1.musicid = A1.musicid)
                 GROUP   BY  songcategoryid
            )  AS  B  ON  B.songcategoryid = A.songcategoryid  AND  A.musicid  IS   NULL     
             WHERE  A.ID  IN ( SELECT   MIN ( [ ID ] )  FROM  #T   WHERE  musicid  IS   NULL   GROUP   BY  songcategoryid)        
         -- 这里主要是考虑分组后UPDATE后，可能还所有一些（必定存在重复的）音乐
         -- 这一段不用考虑唯一性，只要随机就ok。    
         -- 不要下面语句可能会死循环
         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 ))
             BEGIN
                 PRINT   1
                 UPDATE  A  SET  A.musicid = B.musicid
                     FROM  #T  AS  A  INNER   JOIN  
                        ( SELECT  songcategoryid, MIN (musicid)  AS  musicid  FROM  music_category  AS  A1 
                                 WHERE  A1.musicid  in ( SELECT   top    1   B1.musicid  FROM  music_category  AS  B1  WHERE                                    
                                     B1.songcategoryid = A1.songcategoryid
                                 ORDER   BY   NEWID ()  DESC )
                             GROUP   BY  songcategoryid
                    )  AS  B  ON  B.songcategoryid = A.songcategoryid  AND  A.musicid  IS   NULL     
                 WHERE  A.ID  IN ( SELECT   MIN ( [ ID ] )  FROM  #T   WHERE  musicid  IS   NULL   GROUP   BY  songcategoryid)        
             END
         TRUNCATE   TABLE  #ID
         INSERT   INTO  #ID   SELECT   MIN ( [ ID ] )  FROM  #T  WHERE  musicid  IS   NULL   GROUP   BY  songcategoryid
            
     END
SELECT   *   FROM  #T  -- ORDER BY songcategoryid
DROP   TABLE  #T,#ID
drop   table  plans,music_category


/**/ /*
结果：
id    songcategoryid    musicid
1    1    1
2    1    5
3    3    11
4    1    2
5    5    16
6    6    19
7    4    13
8    2    8
9    1    3
10    4    14
11    2    7
12    2    10
13    7    20
14    4    14
15    7    20
16    2    9
17    5    15
18    5    17

*/

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