如何合并两个SQL条件生成'Yes'或1结果并实现统计？

问题需求 #

需要将两个条件合并，当满足任意一个条件时返回'Yes'或1，以便统计符合条件的记录数量。现有条件语句：

AND (ZC_VERB_ORD_MODE_med.VERBAL_MODE_C IN ('118','1','102','101','107')
 OR ZC_VERB_ORD_MODE_proc.VERBAL_MODE_C IN ('118','1','102','101','107')) 

不确定是否需要使用CASE语句，以下是完整查询代码：

@startdate as DATE,
@enddate as DATE;
SET @startdate ='2023-02-01'
SET @enddate = '2023-02-02'
 
 SELECT 
 --CLARITY_LOC.LOC_NAME, 
 --CLARITY_DEP.REV_LOC_ID, 
 --CLARITY_LOC.LOC_ID, 
 --CLARITY_DEP.DEPARTMENT_NAME, 
 --ORDER_METRICS.PAT_LOC_ID, 
 --CLARITY_DEP.DEPARTMENT_ID, 
 --ZC_DEP_SPECIALTY.NAME, 
 --CLARITY_DEP.SPECIALTY_DEP_C, 
 --ZC_DEP_SPECIALTY.DEP_SPECIALTY_C, 
 --ORDER_METRICS.ORDERING_PROV_ID, 
  CLARITY_SER.PROV_NAME, 
  CLARITY_SER.PROV_ID,
 --ZC_NOTE_SER.NAME, 
 --CLARITY_SER.PROVIDER_TYPE_C, 
 --ZC_NOTE_SER.SERVICE_TYPE_C, 
 --ZC_ORDER_TYPE.NAME, 
 --ORDER_METRICS.ORDER_TYPE_C, 
 --ZC_ORDER_TYPE.ORDER_TYPE_C, 
 --ORDER_SIGNED_MED.VERBAL_MODE_C, 
 --ORDER_SIGNED_PROC.VERBAL_MODE_C, 
 ZC_VERB_ORD_MODE_med.NAME as VERBAL_MODE_MED,
 --ZC_VERB_ORD_MODE_med.VERBAL_MODE_C, 
 ZC_VERB_ORD_MODE_proc.NAME as VERBAL_MODE_PROC,
 --ZC_VERB_ORD_MODE_proc.VERBAL_MODE_C, 
 ORDER_METRICS.ORDER_DTTM, 
 --ORDER_SIGNED_MED.LINE, 
 --ORDER_SIGNED_PROC.LINE, 
 --ORDER_SIGNED_PROC.SIGNED_TYPE_C, 
 ORDER_METRICS.ORDER_ID, 
 --ORDER_SIGNED_MED.SIGNED_TYPE_C, 
 ORDER_METRICS.DISPLAY_NAME 
 --ORDER_METRICS.ORDER_DESC
 --ZC_LICENSE_DISPLAY.NAME
 
 FROM ORDER_METRICS ORDER_METRICS 
 LEFT OUTER JOIN ORDER_SIGNED_MED ORDER_SIGNED_MED ON ORDER_METRICS.ORDER_ID=ORDER_SIGNED_MED.ORDER_MED_ID 
 LEFT OUTER JOIN ORDER_SIGNED_PROC ORDER_SIGNED_PROC ON ORDER_METRICS.ORDER_ID=ORDER_SIGNED_PROC.ORDER_PROC_ID
 LEFT OUTER JOIN ZC_ORDER_TYPE ZC_ORDER_TYPE ON ORDER_METRICS.ORDER_TYPE_C=ZC_ORDER_TYPE.ORDER_TYPE_C
 LEFT OUTER JOIN CLARITY_DEP CLARITY_DEP ON ORDER_METRICS.PAT_LOC_ID=CLARITY_DEP.DEPARTMENT_ID
 LEFT OUTER JOIN CLARITY_SER CLARITY_SER ON ORDER_METRICS.ORDERING_PROV_ID=CLARITY_SER.PROV_ID
 LEFT OUTER JOIN ZC_VERB_ORD_MODE ZC_VERB_ORD_MODE_proc ON ORDER_SIGNED_PROC.VERBAL_MODE_C=ZC_VERB_ORD_MODE_proc.VERBAL_MODE_C
 LEFT OUTER JOIN ZC_VERB_ORD_MODE ZC_VERB_ORD_MODE_med ON ORDER_SIGNED_MED.VERBAL_MODE_C=ZC_VERB_ORD_MODE_med.VERBAL_MODE_C 
 LEFT OUTER JOIN CLARITY_LOC CLARITY_LOC ON CLARITY_DEP.REV_LOC_ID=CLARITY_LOC.LOC_ID 
 LEFT OUTER JOIN ZC_DEP_SPECIALTY ZC_DEP_SPECIALTY ON CLARITY_DEP.SPECIALTY_DEP_C=ZC_DEP_SPECIALTY.DEP_SPECIALTY_C
 LEFT OUTER JOIN ZC_NOTE_SER ZC_NOTE_SER ON CLARITY_SER.PROVIDER_TYPE_C=ZC_NOTE_SER.SERVICE_TYPE_C 
 LEFT OUTER JOIN CLARITY_SER_2 CLARITY_SER_2 ON CLARITY_SER.PROV_ID=CLARITY_SER_2.PROV_ID 
 LEFT OUTER JOIN ZC_LICENSE_DISPLAY ZC_LICENSE_DISPLAY ON CLARITY_SER_2.CUR_CRED_C=ZC_LICENSE_DISPLAY.LICENSE_DISPLAY_C
 
 WHERE  ORDER_METRICS.ORDER_DTTM>= @startdate AND ORDER_METRICS.ORDER_DTTM<= @enddate 
 AND ((ORDER_SIGNED_MED.LINE IS  NULL  AND ORDER_SIGNED_PROC.LINE IS  NULL ) 
 OR (ORDER_SIGNED_PROC.LINE IS  NULL  AND ORDER_SIGNED_MED.LINE=1) 
 OR (ORDER_SIGNED_MED.LINE IS  NULL  AND ORDER_SIGNED_PROC.LINE=1))
 AND (ZC_VERB_ORD_MODE_med.VERBAL_MODE_C IN ('118','1','102','101','107')
 OR  ZC_VERB_ORD_MODE_proc.VERBAL_MODE_C IN ('118','1','102','101','107'))
 AND  CLARITY_SER.PROV_ID='1862' 
 --AND CLARITY_SER.PROV_ID='1348'
 --AND (CLARITY_SER.PROV_ID='1184' 
 --OR CLARITY_SER.PROV_ID='1348' 
 --OR CLARITY_SER.PROV_ID='1862' 
 --OR CLARITY_SER.PROV_ID='3432' 
 --OR CLARITY_SER.PROV_ID='3823')

 order by CLARITY_SER.PROV_NAME

