34,587
社区成员
发帖
与我相关
我的任务
分享
if object_id('tempdb..#tb') is not null
drop table #tb
go
create table #tb
(
userid int,--会员ID
wt int --权重,值为1-5之间
)
go
insert into #tb
select 1,2 union all
select 2,1 union all
select 3,1 union all
select 4,3 union all
select 5,4 union all
select 6,1 union all
select 7,1 union all
select 8,4 union all
select 9,5 union all
select 10,3
go
--请教各位如何实现:有10条数据查询出其中5条,权重越大,被查出的几率越大
SELECT sv.number,CONVERT(INT,(1+SQRT(1+8*sv.number))/2) FROM MASTER..spt_values AS sv WHERE sv.type='p'
/*得到下面的结果:
number就是生成的随机种子,后面的计算值就对应权重值。可以看到权重越大,那么涵盖的数据范围就越大,
number (No column name)
0 1
1 2
2 2
3 3
4 3
5 3
6 4
7 4
8 4
9 4
10 5
11 5
12 5
13 5
14 5
15 6
16 6
17 6
18 6
19 6
20 6
21 7
22 7
23 7
24 7
25 7
26 7
27 7
*/
结合你的需求,但有个问题要注意,你的数据权重值有重复且不一定连续,因此可能出现拿到不到指定书目的数据:
declare @maxnum INT
select @maxnum=(SELECT max(wt) FROM #tb)
SELECT TOP 5 * FROM #tb AS t
WHERE wt in (
SELECT CONVERT(INT,(1+SQRT(1+8*(abs(checksum(newid()))%(5*(5-1)/2+1))))/2) AS wt
from MASTER.dbo.spt_values
)
declare @num int
select @num=abs(checksum(newid()))%150
select case when @num between 0 and 9 then 1
when @num between 10 and 29 then 2
when @num between 30 and 59 then 3
when @num between 60 and 99 then 4
when @num between 100 and 149 then 5
end
如果对这种方法有兴趣,可以继续研究下
declare @num int
select @num=abs(checksum(newid()))%99+1
select case when @num between 1 and 10 then 1 --10个数 1-100中占10%
when @num between 11 and 30 then 2 --20个数 20%
when @num between 31 and 60 then 3 --30个数 30%
when @num between 61 and 90 then 4 --40个数 40%
when @num between 91 and 100 then 5 --50个数 50%
end
--问题无须复杂化,二楼的就行了
select top 5 * from #tb order by wt desc
-- 可以考虑使用一下 checksum 和 newid
select top 5 * from #tb order by wt * abs(checksum(newid()) /100.0) desc
go
drop table #tb
go
if object_id('tempdb..#tb') is not null
drop table #tb
GO
if object_id('tempdb..#tb1') is not null
drop table #tb1
go
create table #tb
(
userid int,--会员ID
wt int --权重,值为1-5之间
)
go
insert into #tb
select 1,2 union all
select 2,1 union all
select 3,1 union all
select 4,3 union all
select 5,4 union all
select 6,1 union all
select 7,1 union all
select 8,4 union all
select 9,5 union all
select 10,3
go
--请教各位如何实现:有10条数据查询出其中5条,权重越大,被查出的几率越大
DECLARE @Sum INT ;
DECLARE @Rows INT,@Row INT;
SELECT @Rows = 5,
@Row = 1
SELECT @Sum=SUM(wt)
FROM #tb
SELECT a.*,b.wt*1.0/@Sum AS gl --概率
INTO #tb1
FROM #tb a
CROSS APPLY (SELECT SUM(wt) AS wt FROM #tb WHERE userid <= a.userid) b
WHILE @Row <= @Rows
BEGIN
SELECT TOP 1 userid FROM #tb1 WHERE gl > RAND()
SELECT @Row = @Row + 1
END
select top 5 * from #tb order by wt desc