日期:2014-05-17 浏览次数:20586 次
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()
------解决方案--------------------
;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
*/