sql - 如何从嵌套集中的每个组中检索一个项目

Question

我有三个用于将产品与类别匹配的表:

1. 类别，
2. 产品，(实际上可以忽略)
3. categories_products 多对多引用表

类别表是一个嵌套集，简化后的样子:

categories:
id, parent_id, name,                 level
1,  null,      Electronics,          1
2,  1,         Kitchen Appliances,   2
3,  1,         Personal Electronics, 2
5,  2,         Coffee Makers,        3
6,  2,         Juicers,              3
7,  3,         MP3 Players,          3
8,  3,         Mobile Phones,        3
....
1000,

categories_products 很简单

categories_products
id, category_id, product_id
1,  5,           42
2,  5,           45
3,  6,           63
4,  7,           76
5,  7,           32
...
8000,

注意:所有产品都链接到类别树的第 3 级

现在我想要的是一个可以为每个二级类别返回 1(随机)产品的查询。 (按第一级类别分组，或者我可以单独查询传递第一级 ID。)

所以在上面的示例中，我会从 Kitchen Applicances 中获得 1 件元素，这意味着一件来自类别 5 或 6 的随机元素，以及一件来自 Personal Electroincs 的元素，这意味着一件元素来自类别 7。

这超出了我的 sql 水平，因此寻求任何可能的帮助。

非常感谢。

Answer 1

尝试此查询并将其与 products 表连接起来。 ROW_NUMBER 行在一个类别中随机订购产品，因此您只需选择第一个 - (rn=1)。

SELECT product_id,
       category_id,
       category_name 
FROM
( select product_id,
         c2.id category_id,
         c2.name category_name,
         row_number() OVER (PARTITION BY c2.id ORDER BY random()) as rn
  FROM categories_products cp
  JOIN categories c3 ON (cp.category_id=c3.id)
  JOIN categories c2 ON (c3.parent_id=c2.id) 
) T 
WHERE rn =1 

SQLFiddle demo