select - 查询重复值并根据它们选择列值

Question

我需要从表 TEST 中提取数字，该表具有两种以上的区域类型，并且将 area="three" 作为值。我应该得到结果“b”和“f”。

谢谢你。

标签： selecthiveduplicates

Answer 1

以下是查询 -

select count(*), number from <tableName> where number in (select number from <tableName> where area='three') group by number having count(*)>1;

select number from <tableName> group by number having count(*)>1 and sum(case when area='Three' then 1 else 0) = 1;