HANA SQL查询修改：如何包含计数为0的Office记录？

OFFICE_DETAILS表中存在a、b、c、d四个Office，当前执行的HANA SQL查询仅返回存在检验计数的Office（结果排除了Office c），现有查询结果如下：

office_name    lab_count   rad_count
 a             5            0
 b             1            2
 d             3            1

需要修改查询，使结果包含所有Office，无检验记录的Office的LAB_TEST_COUNT和RAD_TEST_COUNT显示为0，期望输出：

office_name    lab_count   rad_count
 a             5            0
 b             1            2
 c             0            0
 d             3            1

原SQL代码 #

SELECT  d.OFFICE_name AS "OFFICE_NAME" ,
   count(CASE 
            WHEN c.LAB_TYPE LIKE 'L' THEN 1
        END) AS "LAB_TEST_COUNT", 
   count(CASE 
            WHEN c.LAB_TYPE in ('X','O') THEN 1
        END) AS "RAD_TEST_COUNT"
FROM 
DOCTOR_CONSULT a
INNER JOIN  consult_labtest b
on(a.CONSULT_ID=b.CONSULT_ID) 
INNER JOIN test_setup c
 on(b.LABTEST_ID=c.TEST_ID)
INNER JOIN OFFICE_DETAILS d
on(a.OFFICE_ID=D.OFFICE_ID)
INNER JOIN USER_SETUP e
on(a.DOCTORS_ID = e.USER_ID)
INNER JOIN DEPARTMENT_SETUP f
ON(a.DEPARTMENT_ID = f.DEPARTMENT_ID)
INNER JOIN TEST_CATEGORY g
  ON (c.CATEGORY_ID=g.CATEGORY_ID)
WHERE 
c.LAB_TYPE IN ('L','X','O') --'L'-> Laboratory, 'X'-> Radiology
AND c.ACTIVE_STATUS='Y'
AND d.ACTIVE_STATUS='Y'
AND g.ACTIVE_STATUS='Y'
AND
a.CONSULT_DATE BETWEEN CURRENT_DATE AND CURRENT_DATE
AND 
d.ACTIVE_STATUS='Y'
AND 
a.EMPLOYEE_ID NOT IN ('NEW RECRUITMENT 380', '0000', 'army', 'undefined')
GROUP BY d.OFFICE_NAME
ORDER BY d.OFFICE_NAME ASC;

修改方案 #

核心修改点 #

• 切换主表并使用左外连接：以OFFICE_DETAILS作为查询的起始主表，将所有INNER JOIN替换为LEFT JOIN，确保所有Office都能被查询到，即使没有对应的检验记录。
• 调整过滤条件位置：将原WHERE子句中涉及关联表（如DOCTOR_CONSULT、test_setup等）的过滤条件，移动到对应的LEFT JOIN的ON子句中，避免无记录的Office被WHERE条件过滤掉。
• 保留主表自身过滤条件：d.ACTIVE_STATUS='Y'保留在WHERE子句中，确保只统计激活状态的Office。

修改后的完整SQL代码 #

SELECT  d.OFFICE_name AS "OFFICE_NAME" ,
   COUNT(CASE WHEN c.LAB_TYPE LIKE 'L' THEN 1 END) AS "LAB_TEST_COUNT", 
   COUNT(CASE WHEN c.LAB_TYPE IN ('X','O') THEN 1 END) AS "RAD_TEST_COUNT"
FROM 
OFFICE_DETAILS d
LEFT JOIN DOCTOR_CONSULT a
  ON a.OFFICE_ID = d.OFFICE_ID
  AND a.CONSULT_DATE BETWEEN CURRENT_DATE AND CURRENT_DATE
  AND a.EMPLOYEE_ID NOT IN ('NEW RECRUITMENT 380', '0000', 'army', 'undefined')
LEFT JOIN consult_labtest b
  ON a.CONSULT_ID = b.CONSULT_ID
LEFT JOIN test_setup c
  ON b.LABTEST_ID = c.TEST_ID
  AND c.LAB_TYPE IN ('L','X','O')
  AND c.ACTIVE_STATUS='Y'
LEFT JOIN USER_SETUP e
  ON a.DOCTORS_ID = e.USER_ID
LEFT JOIN DEPARTMENT_SETUP f
  ON a.DEPARTMENT_ID = f.DEPARTMENT_ID
LEFT JOIN TEST_CATEGORY g
  ON c.CATEGORY_ID = g.CATEGORY_ID
  AND g.ACTIVE_STATUS='Y'
WHERE 
d.ACTIVE_STATUS='Y'
GROUP BY d.OFFICE_NAME
ORDER BY d.OFFICE_NAME ASC;

说明 #

使用LEFT JOIN后，即使某Office没有对应的咨询、检验记录，关联表的字段会返回NULL，COUNT(CASE...)会自动统计为0，符合期望输出要求；将关联表的过滤条件移到ON子句中，避免了WHERE子句过滤掉左连接后产生的NULL记录。

内容的提问来源于stack exchange，提问作者Luke