sql - 如何在左外连接上显示 0 计数缺失设施

Question

我有一个看起来像这样的设施列表:

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

协商

---------------------------------------
| 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

对于当前的查询，我不会返回第 1 个月的 RCDOC 的 count。

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