从数据库中选择前10行并随机显示结果

I want to display trending Ads on my site on the basis of number of clicks.

我想根据点击次数在我的网站上展示热门广告。

My SQL Query looks like this:

我的SQL查询如下所示：

  SELECT  ad_id, clicks from ads ORDER BY  clicks DESC LIMIT 5

Result:
ad_id   clicks
3393    2204
4495    1208
2399    932
2780    777
3316    679

I want to display this result randomly every time page refresh.

我希望每次页面刷新时随机显示此结果。

I tried using

我试过用

SELECT  * from ads ORDER BY  clicks DESC, RAND() LIMIT 10

But this is not working giving the same result every time on page refresh.

但这并不是每次在页面刷新时都给出相同的结果。

If I use like:

如果我使用像：

SELECT  ad_id, clicks from ads ORDER BY RAND(), clicks DESC LIMIT 10
ad_id   clicks
9762    0
6305    1
4040    17
11598   0
11347   0

It is showing data randomly but now the highest number of clicks is zero.

它随机显示数据，但现在点击次数最多为零。

Can you suggest me how to display top clicks result randomly on every page refresh.

您能否建议我如何在每次刷新页面时随机显示最高点击结果。

1 个解决方案

#1

If you want to get top 10 rows and then display them in random order, use this:

如果要获得前10行，然后以随机顺序显示它们，请使用：

select *
from (
    select *
    from ads
    order by clicks desc LIMIT 10
    ) t
order by rand() desc

If you want to get 10 randomly chosen records sorted in descending order of clicks, try this:

如果您想要按照点击次序的降序排序10个随机选择的记录，请尝试以下操作：

select *
from (
    select *
    from ads
    order by RAND() LIMIT 10
    ) t
order by clicks desc

It finds 10 random records in subquery and then sorts it afterwards.

它在子查询中找到10个随机记录，然后对其进行排序。

Or perhaps you want to get 10 random records out of some top , say 100, rows sorted in descending order of clicks:

或者你可能希望得到10个随机记录，这些记录来自一些顶部，比方说100，按照点击的降序排序：

select *
from (
    select *
    from (
        select *
        from ads
        order by click desc LIMIT 100  -- change this as per your needs
        ) t
    order by rand() limit 10
    ) t
order by clicks desc

#1