使用外部联接并限制每个父级的子记录的SQL查询 [英] SQL query using outer join and limiting child records for each parent

问题描述

我在弄清楚如何构造SQL查询时遇到了麻烦.假设我们有一个User表和一个Pet表.每个用户可以养很多只宠物，并且Pet有一个繁殖"列.

I'm having trouble figuring out how to structure a SQL query. Let's say we have a User table and a Pet table. Each user can have many pets and Pet has a breed column.

User:

id    |    name
______|________________
1     |    Foo
2     |    Bar

Pet:

id    |    owner_id    |    name    |    breed    |
______|________________|____________|_____________|
1     |        1       |  Fido      |  poodle     |   
2     |        2       |  Fluffy    |  siamese    |

最终目标是提供一个查询，该查询将为我提供与给定where子句匹配的每个用户的所有宠物，同时允许使用sort和limit参数.因此，可以将每个用户的宠物限制为5个并按名称排序.

The end goal is to provide a query that will give me all the pets for each user that match the given where clause while allowing sort and limit parameters to be used. So the ability to limit each user's pets to say 5 and sorted by name.

我正在为ORM动态构建这些查询，因此我需要一个可以在MySQL和Postgresql中运行的解决方案(尽管它可以是两个不同的查询).

I'm working on building these queries dynamically for an ORM so I need a solution that works in MySQL and Postgresql (though it can be two different queries).

我已经尝试过类似的方法，但这种方法不起作用:

I've tried something like this which doesn't work:

SELECT "user"."id", "user"."name", "pet"."id", "pet"."owner_id", "pet"."name",
  "pet"."breed"
FROM "user"
LEFT JOIN "pet" ON "user"."id" = "pet"."owner_id"
WHERE "pet"."id" IN
  (SELECT "pet"."id" FROM "pet" WHERE "pet"."breed" = 'poodle' LIMIT 5)

推荐答案

在 Postgres (8.4或更高版本)中，使用

In Postgres (8.4 or later), use the window function row_number() in a subquery:

SELECT user_id, user_name, pet_id, owner_id, pet_name, breed
FROM  (
   SELECT u.id AS user_id, u.name AS user_name
        , p.id AS pet_id, owner_id, p.name AS pet_name, breed
        , row_number() OVER (PARTITION BY u.id ORDER BY p.name, pet_id) AS rn
   FROM  "user"    u
   LEFT   JOIN pet p ON p.owner_id = u.id
                    AND p.breed = 'poodle'
   ) sub
WHERE  rn <= 5
ORDER  BY user_name, user_id, pet_name, pet_id;

• 使用LEFT JOIN时，不能将其与 left 表上的WHERE条件结合使用.这会强制将LEFT JOIN转换为普通的[INNER] JOIN(并且可能会从您不想删除的结果中删除行).将此类条件拉入join子句.
  按照我的理解，结果中包括没有宠物的用户-与您的查询存根相反.

  • When using a LEFT JOIN, you can't combine that with WHERE conditions on the left table. That forcibly converts the LEFT JOIN to a plain [INNER] JOIN (and possibly removes rows from the result you did not want removed). Pull such conditions up into the join clause.
    The way I have it, users without pets are included in the result - as opposed to your query stub.

    ORDER BY子句中的其他id列应该打破非唯一名称之间的可能联系.

    The additional id columns in the ORDER BY clauses are supposed to break possible ties between non-unique names.

    请勿使用保留的单词，例如user 作为标识符.

    Never use a reserved word like user as identifier.

    按照您的命名约定进行操作. id或name是糟糕的，非描述性的选择，即使某些ORM提出了这种胡说八道.正如您在查询中看到的那样，当联接几个表时，这会导致复杂化，这在SQL中是您要做的.
    开头应该是pet_id，pet，user_id，username等.

    Work on your naming convention. id or name are terrible, non-descriptive choices, even if some ORMs suggest this nonsense. As you can see in the query, it leads to complications when joining a couple of tables, which is what you do in SQL.
    Should be something like pet_id, pet, user_id, username etc. to begin with.

    使用适当的命名约定，我们可以在子查询中只是SELECT *.

    With a proper naming convention we could just SELECT * in the subquery.

    MySQL不支持窗口功能，有烦躁的替代品...

    MySQL does not support window functions, there are fidgety substitutes ...

    这篇关于使用外部联接并限制每个父级的子记录的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！