如果存在包含和排除语句

Question

我有以下数据表：

patient_id    disease
hashA         HIV-2
hashA         COPD-1
hashA         diabetes
hashB         diabetes
hashB         FSGS
.
.
.

我想提取所有行的糖尿病患者，但不是COPD或艾滋病毒。

到目前为止，我已经：

select t.*
from patient_table t
   where exists (select 1
      from patient_table t2
      where t2.disease like '%diabetes%' and
            t2.disease not like '%HIV%' and 
            t2.disease not like '%COPD%' and
            t.patient_id = t2.patient_id
)

然而，这并不排除COPD和HIV患者的所有行。还有别的选择吗？

Answer 1

如果您正在(希望)使用MySql 8，则可以在conditional sum中使用window functions

with cte as (
    select *, Sum(case when disease like '%HIV%' or disease like '%COPD%' then 1 else 0 end) over(partition by patient_id) v
    from t
)
select patient_id, disease
from cte
where v=0

示例Fiddle

Answer 2

select t.*
from patient_table t
   where exists (select 1
      from patient_table t2
      where t2.disease like '%diabetes%')

Answer 3

看看这个，也许会有帮助：

SELECT patient_table.* 
FROM patient_table
LEFT JOIN patient_table AS not_allowed_1 
    ON not_allowed_1.patient_id = patient_table.patient_id AND not_allowed_1.disease like '%HIV%'
LEFT JOIN patient_table AS not_allowed_2 
    ON not_allowed_2.patient_id = patient_table.patient_id AND not_allowed_2.disease like '%COPD%'
WHERE patient_table.disease like '%diabetes%' 
    AND not_allowed_1.patient_id IS NULL 
    AND not_allowed_2.patient_id IS NULL 

它将左加入每一行，其中disease like '%diabetes%'两次使用相同的表在行上使用disease like '%COPD%'和disease like '%HIV%'，然后筛选左联接行有一个值。

Joining是SQL深知如何处理的东西，这比EXISTS操作符快得多。