SQLite3用LEFT JOIN和UNION模拟RIGHT OUTER JOIN [英] SQLite3 Simulate RIGHT OUTER JOIN with LEFT JOINs and UNION

问题描述

我有下面的select语句，我需要从表tbTasks中总结每个任务，并通过table tbProjects中的projectId对它们进行分组，以获得如下记录：

  ProjectID = 1，ProjectName ='My Project'，TotalTime = 300 //<  - 每个任务时间的总和
  

查询如下所示：

  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 
  

这个工作正常并且执行得很好，但有一个问题，项目没有任何与它相关的任务，那么我根本就没有记录（我想要得到projectId，projectName和totalTime的0或NULL）。所以，为了在表上正确加入，tbProjects SQLite3迫使我以循环方式完成它。

  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 
  

只有这样做不行，我得到一个SQL语法错误。我究竟做错了什么？有没有更好的方法来实现我的目标？

解决方案

即使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 。

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.

这篇关于SQLite3用LEFT JOIN和UNION模拟RIGHT OUTER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！