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',1select * from test逻辑是,如果isdefault为1,那么优先取1的记录
如果不为1,那么取0,最后取null对每个id,只有取1条记录就可以了,希望结果如下1 'bbb' 1
2 'ccc' 1/这里也可以是2 'ddd' 1, 2 'fff' 1,次序随意
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',1select * from test逻辑是,如果isdefault为1,那么优先取1的记录
如果不为1,那么取0,最后取null对每个id,只有取1条记录就可以了,希望结果如下1 'bbb' 1
2 'ccc' 1/这里也可以是2 'ddd' 1, 2 'fff' 1,次序随意
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 = 1drop table test
/****************cid addr isdefault rid
----------- ---------------------------------------------------------------------------------------------------- ----------- --------------------
1 bbb 1 1
2 fff 1 1(2 行受影响)
union all
select * from test where isdefault=0
union all
select * from test where isdefault is null
order by cid