我有一个表中的以下示例数据
Id Dsc
500001 INSURED
500001 THIRD PARTY
500001 THIRD PARTY
500001 THIRD PARTY
500002 INSURED
500002 THIRD PARTY
500003 INSURED
500004 BROKER
500005 CLAIMANT
我希望提取Dsc既不是“被保险人”也不是“经纪人”的那些ID。由于这两列都有重复数据,因此我为此设计了以下查询。
Select Id from table1 where Dsc not in ('Insured', 'Broker')
Except
Select Id from table1 where Dsc in ('Insured', 'Broker')
有其他替代方法吗?
最佳答案
SELECT id FROM table1 GROUP BY id
HAVING SUM(CASE WHEN Dsc='Insured' THEN 1 ELSE 0 END)=0
AND SUM(CASE WHEN Dsc='Broker' THEN 1 ELSE 0 END)=0
关于sql - 在mysql中过滤组,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/31238821/