如何在两种不同的条件下排除

Question

不知道是否有人知道如何做到这一点？

基本上，我只需要返回Type等于"I"的结果，所以我删除了行----AND PatientType="I"----这就像一个符咒，但后来我发现，我有一些MRN相同的记录，其中包含"I"one_answers"O"两种类型。如果它们有"O"，那么我就无法计算它们的"I"。现在这意味着我必须删除那些符合条件的记录。是的，你猜对了，我又被卡住了。哈哈

  SELECT DISTINCT 
       p.id, 
       v.PatientID,
       p.firstname, 
       p.lastname, 
       p.dob, 
       p.mrn, 
       s.SmokeStatus,  
       v.VisitNo, 
       s.VisitID,
       v.ID,
   v.AdmitedDate
   FROM
       tblPatient p 
       JOIN tblPatientVisit v ON p.id = v.PatientID
       JOIN tblPatientSmokingScreenOrder s ON v.id = s.VisitID
   WHERE 
       isdate(p.DOB) = 1 
       AND CONVERT(date,p.DOB) <'12/10/2000' 
       AND isdate(v.AdmitedDate) = 1 
       AND CONVERT(date,v.AdmitedDate) > '06/16/2013 00:00' 
   AND v.PatientType = 'I'
   AND s.TobaccoType = 'Cigarettes' OR s.TobaccoType='Cigars' or s.TobaccoType='Pipes'
   AND v.PatientType !='O'
   AND v.PatientType !='2'
   AND v.PatientType = 'I'
   order by MRN

Answer 1

添加括号，因为OR和and之间存在运算符优先级问题。

AND (s.TobaccoType = 'Cigarettes' OR s.TobaccoType='Cigars' or s.TobaccoType='Pipes')

或者更好，IN子句

AND s.TobaccoType IN ('Cigarettes', 'Cigars', 'Pipes')

然后，添加NOT EXISTS条款（如果我理解得很好）以排除"任何就诊中出现O"的患者

AND NOT EXISTS (select null from
                tblPatientVisit 
                where PatientId = p.id
                and PatientType = 'O')

因此整个查询应该是（不确定v.PatientType != '2'应该去哪里）

SELECT DISTINCT 
       p.id, 
       v.PatientID,
       p.firstname, 
       p.lastname, 
       p.dob, 
       p.mrn, 
       s.SmokeStatus,  
       v.VisitNo, 
       s.VisitID,
       v.ID,
       v.AdmitedDate
   FROM
       tblPatient p 
       JOIN tblPatientVisit v ON p.id = v.PatientID
       JOIN tblPatientSmokingScreenOrder s ON v.id = s.VisitID
   WHERE 
       isdate(p.DOB) = 1 
       AND CONVERT(date,p.DOB) <'12/10/2000' 
       AND isdate(v.AdmitedDate) = 1 
       AND CONVERT(date,v.AdmitedDate) > '06/16/2013 00:00' 
       AND v.PatientType = 'I'
       AND v.PatientType !='2'
       AND s.TobaccoType IN ('Cigarettes', 'Cigars', 'Pipes')
       AND NOT EXISTS (select null from
                    tblPatientVisit 
                    where PatientId = p.id
                    and PatientType = 'O')