MySQL ORDER BY RAND()

Question

我有一张表存储所用主题的使用次数，每个后续请求都会增加使用次数。我添加了一个查询以返回最少使用的主题。

      SELECT b.body, n.nid
      FROM node n
      LEFT JOIN body b ON n.nid = b.entity_id
      LEFT JOIN topic_usage u ON n.nid = u.entity_id
      LEFT JOIN reference r ON n.nid = r.entity_id
        AND (
          r.entity_type =  'node'
        AND
          r.deleted =  '0'
        )
      WHERE n.type = :type
      AND n.status = 1
      AND r.target_id= :id
      ORDER BY u.field_topic_usage_value ASC LIMIT 0,1

示例表

nid | Usage Count
-----------------
 1  | 0
 2  | 0
 3  | 0
 4  | 1

上面的查询可以很好地返回使用最少的主题。但要求是在最低使用率上应用 RAND()。

根据上面的示例表，查询应该执行 rand()，其中使用计数为 0。

我可以编写 2 个查询，第一个查询获得最低计数，第二个查询根据该计数执行 rand() 但可以将其合并为 1 个吗？

谢谢。

解决方案 1 @fancypants

      SELECT b.body, node.nid
      FROM node 
      LEFT JOIN body ON n.nid = body.entity_id 
      LEFT JOIN topic_usage ON n.nid = u.entity_id 
      LEFT JOIN field_data_field_issue_reference r ON node.nid = f.entity_id , 
        ( select @min := ( 
            SELECT min(f.usage_value) from node n 
            LEFT JOIN field_data_field_topic_usage ON n.nid = f.entity_id 
           )
        ) var_init_subquery 
      AND node.status = 1 
      AND f.field_issue_reference_target_id = 708 
      order by f.usage_value, if(f.usage_value=@min, rand(), node.nid) 
      LIMIT 0,1

解决方案 2 @Hitesh

    SELECT b.body, node.nid
    FROM node
    LEFT JOIN body ON node.nid = b.entity_id
    LEFT JOIN topic_usage ON node.nid = f.entity_id
    LEFT JOIN issue_reference f ON node.nid = f.entity_id
      AND (
        f.entity_type =  'node'
      AND
        f.deleted =  '0'
      )
    WHERE node.type = 'issue_paragraphs'
    AND node.status = 1
    AND f.field_issue_reference_target_id  = 708
    GROUP BY node.nid having f.usage_value=min(f.usage_value)
    ORDER BY rand() LIMIT 0,1

Answer 1

示例数据:

CREATE TABLE t
    (`a` int, `b` int)
;

INSERT INTO t
    (`a`, `b`)
VALUES
    (1, 1),
    (2, 1),
    (3, 0),
    (4, 0),
    (5, 1),
    (6, 0)
;

查询:

select
*
from
t
order by b, if(b=0, rand(), a)

• 看到它在 sqlfiddle 中实时运行

每次执行它，a对于 b 的条目，列随机排序是0并按 a 订购其中 b不是 0 .

要让它以最低限度工作，您可以简单地使用 variables :

select
a, b
from
t
, (select @min := (select min(b) from t)) var_init_subquery
order by b, if(b=@min, rand(), a)

• 再次，sqlfiddle