解决方案 #

1. 仅筛选符合条件的记录（当前写法可行） #

你当前在WHERE子句中的条件写法是正确的，它会直接过滤出满足任意一个VERBAL_MODE_C条件的记录。如果只是要统计这类记录的数量，直接用COUNT(*)即可，示例：

SELECT COUNT(*) AS 符合条件记录数
FROM ... -- 保留原FROM和WHERE子句内容

2. 在查询结果中标记是否符合条件（需要CASE语句） #

如果需要在SELECT结果里显示每条记录是否满足条件，就需要用CASE语句，同时要注意LEFT JOIN可能导致字段为NULL，需排除NULL干扰：

SELECT 
  CLARITY_SER.PROV_NAME, 
  CLARITY_SER.PROV_ID,
  ZC_VERB_ORD_MODE_med.NAME as VERBAL_MODE_MED,
  ZC_VERB_ORD_MODE_proc.NAME as VERBAL_MODE_PROC,
  ORDER_METRICS.ORDER_DTTM,
  ORDER_METRICS.ORDER_ID,
  ORDER_METRICS.DISPLAY_NAME,
  -- 添加标记字段
  CASE 
    WHEN ZC_VERB_ORD_MODE_med.VERBAL_MODE_C IN ('118','1','102','101','107') 
      OR ZC_VERB_ORD_MODE_proc.VERBAL_MODE_C IN ('118','1','102','101','107')
    THEN 'Yes' -- 或写1，根据需求选择
    ELSE 'No' -- 或写0
  END AS 是否符合条件
FROM ... -- 若要保留所有记录，需移除WHERE中的对应条件；若仅保留符合条件的记录，WHERE条件可保留

3. 简化条件写法 #

可以把重复的IN列表提取出来，让代码更简洁（以MySQL为例）：

AND (
  FIND_IN_SET(ZC_VERB_ORD_MODE_med.VERBAL_MODE_C, '118,1,102,101,107')
  OR FIND_IN_SET(ZC_VERB_ORD_MODE_proc.VERBAL_MODE_C, '118,1,102,101,107')
)

注：不同数据库语法有差异，比如PostgreSQL可使用= ANY(ARRAY['118','1','102','101','107'])。

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