• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

100分求SQL语句

chenfei00 2005-10-27 05:29:02
A表 UserID  UserName
1 User1
2 User2
3 User3
4 User4
B表 UserID Times
1 1
2 1
3 0
要实现B表中Times=1时的所有记录.要对应该A表的所有记录.
要得到以下的结果
UserID  UserName Times
1 User1 1
2 User2 1
3 User3 null
4 User4 null
如何写SQL语句
...全文
94 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
$扫地僧$ 2005-10-27
借上面的数据用一下
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
回复
QQMagicer 2005-10-27
select A.userid,A.username,B.times
from A,B
where A.userid*=B.userid
order by A.userid

回复
vivianfdlpw 2005-10-27
select A.userid
,A.username
,B.Times
from A
left join (
select * from B where Times=1
)T
on A.userid=T.userid

回复
子陌红尘 2005-10-27
--生成测试数据
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
*/
回复
子陌红尘 2005-10-27
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
回复
jixiaojie 2005-10-27

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
回复
chenfei00 2005-10-27
1.2楼的不对...得到的结果没有User3,User4的记录..
3 User3 null
4 User4 null
我要保正A表的记录都存在
回复
游戏Lan 2005-10-27
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
回复
frankwong 2005-10-27
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
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2005-10-27 05:29
社区公告
暂无公告