Python sqlite 中 2 个左连接的语法

发布于04月17日

该查询运行良好，并给出如下输出:

[(1, 9), (2, 12), (4, 14), (6, 14)]

    query = """
    SELECT users.id,
       count(tasks.userId)
    FROM users
    LEFT JOIN tasks ON users.id = tasks.userId
    WHERE tasks.completed = FALSE
    GROUP BY users.id
    """

但是，当我添加另一个左连接时，它不会给出准确的结果:

    query = """
    SELECT users.id,
       count(tasks.userId), count(songs.userId)
    FROM users
    LEFT JOIN tasks ON users.id = tasks.userId
    LEFT JOIN songs ON users.id = songs.userId
    WHERE tasks.completed = FALSE
    GROUP BY users.id
    """

结果应该是这样的:

[(1, 9, 10), (2, 12, 10), (4, 14, 10), (6, 14, 10)]

但是，我的结果是这样的:

[(1, 90, 90), (2, 120, 120), (4, 140, 140), (6, 140, 140)]

看起来像是tasks x 10的值，count(任务)和count(歌曲)

count(tasks.userId)个用户的 idea 是找到用户ID匹配的任务数.

推荐答案

分别在tasks和songs中聚合，然后将users加入聚合结果:

SELECT u.id, 
       COALESCE(t.count_tasks, 0) count_tasks,
       COALESCE(s.count_songs, 0) count_songs
FROM users u
LEFT JOIN (
  SELECT userId, COUNT(*) count_tasks
  FROM tasks 
  WHERE completed = FALSE
  GROUP BY userId
) t ON u.id = t.userId
LEFT JOIN (
  SELECT userId, COUNT(*) count_songs
  FROM songs  
  GROUP BY userId
) s ON u.id = s.userId;

我不确定您是否真的想要LEFT个联接(至少对于表tasks)，因为在您的代码中，您所说的第一个查询返回您期望的结果，尽管它包含LEFT个联接，但实际上该联接是INNER个联接，因为条件WHERE tasks.completed = FALSE只返回匹配的行.