100分求SQL语句

[chenfei00]

A表 UserID　 UserName
1 User1
2 User2
3 User3
4 User4
B表 UserID Times
1 1
2 1
3 0
要实现B表中Times=1时的所有记录．要对应该Ａ表的所有记录．
要得到以下的结果
UserID　 UserName Times
1 User1 1
2 User2 1
3 User3 null
4 User4 null
如何写SQL语句

[frankwong]

select a.userid,a.username,b.times
from A
left join B
on a.userid=b.userid
where b.times=1
order by a.userid

[游戏Lan]

select A.userid,A.username,B.times
from A
left　outer join B
on A.userid=B.userid
where B.times=1
order by A.userid

[chenfei00]

１．２楼的不对...得到的结果没有User3,User4的记录..
3 User3 null
4 User4 null
我要保正A表的记录都存在

[jixiaojie]

create table tb1
(
userid int,
username varchar(30)
)


create table tb2
(
userid int,
times int
)


insert into tb1
select '1 ','User1' union all
select '2 ','User2' union all
select '3 ','User3' union all
select '4 ','User4'


insert into tb2
select '1','1' union all
select '2','1' union all
select '3','0'


select tb1.*,tb2.times from tb1 left join (select * from tb2 where times =1) tb2
on tb1.userid =tb2.userid

drop table tb1,tb2


1 User1 1
2 User2 1
3 User3 NULL
4 User4 NULL

[子陌红尘]

select
a.UserID,
a.UserName,
Times = case when b.Times=0 then null else b.Times end
from
A表 a
left join
B表 b
on
a.UserID = b.UserID
order by
a.UserID

[子陌红尘]

--生成测试数据
create table A(UserID int,UserName varchar(10))
insert into A select 1,'User1'
insert into A select 2,'User2'
insert into A select 3,'User3'
insert into A select 4,'User4'
create table B(UserID int,Times int)
insert into B select 1,1
insert into B select 2,1
insert into B select 3,0

--执行查询
select
a.UserID,
a.UserName,
b.Times
from
A a
left join
(select * from B where Times>0) b
on
a.UserID = b.UserID
order by
a.UserID

--输出结果
/*
UserID UserName Times
------ -------- -----
1 User1 1
2 User2 1
3 User3 NULL
4 User4 NULL
*/

[vivianfdlpw]

select A.userid
,A.username
,B.Times
from A
left join (
select * from B where Times=1
)T
on A.userid=T.userid

[QQMagicer]

select A.userid,A.username,B.times
from A,B
where A.userid*=B.userid
order by A.userid

[$扫地僧$]

借上面的数据用一下
create table tb1
(
userid int,
username varchar(30)
)


create table tb2
(
userid int,
times int
)


insert into tb1
select '1 ','User1' union all
select '2 ','User2' union all
select '3 ','User3' union all
select '4 ','User4'


insert into tb2
select '1','1' union all
select '2','1' union all
select '3','0'

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

select tb1.*, case when tb2.times<1 then Null else tb2.times end times
from tb1,tb2
where tb1.userid *=tb2.userid