sql - 包含计数不工作的内部连接的存储过程

Question

假设我有一个看起来有点像这样的数据库表，其中包含有关某些作业的信息。

Id   | ProfessionId      | Title       | Deadline     | DateCreated | ClosingDate
1    |  5                | Something   | 01-12-2012   | 05-11-2012  | 12-11-2012
2    |  6                | Something   | 01-12-2012   | 05-11-2012  | 12-11-2012
3    |  7                | Something   | 01-12-2012   | 05-11-2012  | 12-11-2012
4    |  7                | Something   | 01-12-2012   | 05-11-2012  | 12-11-2012

我想为每个职业(任务属于某个职业)生成一个概览，并计算每个职业的任务数量。来自数据库的概览应如下所示；

Id | Name           | FriendlyUrl     | Ordinal   | NumberOfAssignments
5  | Profession 1   | profession-1    | 1         | 1
6  | Profession 2   | profession-2    | 1         | 1
7  | Profession 3   | profession-3    | 1         | 2
8  | Profession 4   | profession-4    | 1         | 0

我目前有一个存储过程返回上面的概述，只是分配的数量不正确。过去截止日期的作业(我们假设作业已关闭)不应计入作业总数。

当前的存储过程是这样的:

BEGIN
    SELECT  p.Id, 
            p.Naam, 
            p.FriendlyUrl, 
            p.Ordinal, 
            COUNT(a.ProfessionId) AS NumberOfAssignments
    FROM ME_Profession AS p 
    LEFT OUTER JOIN ME_Assignment AS a ON a.ProfessionId = p.Id
    INNER JOIN ME_Client AS c ON a.ClientId = c.Id
    INNER JOIN aspnet_Membership AS m ON m.UserId = c.UserId
    WHERE m.IsApproved = 1 
    GROUP BY p.Id, p.Naam, p.FriendlyUrl, p.Ordinal
END

我已经提出并修改了如下所示的程序，但它不起作用。感觉我要么想得太难了，要么漏掉了一些明显的东西。会出什么问题？

SELECT        p.Id, p.Naam, p.FriendlyUrl, p.Ordinal, pc.NumberOfAssignments
FROM            ME_Profession AS p 
INNER JOIN ME_Assignment AS a ON a.ProfessionId = p.Id 
INNER JOIN ME_Client AS c ON a.ClientId = c.Id
INNER JOIN aspnet_Membership AS m ON m.UserId = c.UserId
INNER JOIN (SELECT a2.ProfessionId, COUNT(*) AS NumberOfAssignments FROM ME_Assignment AS a2 GROUP BY a2.ProfessionId WHERE a2.Closingdate > GETDATE()) pc ON p.ProfessionId = pc.ProfessionId
WHERE        m.IsApproved = 1 AND a.Closingdate > GETDATE()
GROUP BY p.Id, p.Naam, p.FriendlyUrl, p.Ordinal

更新 1: 添加了 date 的 where 条件

Answer 1

我认为您不需要再次加入 ME_profession 表，试试这个:

SELECT  p.Id, p.Naam, p.FriendlyUrl, p.Ordinal, pc.NumberOfAssignments,
        COUNT(CASE WHEN ClosingDate > GETDATE() OR ClosingDate IS NULL THEN 1 END) AS NumberOfAssignments 
FROM  ME_Profession AS p 
INNER JOIN ME_Assignment AS a 
    ON a.ProfessionId = p.Id 
INNER JOIN ME_Client AS c 
    ON a.ClientId = c.Id
INNER JOIN aspnet_Membership AS m 
    ON m.UserId = c.UserId
WHERE (m.IsApproved = 1)
GROUP BY p.Id, p.Naam, p.FriendlyUrl, p.Ordinal