求一sql语句,查询比例相关
问sql语句
我想从某表里面每次随机查询指定个数的记录(select top(xxx)),并且希望查询到的结果某列的值有一定的比例.
比如下面的demo,随机从#t表内查询10条记录,,每次大约男生占60%,不要求绝对是这个值,但总体是这个比例..
这个肿么弄....在线等
create table #t( id int IDENTITY(1,1) NOT NULL,xm nvarchar(10),xb char(2));
go
declare @i int
set @i=1;
while(@i<=50)
begin
insert into #t(xm,xb) values('张' + CONVERT(nvarchar(8),@i),'男');
set @i=@i+1
end
set @i=1;
while(@i<=50)
begin
insert into #t(xm,xb) values('王' + CONVERT(nvarchar(8),@i),'女');
set @i=@i+1
end
go
select * from #t;
------最佳解决方案--------------------declare @i int=10
select * from (select top(cast(ceiling(@i*0.6) AS int)) * from #t where xb='男' order by NEWID()) as a
union all
select * from (select top(cast(floor(@i*0.4) AS int)) * from #t where xb='女' order by NEWID()) as a
或者 直接:
select * from (select top 6 * from #t where xb='男' order by NEWID()) as a
union all
select * from (select top 4 * from #t where xb='女' order by NEWID()) as a
------其他解决方案--------------------内牛满面....
解决问题了,,没想这么简单,却卡住我一个多小,愁了两个小时...