联接四个涉及LEFT JOIN的表，没有重复项

一尘不染

联接四个涉及LEFT JOIN的表，没有重复项

sql

我想联接四个具有空值且没有重复的表，然后将其转换为SQLAlchemy查询。

这些表是（简化的）：

Category(id, name)
Task(id, category.id, name)
User(id, name)

还有一个多对多表：

Solved(task.id, user.id)

我想获得所有任务及其类别，并列出解决任务的特定用户：

+---------------+-----------+-----------+
| category.name | task.name | user.name |
+---------------+-----------+-----------+
| abc           | abctask1  | <null>    |
| abc           | abctask2  | luke      |
| def           | deftask1  | <null>    |
| ghi           | ghitask1  | <null>    |
| ghi           | ghitask2  | luke      |
+---------------+-----------+-----------+

目前，我有3到4个单独的SQLAlchemy查询来执行该任务。如果可能，应将其合并为一个查询，以避免对数据库进行过多读取。

到目前为止，我已经：

SELECT DISTINCT
  cat.name, t.name, u.name
FROM
  Task t
JOIN 
  Category cat ON cat.id = t.category_id
LEFT JOIN 
  Solved s ON s.task_id = t.id
LEFT JOIN 
  User u ON s.user_id = u.id AND
  u.name = 'luke'
ORDER BY
  cat.name

但是，尽管DISTINCT，但是与给定用户在所有行中都有重复项：

+---------------+-----------+-----------+
| category.name | task.name | user.name |
+---------------+-----------+-----------+
| abc           | abctask1  | <null>    |
| abc           | abctask2  | luke      |
| abc           | abctask2  | <null>    | <-- duplicate
| def           | deftask1  | <null>    |
| ghi           | ghitask1  | <null>    |
| ghi           | ghitask2  | luke      |
| ghi           | ghitask2  | <null>    | <-- duplicate
+---------------+-----------+-----------+

是否可以通过一个查询获取此表并将其转换为SQLAlchemy？

阅读 188

2021-03-17

共1个答案

一尘不染

您有两个LEFT JOINS：

左第一个联接可以从连接到多个行solved。说“ jane”和“ luke”解决了任务。
左第2个联接只能与名为“ luke”的用户联接（联接条件中为“ luke”！）。

您仍然会得到两行，只是未显示“ jane”，联接条件将其过滤掉，但LEFT JOIN无论如何仍将行保留在结果中并追加NULL值。

您可以通过使用括号和[INNER] JOIN而不是LEFT JOIN之间的solved和来实现您想要的功能users。手册：

如有必要，请使用括号来确定嵌套顺序。在没有括号的情况下，JOINs从左到右嵌套。

SELECT c.name AS cat_name, t.name AS task_name, u.name AS user_name
FROM   task t
JOIN   category c ON cat.id = t.category_id
LEFT   JOIN
      (solved s JOIN users u ON u.id = s.user_id AND u.name = 'luke') ON s.task_id = t.id
ORDER  BY 1, 2, 3;

使用表名users代替保留字 ~~user~~ 。
假设users.name定义为 唯一， 或者您可以有多个名为“ luke”的用户。
如果(task.id, users.id)insolved定义为UNIQUE或PRIMARY KEY，则完全不需要DISTINCT。

结果查询不仅正确，而且速度更快。

SQLAlchemy的版本上述查询的： （贡献的@van）
这是假定Category，Task和User被映射类，而solved是实例Table（如图代码示例只是一个关联表多对多）：

user_name = 'luke'
q = (session.query(Category.name, Task.name, User.name)
     .select_from(Task)
     .join(Category)
     .outerjoin(
         join(solved, User,
              (solved.c.user_id == User.id) & (User.name == user_name),
         ))
     .order_by(Category.name, Task.name, User.name)
     )

2021-03-17