在第二个表上使用限制10加入查询

I have designer and design table

我有设计师和设计表

designer has many designs

设计师有很多设计

I want to get all designers with 10 designs for each designer

我想为每位设计师提供10个设计的所有设计师

Is there way I can do it in a single query in either postgresql, mysql

有没有办法可以在postgresql,mysql中的单个查询中完成

3 个解决方案

#1

For best performance in Postgres (not possible in MySQL) use a LATERAL join:

为了在Postgres中获得最佳性能(在MySQL中不可能),请使用LATERAL join:

SELECT d.designer, d1.design  -- whatever columns you need
FROM   designer d
LEFT   JOIN LATERAL (
   SELECT *                   -- or just needed columns
   FROM   design
   WHERE  designer_id = d.designer_id
   -- ORDER  BY ???           -- you may want to pick certain designs
   LIMIT  10
   ) d1 ON true
ORDER  BY d.designer_id, d.design_id;  -- or whatever

This gives you 10 designs per designer - or as many as he/she has if there are fewer.

这为每位设计师提供了10个设计 - 如果有更少的设计,则可以为他/她提供多少设计。

LEFT JOIN LATERAL ... ON true keeps designers in the result that don't have a single design (yet).
LEFT JOIN LATERAL ... ON true使设计师在结果中没有单一设计(尚未)。
- What is the difference between LATERAL and a subquery in PostgreSQL?
- Call a set-returning function with an array argument multiple times
You get best performance if you add an ORDER BY clause that matches an existing index on design like:
如果添加与设计上的现有索引匹配的ORDER BY子句,则可获得最佳性能,如:
```
CREATE INDEX foo ON design (designer_id, design_id)
```
Then, in the subquery d1 in above query:
然后,在上面查询的子查询d1中:
```
...
ORDER  BY design_id
...
```
Now Postgres can pick the top items from the index directly.
现在Postgres可以直接从索引中选择顶级项目。

3 个解决方案

#1

更多相关文章

随机推荐