sql server count() function?

Question

我有这个Sql查询来显示Project下有多少任务，但问题是它没有显示任务为0的项目，有人能帮我吗？

SELECT Projects.projectName AS [Project Name],
    COUNT(Projects_tasks.taskID) AS #tasks,
    Projects.projectID AS [Project ID]
FROM Projects
INNER JOIN Projects_tasks
ON Projects.projectID = Projects_tasks.projectID 
GROUP BY Projects.projectName,
    Projects_tasks.taskID,
    Projects.projectID

Answer 1

使用Left Join

SELECT     Projects.projectName AS [Project Name],
                  ISNULL(COUNT(Projects_tasks.taskID),0) AS #tasks, Projects.projectID AS [Project ID]
FROM       Projects LEFT JOIN
                  Projects_tasks ON Projects.projectID = Projects_tasks.projectID 
GROUP BY Projects.projectName,Projects.projectID

也删除您在group by 中计数的column

Answer 2

使用LEFT join、ISNULL（），不要按正在计数的列分组。

SELECT     Projects.projectName AS [Project Name],
                  ISNULL(COUNT(Projects_tasks.taskID), 0) AS #tasks, Projects.projectID AS [Project ID]
FROM       Projects LEFT JOIN
                  Projects_tasks ON Projects.projectID = Projects_tasks.projectID 
GROUP BY Projects.projectName,Projects.projectID

Answer 3

您应该使用LEFT JOIN来确保包含0个任务的项目也包含

SELECT     Projects.projectName AS [Project Name],
           ISNULL(COUNT(Projects_tasks.taskID),0) AS #tasks, Projects.projectID AS [Project ID]
FROM       Projects LEFT JOIN
                  Projects_tasks ON Projects.projectID = Projects_tasks.projectID 
GROUP BY Projects.projectName,Projects_tasks.taskID,Projects.projectID

Answer 4

尝试将INNER JOIN更改为LEFT JOIN

您没有看到0，因为没有要加入的任务。

Answer 5

使用LEFT JOIN。以及CCD_ 5来转换CCD_ 6的值。COALESCE必须在COUNT函数内，因为列Projects_tasks.taskID可以是NULL。

SELECT Projects.projectName AS [Project Name],
       COUNT(COALESCE(Projects_tasks.taskID, 0)) AS #tasks,
       Projects.projectID AS [Project ID]
FROM   Projects
         LEFT JOIN Projects_tasks
            ON Projects.projectID = Projects_tasks.projectID 
GROUP BY Projects.projectName,
    Projects_tasks.taskID,
    Projects.projectID

Answer 6

我怀疑您的查询是否会以任务数的形式返回超过1个任务。Projects_tasks.taskID不应在group子句中。对于您的问题，您必须放置LEFT JOIN而不是内部联接，这将起作用。

SELECT Projects.projectName AS [Project Name],
    sum(case when Projects_tasks.taskID is null then 0 else 1 end) AS #tasks,
    Projects.projectID AS [Project ID]
FROM Projects  
LEFT JOIN Projects_tasks
ON Projects.projectID = Projects_tasks.projectID 
GROUP BY Projects.projectName,
    Projects.projectID

Answer 7

尝试将INNER JOIN更改为LEFT JOIN

SELECT     Projects.projectName AS [Project Name],
       ISNULL(COUNT(Projects_tasks.taskID),0) AS #tasks, Projects.projectID AS [Project ID]
FROM Projects LEFT JOIN Projects_tasks ON Projects.projectID = Projects_tasks.projectID
GROUP BY Projects.projectName,Projects_tasks.taskID,Projects.projectID