问一句sql的写法

[xiaotupansy]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))
DROP TABLE [dbo].test
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

select * from test

逻辑是，如果isdefault为1，那么优先取1的记录
如果不为1，那么取0,最后取null

对每个id，只有取1条记录就可以了，希望结果如下

1 'bbb' 1
2 'ccc' 1/这里也可以是2 'ddd' 1, 2 'fff' 1,次序随意

[AcHerat]

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 行受影响)

[Zoezs]

select * from test where isdefault=1
union all
select * from test where isdefault=0
union all
select * from test where isdefault is null
order by cid