问题描述
我有下面的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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!