php - 从许多 post_ids 中选择 1 个 MySQL 中的随机项目

Question

我有一个这样布局的数据库:

Array
(
    [0] => Array
        (
            [id] => 23
            [post_id] => 9480
            [title] => This is numero uno
        )

    [1] => Array
        (
            [id] => 25
            [post_id] => 9480
            [title] => I like food
        )

    [2] => Array
        (
            [id] => 26
            [post_id] => 9480
            [title] => Oranges!
        )

    [3] => Array
        (
            [id] => 28
            [post_id] => 9476
            [title] => The quick brown fox
        )

    [4] => Array
        (
            [id] => 29
            [post_id] => 9476
            [title] => jumped!
        )

)

请注意 post_id 在某些行上可以相同。这基本上是属于单个帖子的多个标题变体。

我想做的是在给定 post_id 的 ID 数组时使用 MySQL 随机选择一个标题

所以输出可能是这样的(来自上面的输入):

9480 - 25 - I like food
9476 - 28 - The quick brown fox

其中每一个都是从 post_id 的子集中随机选择的。

这就是我到目前为止所拥有的……不多:/

SELECT `id`, `post_id`, `title` FROM `headlines` WHERE `post_id` IN(9480,9476)

Answer 1

按 rand() 排序，然后将 1 个限制为每个 OP 请求仅获得一个。请参阅工作 FIDDLE

SELECT
    mt.id, 
    mt.post_id, 
    mt.title
FROM (
  SELECT
  DISTINCT mt1.post_id AS my_post_id,
     (
       SELECT
         tttt.id AS m_id
       FROM myTable tttt
       WHERE tttt.post_id = my_post_id
       ORDER BY rand()
       LIMIT 1
     ) AS t
  FROM myTable mt1
  GROUP BY mt1.post_id
) AS tt
JOIN myTable mt ON mt.id = t
GROUP BY mt.post_id