求一个查询的解法
实现一个查询从数据表a见下
a(id, state)
----------
1 0
1 1
2 1
3 0
获得
1 0
2 1
3 0
就是说如果id有重，取state=0；如果没有重复，那么state=0或者=1都需要
我的做法是
select id
from a
where state=0
union
select id
from a aa
where aa.state=1 and not exists (select id from a where a.id=aa.id and a.state=0)
这里想问还有其他实现方法吗？
对自己的数学没自信，希望知道多一些解法，也许对以后的工作有帮助。谢谢啦！
[解决办法]
select id,min(state) from a group by id
[解决办法]
select * from [TA] T WHERE NOT EXISTS(SELECT 1 FROM TA WHERE T.id=ID AND t.state>[state])
[解决办法]
with cte as
(select id, state, row_number() over (order by id) as xh from a with(nolock))
select id,state,min(xh) from cte group by id,state
[解决办法]
select id,min(state) from a group by id
