sql随机取n条数据
发布网友
发布时间:2022-04-07 17:42
我来回答
共4个回答
热心网友
时间:2022-04-07 19:12
oracle 里面能实现,sqlserver里面不知道有没有分析函数
给你个参考
select *
from
(
select id,name,tid,rank()over(partition by tid order by sys_guid())rnd
from table
) t
where rnd <= 5
其中
rank()over(partition by tid order by sys_guid())
的作用是求按tid分组然后按照sys_guid(相当于sql中的newid)排序后这一行所处的名次
热心网友
时间:2022-04-07 20:30
select * from (select * from tablename where tid=1 order by dbms_random.value) where rownum<6
union
select * from (select * from tablename where tid=2 order by dbms_random.value) where rownum<6
union
select * from (select * from tablename where tid=3 order by dbms_random.value) where rownum<6
热心网友
时间:2022-04-07 22:04
可以使用union试试
测试语句如下
select top 5 id,name,tid from tablename where tid=1 order by newid()
union
select top 5 id,name,tid from tablename where tid=2 order by newid()
union
select top 5 id,name,tid from tablename where tid=3 order by newid()
参考资料:http://www.ffasp.com/morenews.asp?lm=lm2&lmid=87
热心网友
时间:2022-04-07 23:56
程序和数据库最优化效率考虑:
程序部分:随机5个大于1小于表记录数的数字a,b,c,d,e 。
select * from table where 记录行 in (a,b,c,d,e)