问一句sql的写法

xiaotupansy 2011-07-12 10:19:56
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,次序随意
...全文
40 1 收藏 2
写回复
2 条回复
Zoezs 2011年07月12日
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
回复 点赞
兔子党党务院院长 2011年07月12日

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 行受影响)
回复 点赞
发动态
发帖子
疑难问题
创建于2007-09-28

9308

社区成员

12.1w+

社区内容

MS-SQL Server 疑难问题
社区公告
暂无公告