代码之家 › 专栏 › 技术社区 › BMBM

非常困难的mysql查询-两个表上的随机顺序

mysql

BMBM · 技术社区 · 14 年前

考虑一下这个经典的设置:

entry 表格:
id(整数,主键)
标题(varchar 255)

entry_category 表格:
条目id(int)

category 表格:
id(整数,主键)

这基本上意味着条目可以在一个或多个类别中表用作MM/连接表)

现在我需要随机查询6个唯一的类别以及这些类别中的1个唯一条目!

澄清:目的是显示6个随机类别,每个类别有1个随机条目 .

对的

category_id   entry_id     
10            200 
20            300
30            400
40            500
50            600
60            700

这是不正确的,因为在 category_id

category_id  entry_id
10           300
20           300
...

这是不正确的,因为 member_id 列:

category_id  entry_id     
20           300
20           400
...

我该如何查询?

select c.id, e.id
from category c    
inner join entry_category ec on ec.category_id = c.id
inner join entry e on e.id = ec.entry_id
group by c.id
order by rand()

性能目前不是最重要的因素,但我需要一个可靠的工作查询来实现这一点,而上面的查询几乎毫无用处,根本做不到我想要的事情。

编辑:顺便说一下,上面的查询在使用 select distinct ... 不考虑 group by . 这包括重复的行 distinct 只确保 c.id 和 e.id

编辑: 我发现,但在更大的数据集上可能会慢得要命:

select t1.e_id, t2.c_id
from (select e.id as e_id from entry e order by rand()) t1
inner join (select ec.entry_id as e_id, ec.category_id as c_id from entry_category ec group by e_id order by rand()) t2 on t2.e_id = t1.e_id
group by t2.c_id
order by rand()

2 回复 | 直到 14 年前

Quassnoi 14 年前

SELECT  category_id, entity_id
FROM    (
        SELECT  category_id,
                @ce :=
                (
                SELECT  entity_id
                FROM    category_entity cei
                WHERE   cei.category_id = ced.category_id
                        AND NOT FIND_IN_SET(entity_id, @r)
                ORDER BY
                        RAND()
                LIMIT 1
                ) AS entity_id,
                (
                SELECT  @r := CAST(CONCAT_WS(',', @r, @ce) AS CHAR)
                )
        FROM    (
                SELECT  @r := ''
                ) vars,
                (
                SELECT  DISTINCT category_id
                FROM    category_entity
                ORDER BY
                        RAND()
                LIMIT 15
                ) ced
       ) q
WHERE  entity_id IS NOT NULL
LIMIT  6

这个解决方案不是我引以为豪的代码,因为它依赖于 MySQL 保留递归堆栈。然而,它是有效的。

6 值(如果 entity_id 在类别中重复的次数太多)。在这种情况下,您可以增加 15

创建唯一索引或 PRIMARY KEY 在 category_entity (category_id, entity_id) 为了让它快速工作。

Pointy 14 年前

在我看来,这样做的好方法是从每个集合中选取6个不同的值,对每个值列表(每个列表单独)进行无序排列,然后将这些列表粘合到一个两列的结果中。

随机选择你得到的6个,洗牌列出每种类型的值,并获取前六个值。