根据分类,每类随机抽取N个（N对每个分类都不一样）
根据分类,每类随机抽取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
[解决办法]
d
[解决办法]
select * from # where songcategoryid=1 and songcategoryorder=(ABS(CHECKSUM(NEWID())) % songcategorytotal)
改为
select * from # where songcategoryid=1 and songcategoryorder=(ABS(CHECKSUM(NEWID())) % (songcategorytotal+1))
如何
[解决办法]
try:\\
--音乐分类表
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
select id,musicid,songcategoryid
,(select count(*) from dbo.music_category where songcategoryid=a.songcategoryid and id <a.id) as songcategoryorder
,(select count(*) from dbo.music_category where songcategoryid=a.songcategoryid) as songcategorytotal
into #
from dbo.music_category a order by songcategoryid,musicid
print 'temp '
select * from #
declare @i int
select @i=0
declare @temp int
set @temp = 0
while @i <10
begin
--这儿就没有按理说了
set @temp = ABS(CHECKSUM(NEWID()))
select * from # where songcategoryid=1 and songcategoryorder=(@temp % songcategorytotal)
select @i=@i+1
end
drop table #
drop table plans,music_category
[解决办法]
--音乐分类表
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
select id,musicid,songcategoryid
,(select count(*) from dbo.music_category where songcategoryid=a.songcategoryid and id <a.id) as songcategoryorder
,(select count(*) from dbo.music_category where songcategoryid=a.songcategoryid) as songcategorytotal
into #
from dbo.music_category a order by songcategoryid,musicid
print 'temp '
select * from #
declare @i int
select @i=0
declare @temp int
set @temp = 0
while @i <10
begin
--这儿就没有按理说了
set @temp = ABS(CHECKSUM(NEWID()))
select * from # where songcategoryid=1 and songcategoryorder=(@temp % songcategorytotal)
select @i=@i+1
end
drop table #
drop table plans,music_category
这个试过行啊！！！
[解决办法]
实现功能并不难，问题是效率可能不怎么样
--音乐分类表
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 *,cast(null as int) as musicid
into #
from plans
--循环更改
while exists (select 1 from # where musicid is null)
begin
set rowcount 1
update t
set musicid=(
select top 1 musicid
from music_category a
where songcategoryid=t.songcategoryid
order by (
select count(*) from # where musicid=a.musicid), --这个是控制重复率的
newid() --这个是控制随机的
)
from # t
where musicid is null
set rowcount 0
end
select * from #
order by songcategoryid,musicid
drop table #
drop table plans,music_category
--重复率的控制是以牺牲效率为代价的
[解决办法]
select top 1 * from music_category a where exists ( select 1 from plans where songcategoryid=a.songcategoryid )
order by newid()
[解决办法]
楼主可以参考一下方法，该方法有时候会重复，有时间我再优化优化（借用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
/*
结果：
idsongcategoryidmusicid
111
215
3311
412
5516
6619
7413
828
913
10414
1127
12210
13720
14414
15720
1629
17515
18517
*/
[解决办法]
随机出现重复的可能性小，当然还可以优化一下。
有时间再看怎么样优化，把随机性控制到最小。