MySQL Round Robin选择 [英] MySQL Round Robin Select

问题描述

我有一个网址表.我需要在循环中选择1.希望每个访问量或每个访问者显示1个，如果我有100个访问量或访问者，则显示20次.

I have a table of urls. I need to select 1, in round robin. Hoping to display 1 per pageview OR per Visitor and show each 20 times if I have 100 pageviews OR Visitors.

本质上:SELECT url FROM table LIMIT 1(旋转)

+-------+---------------------+
| id    | url                 |
+-------+---------------------+
|     1 | http://google.com   |
|     2 | http://yahoo.com    |
|     3 | http://ebay.com     |
|     4 | http://anything.com |
|     5 | http://other.com    |
+-------+---------------------+

推荐答案

如果我正确理解循环机制的意思，那么您可以按照以下方式做些事情

If I understand correctly what you mean by round-robin then you can do something along the lines of

SELECT id, url
  FROM urls u CROSS JOIN
(
  SELECT MIN(id) min_id, MAX(id) max_id
    FROM urls
) m 
 WHERE id > IF(? >= max_id, 0, ?) -- last shown id goes here instead of placeholders
 ORDER BY id
 LIMIT 1;

存储(在会话，文件，另一个表等中)，并将最后显示的id或0传递给查询，以进行初始查询.

Store (in session, file, another table, etc.) and pass to your query the last shown id or 0 for the initial query.

这将为您提供下一行，如果到达最后一行，则将再次提供第一行. 如果您在id s之间有间隔，此查询仍然可以使用.

This will give you the next row or first again if you reached the last one. This query will still work if you have gaps in ids.

这是一个 SQLFiddle 演示

Here is a SQLFiddle demo

这篇关于MySQL Round Robin选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！