即使SQLite 尚未实现 RIGHT OUTER 或 FULL OUTER ，它 does 有 LEFT OUTER JOIN ，它应该做你想做的事。只需要在左边有 tbProjects 。

  SELECT tbProjects.projectId， 
 COALESCE（SUM（tbTasks.taskTime），0）AS totalTime，
 tbProjects.projectName 
 FROM tbProjects 
 LEFT OUTER JOIN tbTasks ON tbProjects.projectId = tbTasks.projectId 
 GROUP BY tbProjects.projectId 
 ORDER BY tbProjects.created DESC 
 

您得到 NULLS 包含在 totalTime 中，用于没有任何任务的项目以及对 COALESCE（） 用 0 替换null >。

I have the following select statement where I need to sum each task from table tbTasks and group them by projectId from table tbProjects in order to get a record like this:

ProjectID = 1, ProjectName = 'My Project', TotalTime = 300 //<--sum of each task time

The query looks like this:

SELECT tbTasks.projectId, 
       SUM(tbTasks.taskTime) AS totalTime, 
       tbProjects.projectName 
FROM tbTasks 
    INNER JOIN tbProjects ON tbTasks.projectId = tbProjects.projectId 
GROUP BY tbTasks.projectId 
ORDER BY tbProjects.created DESC

This works and executes fine but with one problem, if a project has no task associated with it, then I get no record back at all (where I want to get projectId, projectName, and 0 or NULL for totalTime). So, in order to right join on the table tbProjects SQLite3 forces me to do it in a round-about way.

SELECT tbTasks.projectId, 
       SUM(tbTasks.taskTime) AS totalTime, 
       tbProjects.projectName 
FROM tbTasks LEFT OUTER JOIN tbProjects
       ON tbTasks.projectId = tbProjects.projectId 
GROUP BY tbTasks.projectId 
UNION 
SELECT tbProjects.projectId, 
       SUM(tbTasks.taskTime) AS totalTime, 
       tbProjects.projectName   
FROM tbProjects LEFT OUTER JOIN tbTasks 
      ON tbProjects.projectId = tbTasks.projectId 
GROUP BY tbTasks.projectId 
ORDER BY tbProjects.created DESC

Only this does not work, I get an SQL syntax error. What am I doing wrong? Is there a better way to achieve my goal?

Even though SQLite hasn't implemented RIGHT OUTER or FULL OUTER, it does have LEFT OUTER JOIN, which should do what you'd like. Just have tbProjects be on the left.

SELECT tbProjects.projectId, 
       COALESCE(SUM(tbTasks.taskTime), 0) AS totalTime, 
       tbProjects.projectName 
FROM tbProjects
    LEFT OUTER JOIN tbTasks ON tbProjects.projectId = tbTasks.projectId
GROUP BY tbProjects.projectId 
ORDER BY tbProjects.created DESC

You get NULLS in totalTime for projects that don't have any tasks, and the call to COALESCE() replaces the null with a 0.