sql一个结果集在另一个结果集中不存在

现有SQL结果集一个如图1。我要得到另一个结果集如图2。具体下见详情

此结果为虚拟，数据有N条，我要得到的结果集为：每个cid相同的里面只要state=1的第一条数据。我现在的解决方案为

select cid from tab_tabA group by cid.然后用循环拼接SQL语句用union all拼接数据集。

select * from

(select top(1)* from tab_tabA where cid={0} and state=0 order by createtime asc) as a0

union all

select * from (select top(1)* from tab_tabA where cid={0} and state=0 order by createtime asc) as a1 ...........

图1

图2

但是

数据多了后效率会很差

 

 

结论

SELECT t.* from  tab_tabA t,(
select min(id) as min_id from tab_tabA  where state=1 group by cid) t1
where t.id =t1.min_id