22,302
社区成员




create table test(cid int,addr nvarchar(100) null,isdefault int null)
insert into test select 1,'aaa',null
union select 1,'bbb',1
union select 2,'ccc',1
union select 2,'ddd',1
union select 2,'eee',0
union select 2,'fff',1
go
;with AcHerat as
(
select *,rid=row_number() over (partition by cid order by (case when isdefault = 1 then 0
when isdefault = 0 then 1 else 2 end))
from test
)
select *
from AcHerat
where rid = 1
drop table test
/****************
cid addr isdefault rid
----------- ---------------------------------------------------------------------------------------------------- ----------- --------------------
1 bbb 1 1
2 fff 1 1
(2 行受影响)