请教两张表关联问题！

[jieyh]

如何根据下面两张A,B表获取如下结果集？
A表中的C1,C2都是B表中的C1的外键。

A表
c1 c2 c3
1 3 'aa'
4 8 'bb'
B表
c1
1
2
3
4
5
6
7
8
9
10
结果集
C1,C2
1 'aa'
2 'aa'
3 'aa'
4 'bb'
5 'bb'
6 'bb'
7 'bb'
8 'bb'
9 NULL
10 NULL
不知我的表达的意思大家是否理解~~~~~~~~

[w_f]

你要表述的是：
A表中，C1是下界，C2是上界吧！
然后按B表输出！

[jieyh]

正式，有没不需要游标就能实现获取我所需要的结果集啊？

[lw1a2]

select B.c1,A.c3 from A right join B on B.c1 between A.c1 and A.c2

[OracleRoob]

select b.c1, (select a.c3 from a where b.c1>= a.c1 and b.c1<=a.c2)
from b left join a on b.c1=a.c1

[OracleRoob]

select b.c1, (select a.c3 from a where b.c1>= a.c1 and b.c1<=a.c2) as c2
from b left join a on b.c1=a.c1

[WangZWang]

create table a(c1 int,c2 int,c3 varchar(8))
create table b(c1 int)
go
Insert into a
select
1 , 3 , 'aa'
union select 4 , 8 ,'bb'
go
insert into b
select 1
union select 2
union select 3
union select 4
union select 5
union select 6
union select 7
union select 8
union select 9
union select 10
go
select b.c1,a.c3 as c2
from b left join a
on b.c1 between a.c1 and a.c2

[w_f]

掉线一下这么多人了！
思路：
SELECT B.C1,A.C3
FROM B,A
WHERE B.C1>=A.C1 AND B.C1<=A.C2

[OracleRoob]

w_f() :

9 null
10 null
没有了呀!

[jin2005]

那就用right join
lw1a2(一刀) 的正解

[w_f]

修改一点才对
SELECT B.C1,A.C3
FROM B left join A on B.C1=A.C1
WHERE B.C1>=A.C1 AND B.C1<=A.C2