日期:2014-05-17  浏览次数:20440 次

基础:分组随机取数?
有一个表t1(id int,value float),id自增。
现在按value升序,若value相同按ID降序。
现在需要抽取10条记录:value值前10%数据中的5组记录(随机)和value值在45%~55%之间的5组记录(随机)。
谢谢哈!

--测试数据--
if OBJECT_ID('test1')is not null
drop table test1
go
create table test1(id int identity(1,1),value float)
go
declare @i int
set @i=0
while @i<10000
begin
insert into test1 values(abs(CHECKSUM(NEWID())%100000))
set @i=@i+1
end
select * from test1 order by value
drop table test1



------解决方案--------------------
top ..percent order by newid()
------解决方案--------------------
SQL code

10%随机5条
select top 5 * from (select top 10 percent * from test1 order by value desc)t order by newid()
45-55随机5条
select top 5 * from (
select top 55 percent * from test1 where id not in(select top 45 id from test1 order by value desc) order by value desc
)t order by newid()

------解决方案--------------------
SQL code

;with t1
as
(
    select top 5 id,value from
    (
        select top ((select count(*) from test1)/10) * from test1 order by value
    ) t 
    order by newid()
)
,t2 as
(
    select top 5 id,value from
    (
        select top ((select count(*) from test1)/10*(55-45)/10) * 
        from 
        (
            select top ((select count(*) from test1)/10*55/10) * 
            from test1 order by value
        ) t 
        order by value desc
    ) t
    order by newid()
)
select * from t1
union all
select * from t2
/*
id          value
----------- ----------------------
9438        8026
5226        5290
8750        1825
4030        6919
1941        869
6263        47308
2370        48326
9811        53297
3334        53988
295         50459
*/