本文介绍了SQLite3用LEFT JOIN和UNION模拟RIGHT OUTER JOIN的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有下面的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 中,用于没有任何任务的项目以及对用 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-22 14:34
查看更多