如何显示左侧外部联接中缺席设施的0计数

Question

我有一份类似于这样的设施清单：

Facilities
--------------------------------------------------
| ID | NAME                | ABBREVIATION        |
--------------------------------------------------
| 2  | Adams               | ACCC                |
--------------------------------------------------
| 3  | Bucks               | BCJS                |
--------------------------------------------------
| 4  | Rocking             | RCDOC               |
--------------------------------------------------

consultations

---------------------------------------
| CREATION_DATE | NAME                |
---------------------------------------
| 2014-01-01    | ACCC                |
---------------------------------------
| 2014-01-02    | ACCC                |
---------------------------------------
| 2014-01-01    | BCJS                |
---------------------------------------
| 2014-02-01    | RCDOC               |
---------------------------------------
| 2014-02-03    | BCJS                |
---------------------------------------

我希望的结果应该如下所示：

-----------------------------------------------------
| FACILITY_ID | MONTH                | COUNT        |
-----------------------------------------------------
| ACCC        | 1                    | 2            |
-----------------------------------------------------
| BCJS        | 1                    | 1            |
-----------------------------------------------------
| RCODOC      | 1                    | 0            |
-----------------------------------------------------
| ACCC        | 2                    | 0            |
-----------------------------------------------------
| BCJS        | 2                    | 1            |
-----------------------------------------------------
| RCODOC      | 2                    | 1            |
-----------------------------------------------------

我当前的查询如下：

SELECT 
    c.facility_id, 
    DATEPART(MONTH,creation_date), 
    COUNT(1) as count
FROM
    consultations c 
      left outer join facilities f on c.facility_id = f.abbreviation    
WHERE 
    DATEPART(YEAR, creation_date) = '2014'
GROUP BY 
    c.facility_id, 
    DATEPART(MONTH,creation_date)
ORDER BY 
    DATEPART(MONTH,creation_date), 
    c.facility_id

对于当前的查询，我不会在第一个月为RCDOC返回一个RCDOC。

Answer 1

这应该能起作用。

DECLARE @FACILITIES TABLE (FACILITY_ID INT, NAME VARCHAR(50), ABBREVIATION VARCHAR(50))
DECLARE @CONSULTATIONS TABLE (CREATION_DATE DATETIME, NAME VARCHAR(50))

INSERT INTO @FACILITIES
SELECT 2,'Adams','ACCC' UNION
SELECT 3,'Bucks','BCJS' UNION
SELECT 4,'Rocking','RCDOC'

INSERT INTO @CONSULTATIONS
SELECT '2014-01-01','ACCC' UNION
SELECT '2014-01-02','ACCC' UNION
SELECT '2014-01-01','BCJS' UNION
SELECT '2014-02-01','RCDOC' UNION
SELECT '2014-02-03','BCJS' 

SELECT F.ABBREVIATION, X.MOS, COUNT(C.CREATION_DATE) AS NUM
FROM @FACILITIES F 
CROSS JOIN (SELECT DISTINCT MONTH(CREATION_DATE) MOS FROM @CONSULTATIONS WHERE YEAR(CREATION_DATE) = 2014) AS X
LEFT OUTER JOIN @CONSULTATIONS C
   ON F.ABBREVIATION = C.NAME
   AND MONTH(C.CREATION_DATE) = X.MOS
GROUP BY F.ABBREVIATION, X.MOS
ORDER BY X.MOS, F.ABBREVIATION

Answer 2

你需要从所有设施和所有月份的清单开始。然后反馈你想要的数据。你会得到一个cross join的完整列表

SELECT f.facility_id, m.mon, 
       COUNT(c.facility_id) as count
FROM facilities f cross join
     (select distinct month(creation_date) as mon
      from consultations
      where year(creation_date) = 2014
     ) m left join
     consultations c 
     on c.facility_id = f.abbreviation and month(c.creation_date) = m.mon
GROUP BY f.facility_id, m.mon
ORDER BY m.mon, f.facility_id;

我不清楚c.facility_id是否应该是c.name，样本数据有一种方式，样例查询则是另一种方式。