如何在 MySQL 数据库中创建一个简单的粘性位置文章列表，只需一次查询即可检索？

Question

我有一个分页的文章列表，需要按排名排序，但是当文章的位置 <> 0 则必须将其插入到该特定位置。 我想我至少可以为当前页面提取正确位置的文章，然后在 PHP 中对它们进行排序，然后显示在正确的位置。 我想在 MySQL 中仅使用一个查询来执行此操作

我的一些尝试是：

此查询尝试首先选择当前页面位置正确的文章，然后选择排名最高的文章

SELECT id, rank_score, position
FROM Articles
ORDER BY ((position <= (50 * 1)) AND (position > 50 * (1-1))) DESC, rank_score DESC
LIMIT 0, 50

50 是页面上显示的文章数1 是当前页码，它们在查询生成时添加。

此查询存在的问题是，第 2 页上的结果是错误的，因为通过添加 LIMIT 50,50，您可以超出该页面上具有位置的文章。

另一种尝试：

SELECT (
    SELECT id, rank_score, position
      FROM Articles
  ORDER BY ((position <= (50 * 1)) AND (position > 50 * (1-1))) DESC, rank_score DESC  
     LIMIT 50)
UNION (
  SELECT id, rank_score, position
    FROM Articles
 ORDER BY rank_score DESC LIMIT x)

为了正确工作，第二个查询的限制必须等于第一个查询返回的行数。 另外，任何时候一篇文章的排名非常高，而且位置也非常高，它会更早地显示，因为在第二个查询中位置被忽略。

Answer 1

您的第一个 ORDER BY 表达式不够复杂。尝试：

ORDER BY ((position < (50 * (1 + (1-1 * (50 / 1)))) 
    OR position = (50 + (50 - (50 * POW(50, 0)) * 1))) 
  AND (position > 50 * (1-1) / 1 * 50)) DESC

或者，只需使用：

ORDER BY (position <= 50 AND position > 0) DESC

我建议也复杂化（或者，如果必须的话，简化）其余的表达式。我预测你要么会找到你的问题，要么打开一个通向另一个宇宙的洞，在那里神奇的独角兽会解决你的问题。

Answer 2

是的，神奇的独角兽是对的，那个查询是错误的
这是真正神奇的查询

SELECT * FROM (
SELECT @rownum:=@rownum+1 rownum, IF(position=0,@rownum,position) as loc, a.id, a.rank_score, a.position
FROM (SELECT @rownum:=0) r, Articles a
ORDER BY rank_score DESC) t 
ORDER BY loc DESC
LIMIT 0, 50

Answer 3

我找到了问题的答案。
我放弃了 sql + php 排序解决方案，找到了一个完整的 sql 解决方案，在其中我可以根据可用的排名和位置对所有文章进行排序。

SELECT @rownum:=@rownum+1 rownum, IF(position=0,@rownum,position) as loc, a.id, a.rank_score, a.position
FROM (SELECT @rownum:=0) r, Articles a
ORDER BY  loc, rank_score DESC
LIMIT 0, 50

Answer 4

神奇独角兽在最后一个查询中可以看到的一个小观察 - 您正在基于另一个字段 (@rownum) 的字段 (loc) 上对结果集进行排序，您希望该字段具有基于排序集的值。神秘？循环依赖？或者只是奇怪？ ;-)