请教各位一条查询语句问题，TY！

[xiaoyu3362]

id num
1 1
2 2
3 3
select * from XX where num >1 and num <3
结果是
id num
2 2

查询语句如何修改能让id 1,3 同样显示在查询结果中并且num字段为0
想要的结果是这样的
id num
1 0
2 2
3 0

请各位大侠帮忙！

[bdmh]

select * from xx as a left join xx as b on a.id=b.id where b.num>1 and b.num<3

未经测试

[xiaoyu3362]

[Quote=引用 1 楼 的回复:]
select * from xx as a left join xx as b on a.id=b.id where b.num>1 and b.num<3

未经测试
[/Quote]

您的方法我测试了一下 查询结果是
id num id num
2 2 2 2

[China_yuanli]

select id ,(case when num >1 and num <3 then 0 else num end) from XX

[China_yuanli]

[Quote=引用 3 楼 的回复:]
select id ,(case when num >1 and num <3 then 0 else num end) from XX
[/Quote]

额 写反了
select id ,(case when num >1 and num <3 then num else 0 end) from XX

[SqlServer2008]

declare @tab table
(
	id int,
	num int
)

insert into @tab(id,num) 
select 1,1 
union all
select 2,2
union all
select 3,3

select id,num=isnull((select num from @tab where id=t.id and num>1 and num<3),0) from @tab t

[xiaoyu3362]

[Quote=引用 5 楼 的回复:]
SQL code

declare @tab table
(
id int,
num int
)

insert into @tab(id,num)
select 1,1
union all
select 2,2
union all
select 3,3

select id,num=isnull((select num from @tab whe……
[/Quote]

您的方法可行，谢谢指教~

[rhj_koko]

select * from (select id,num from num where id>1 and id<3
union all
select id,'0' from num where id<=1 or id>=3) as a order by id

[SqlServer2008]

[Quote=引用 6 楼 的回复:]

引用 5 楼 的回复:
SQL code

declare @tab table
(
id int,
num int
)

insert into @tab(id,num)
select 1,1
union all
select 2,2
union all
select 3,3

select id,num=isnull((select num from @ta……
[/Quote]


case when then 也可以的。

select id,num=case when num>1 and num<3 then num else 0 end from @tab

[小龙卷卷风]

declare @tab table
(
id int,
num int
)

insert into @tab(id,num)
select 1,1
union all
select 2,2
union all
select 3,3

select id,num=isnull((select num from @tab where id=t.id and num>1 and num<3),0) from @tab t

[xiaoyu3362]

问题已经解决了 谢谢各位大侠的帮助~ 感激不尽~