如何在摘要查询中包含记录以包含没有数据的记录？

Question

我在事务表上有一个查询，该查询根据数据范围返回每个ID的列的汇总总数。查询效果很好，除了它不包括事务表中没有数据的那些ID。如何将这些ID包含在我的结果中，总计零。这是我查询的简化版本。

SELECT tblID.IDName
    ,SUM(CASE 
            WHEN tblTransactions.idxTransType = 30
                THEN CAST(tblTransactions.TimeAmount AS FLOAT) / 60.0
            ELSE 0
            END) AS 'Vacation'
FROM tblTransactions
INNER JOIN tblTransTypes ON tblTransactions.idxTransType = tblTransTypes.IdxTransType
INNER JOIN tblID ON tblTransactions.idxID = tblID.IdxID
WHERE (tblTransactions.Deleted = 0)
    AND (tblTransactions.NotCurrent = 0)
    AND (tblTransactions.TransDate >= CONVERT(DATETIME, 'March 1, 2018', 102))
    AND (tblTransactions.TransDate <= CONVERT(DATETIME, 'April 11, 2018', 102))
GROUP BY tblID.IDName

Answer 1

实际上比这要复杂得多：

SELECT        
    i.IDName, 
    SUM(CASE WHEN t.idxTransType = 30 THEN CAST(t.TimeAmount AS FLOAT) / 60.0 ELSE 0 END) AS 'Vacation'
FROM
    tblID i
    LEFT JOIN tblTransactions t ON t.idxID = i.IdxID AND t.Deleted = 0 AND t.NotCurrent = 0 AND t.TransDate BETWEEN '20180301' AND '20180411'
    LEFT JOIN tblTransTypes tt ON tt.IdxTransType = t.idxTransType
GROUP BY 
    i.IDName;

Answer 2

您想要left join S：

SELECT i.IDName, 
       SUM(CASE WHEN t.idxTransType = 30 THEN CAST(t.TimeAmount AS Float) / 60.0 ELSE 0 END) AS Vacation
FROM tblID i LEFT JOIN
     tblTransactions t
     ON t.idxID = i.IdxID AND
        t.Deleted = 0 AND 
        t.NotCurrent = 0 AND
        t.TransDate >= '2018-03-01' AND
        t.TransDate <= '2018-04-11'
     tblTransTypes tt
     ON t.idxTransType = tt.IdxTransType 
GROUP BY i.IDName;

注意：

• 表的别名使查询更容易编写和阅读。
• 使用ISO/ANSI标准日期格式。
• 除第一个表以外的所有表上的过滤条件属于ON子句。