如何在Postgresql中使用逻辑运算符实现多个if子句

Question

我有 3 个表，我必须按照以下条件检查某些记录的计数。

IF
count(T1DM DX = 0) & COUNT (T2DM DX = 0) & COUNT (T2DM MEDS >= 1) & COUNT(ABC > 6.5 OR DEF > 11.1 OR GHI > 7) >= 1 THEN "CASE"
ELSE
DO NOTHING
**OR** 
IF
count(T1DM DX = 0) & COUNT (T2DM DX >=1) & COUNT (T1DM MEDS = 0) & COUNT (T2DM MEDS = 0) & COUNT(ABC > 6.5 OR DEF > 11.1 OR GHI > 7) >= 1 THEN "CASE"
ELSE
DO NOTHING

我的创建表语句如下

CREATE TABLE DX(
DX_id  serial Primary Key
person_id NOT NULL,
condition_name VARCHAR (50) UNIQUE NOT NULL,
cond_id VARCHAR (355) UNIQUE NOT NULL,
condition_start_date TIMESTAMP NOT NULL,
condition_end_date TIMESTAMP NOT NULL
);

CREATE TABLE RX(
RX_id serial Primary Key
person_id NOT NULL,
presciption_name VARCHAR (50) UNIQUE NOT NULL,
presc_id VARCHAR (355) UNIQUE NOT NULL,
pres_start_date TIMESTAMP NOT NULL,
pres_end_date TIMESTAMP 
);

CREATE TABLE Lab(
Lab_id serial Primary Key
person_id NOT NULL,
Test_name VARCHAR (50) UNIQUE NOT NULL,
Test_id VARCHAR (355) UNIQUE NOT NULL,
Test_start_date TIMESTAMP NOT NULL,
Test_value FLOAT
);

这就是我尝试过的

IF ((select count(*) from public.DX where cond_id = 21) == 0 AND (select count(*) from public.DX where cond_id = 23) == 0 AND (select count(*) from public.RX where presc_id = 33) >=1
AND (select count(*) from (select * from public.Lab where test_id in (41,42,43)) where test_value > 6.5)) THEN "FOUND"
ELSE
DO NOTHING

但这会引发错误。谁能帮我实现多个 if 子句，并在它们之间使用 AND/OR 逻辑运算符？

注意事项

1(DX表可以有多个条件。根据流程图，我们需要寻找T1DM和T2DM。我们可以使用cond_ids(T1DM - 21，T2DM - 23(来识别它们

2(RX表可以有多个处方药。根据流程图，我们需要寻找T1DM药物和T2DM药物。我们可以使用presc_ids来识别它们(T1DM药物-31，T2DM药物-33(

3(实验台可以有多个测量/测试结果。根据流程图，异常实验室的定义是针对下面列出的 3 个测量中的任何一个的阈值违规。

a) Three measurements - ABC, DEF, GHI. Test_ids for these three are 41,42,43
b) ABC > 6.5
c) DEF > 11.1
d) GHI > 7

Answer 1

您可以使用CASE表达式和存在逻辑来编写此内容：

CASE WHEN
NOT EXISTS (SELECT 1 FROM public.DX WHERE cond_id IN (21, 23)) AND
EXISTS (SELECT 1 FROM public.DX WHERE presc_id = 33) AND
EXISTS (SELECT 1 FROM public.Lab WHERE test_id IN (41,42,43) AND test_value > 6.5)
THEN 'FOUND' END;