我正试图通过合并CASE
语句来合并以下用于单独报告的查询。JOIN
信息相同,但WHERE
语句的结果也不同。对于Case_Type_ID
,我需要同时等于1
和2
的所有记录。
查询如下:
查询1:
SELECT
, XXXX
, (CASE WHEN question3.option_id = 3 THEN 'Symptomatic' ELSE CASE WHEN question3.option_id = 4 THEN 'Asymptomatic' ELSE NULL END END) AS SYMPTOM_STATUS
WHERE crr.case_Type_Id = 1
GROUP BY (CASE WHEN question3.option_id = 3 THEN 'Symptomatic' ELSE CASE WHEN question3.option_id = 4 THEN 'Asymptomatic' ELSE NULL END END)
ORDER BY crr.QUARANTINE_END_DATE DESC
查询2:
SELECT
, XXX
, (CASE WHEN question3.option_id = 17 THEN 'Symptomatic' ELSE CASE WHEN question3.option_id = 18 THEN 'Asymptomatic' ELSE NULL END END) AS SYMPTOM_STATUS
WHERE crr.case_Type_Id = 2
GROUP BY (CASE WHEN question3.option_id = 17 THEN 'Symptomatic' ELSE CASE WHEN question3.option_id = 18 THEN
'Asymptomatic' ELSE NULL END END)
ORDER BY crr.QUARANTINE_END_DATE DESC"
在简化代码中没有任何其他内容的情况下,让我们专注于您的CASE表达式和问题的标题:
your CASE
(CASE WHEN question3.option_id = 3 THEN 'Symptomatic' ELSE CASE WHEN question3.option_id = 4 THEN 'Asymptomatic' ELSE NULL END END) AS SYMPTOM_STATUS
如果你这样说(可读性更强(
(CASE
WHEN question3.option_id = 3 THEN 'Symptomatic'
ELSE
CASE
WHEN question3.option_id = 4 THEN 'Asymptomatic'
ELSE NULL
END
END) as SYMPTOM_STATUS
它是完全相同的,但现在很容易看出,它可以写成一个带有两个WHEN条件的CASE表达式,并使其更加简单。CASE表示他们自己不需要bracets来发挥作用,因为他们已经有了严格的sintax,在另一边的开始和结束都有CASE。所以它可能是这样的:
CASE
WHEN question3.option_id = 3 THEN 'Symptomatic'
WHEN question3.option_id = 4 THEN 'Asymptomatic'
ELSE
NULL
END as SYMPTOM_STATUS
此外,如果不满足WHEN条件,CASE将返回Null,因此在本例中可以忽略ELSE:
CASE
WHEN question3.option_id = 3 THEN 'Symptomatic'
WHEN question3.option_id = 4 THEN 'Asymptomatic'
END as SYMPTOM_STATUS
它现在看起来还可以,简单易读。对你的第二个CASE表达式做的同样的事情看起来是这样的:
CASE
WHEN question3.option_id = 17 THEN 'Symptomatic'
WHEN question3.option_id = 18 THEN 'Asymptomatic'
END as SYMPTOM_STATUS
如果你把它和前一个结合起来,它会说,如果option_id是3或17,那么是"有症状的",如果是4或18,那么是无症状的。让我们试着这样说:
CASE
WHEN question3.option_id = 3 OR question3.option_id = 17 THEN 'Symptomatic'
WHEN question3.option_id = 4 OR question3.option_id = 18 THEN 'Asymptomatic'
END as SYMPTOM_STATUS
现在,将它们组合在一起,并在一个具有两个WHEN条件的CASE表达式中。这个应该按预期完成工作。但是(在这个示例数据中(如果我们这样说,它可能会更加简化:
CASE
WHEN question3.option_id IN(3, 17) THEN 'Symptomatic'
WHEN question3.option_id IN(4, 18) THEN 'Asymptomatic'
END as SYMPTOM_STATUS
就是这样。如果option_id为3或17,则返回值为"症状",如果option_id为4或18,则"无症状"和option_id的任何其他值都将返回Null
致问候。。。