如何跨同一列的多行选择具有条件的单个表

I have a table that includes columns for medical numbers and procedures. There are a lot of rows and medical numbers can be repeated with many procedures; for example:

我有一个表格，其中包括医疗号码和程序的列。有许多行和医疗号码可以重复与许多程序;例如:

Mid_no    procedure
--------------------
100.          20
100.          30
200.          30

I want to select all mid_no that have procedure 30 and do not have procedure 20. In the above example, the desired result would be:

我想要选择所有mid_no，其中包含过程30，而没有过程20。在上面的例子中，期望的结果是:

Mid_no.    Procedure
---------------------
200.        30

3 个解决方案

#1

SELECT t.mid_no,
  t.procedure
FROM TABLE t
WHERE NOT EXISTS
  (SELECT 1 FROM TABLE t1 WHERE t1.mid_no = t.mid_no AND t1.procedure = 20
  )
AND t.procedure = 30;

#2

select mid_no from table where procedure = 30
intersect
select mid_no from table where procedure != 20

#3

With a single scan of your table, you can use:

只需扫描一下您的表，就可以使用:

select distinct Mid_no
from (
        select count(case when procedure=20 then 1 end ) over ( partition by Mid_no) as has20,
               count(case when procedure=30 then 1 end ) over ( partition by Mid_no) as has30,
               Mid_no
        from yourTable
     )
where has20 = 0 
  and has30 != 0

The internal query counts the number of occurrences of 20 and 30 in two different values, for each Mid_no; the external one simply picks the only records with no occurrence of 20 and at least one occurrenxe of 30.

内部查询计算每个Mid_no在两个不同值中出现20和30的次数;外部的一个简单地选择唯一的记录，没有出现20，至少一个发生在30。