2左连接的语法在sqlite中加入

Question

此查询运行良好，并给我一个这样的输出：

[(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
    """

但是，当我添加另一个左JOIN时，它并不能给我准确的结果：

    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)]

看起来像值任务x 10 as count（任务）和计数（歌曲） 我错过了什么？

count（tasks.userid）的想法是查找用户ID匹配的任务数。

Answer 1

在任务和歌曲中分别汇总，然后将用户加入聚合的结果：

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加入，加入实际上是内部 JOIN，因为条件wend tasks.completed = false仅返回匹配行。