选择id（在本例中为dupersid），其中第二列（在本例中为icd9codx）的值为（296,311）且仅为250

Selecting id (in this case dupersid) where value from the second column (in this case icd9codx) is in (296, 311) and 250 only.

选择id(在本例中为dupersid),其中第二列(在本例中为icd9codx)的值在(296,311)和250中。

---

| dupersid  | icd9codx
---------------------
46166101    -9
46166101    250
46166101    272
46166101    272
46166101    311
46166101    401
46166101    460
46166101    701
46166101    715
46166101    719
46166101    780
46166102    250
46166102    311
46166103    250
46166103    296

Expected Result

----------------------
dupersid    icd9codx
---------------------
46166102    250
46166102    311
46166103    250
46166103    296

I tried

SELECT dupersid, icd9codx FROM public.sample_test
WHERE icd9codx = 250 AND dupersid IN (select distinct dupersid from public.sample_test where icd9codx IN (296, 311));

but it also gives records like 46166101

但它也给出了像46166101这样的记录

1 个解决方案

#1

You can do this with trick of summing the powers of 2:

你可以通过总结2的幂的技巧来做到这一点:

SELECT * 
FROM public.sample_test 
WHERE dupersid IN(SELECT dupersid FROM public.sample_test
                  GROUP BY dupersid
                  HAVING SUM(CASE icd9codx WHEN 250 THEN 1
                                           WHEN 296 THEN 2 
                                           WHEN 311 THEN 4
                                           ELSE 8 END) IN (3, 5) AND
                         SUM(CASE WHEN icd9codx = 250 THEN 1  
                                  ELSE 0 END) = 1)