sql查询提取唯一记录 [英] sql query to extract unique records

问题描述

假设我的表仅包含2个字段，ID和product，并说我的结构看起来像这样

suppose my table contains only 2 fields , ID and product and say my structure looks like this

1    微软

1       microsoft

0      cisco

0      cisco

2    思科

2       cisco

3      vmware

3      vmware

0     adobe

0      adobe

0      Microsoft

0      microsoft

我需要写一个查询，列出仅具有 id = 0 的记录，

I need to write a query that lists records having id=0 but only,

0     adobe

因为miocrsoft和cisco的另外两个产品还有一个具有ID的记录，因此我可以排除它们.

because the other two products miocrsoft and cisco have one other record that does have an ID and so I can exclude them.

我希望我对Im想要实现的目标很清楚.请让我知道这是否可以在sql中完成

I hope I am clear about what Im trying to achieve. Please let me know if this can be accomplished in sql

推荐答案

是的，可以使用基本聚合来实现.请注意我如何发布消耗性数据?这是您应该做的.与创建查询相比，创建可用数据花了我更长的时间.

Yes this is possible using basic aggregation. Notice how I posted consumable data? This is something you should do. It took me far longer to create usable data than it did to write the query.

with something(Col1, Col2) as
(
    select 1, 'microsoft' union all
    select 0, 'cisco' union all
    select 2, 'cisco' union all
    select 3, 'vmware' union all
    select 0, 'adobe' union all
    select 0, 'microsoft'
)

select Col2
from something
group by Col2
having MAX(Col1) = 0

这篇关于sql查询提取唯一记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！