无字段关联的情况下如何关联

现在有两张数据表,一张a,一张是b,两张表找不到关联关系的字段
a表结构
编码 内容
q nr1
w nr2
e nr3
r nr4
-------------
b表结构
ID ADD ext1 ext2 ext3
1 badd1 bext1 bext1 bext1
2 badd2 bext2 bext2 bext2
3 badd3 bext3 bext3 bext3
4 badd1 bext4 bext4 bext4
5 badd1 bext5 bext5 bext5
------------------
c表结构
ID ADD ext1 ext2 ext3
1 cadd1 cext1 cext1 cext1
2 cadd2 cext2 cext2 cext2
3 cadd3 cext3 cext3 cext3
4 cadd1 cext4 cext4 cext4
5 cadd1 cext5 cext5 cext5
现在想用a表中的编码与b表进行关联,用什么方法?
-----------------
效果
ID ADD ext1 ext2 ext3 编码
1 badd1 bext1 bext1 bext1 q
2 badd2 bext2 bext2 bext2 q
3 badd3 bext3 bext3 bext3 q
4 badd1 bext4 bext4 bext4 q
5 badd1 bext5 bext5 bext5 q
1 cadd1 cext1 cext1 cext1 w
2 cadd2 cext2 cext2 cext2 w
3 cadd3 cext3 cext3 cext3 w
4 cadd1 cext4 cext4 cext4 w
5 cadd1 cext5 cext5 cext5 w

作者: stmn1320   发布时间: 2011-06-13

cross join

作者: jinfengyiye   发布时间: 2011-06-13

SQL code
create table a(id varchar(10),ext varchar(10))
insert into a select 'q','nr1'
insert into a select 'w','nr2'
insert into a select 'e','nr3'
insert into a select 'r','nr4'
create table b(ID int,[ADD] varchar(10),ext1 varchar(10),ext2 varchar(10),ext3 varchar(10))
insert into b select 1,'badd1','bext1','bext1','bext1'
insert into b select 2,'badd2','bext2','bext2','bext2'
insert into b select 3,'badd3','bext3','bext3','bext3'
insert into b select 4,'badd1','bext4','bext4','bext4'
insert into b select 5,'badd1','bext5','bext5','bext5'
create table c(ID int,[ADD] varchar(10),ext1 varchar(10),ext2 varchar(10),ext3 varchar(10))
insert into c select 1,'cadd1','cext1','cext1','cext1'
insert into c select 2,'cadd2','cext2','cext2','cext2'
insert into c select 3,'cadd3','cext3','cext3','cext3'
insert into c select 4,'cadd1','cext4','cext4','cext4'
insert into c select 5,'cadd1','cext5','cext5','cext5'
go
select b.*,a.id as 编码 from a,b where a.id='q'
union all
select c.*,a.id as 编码 from a,c where a.id='w'
go
drop table a,b,c
/*
ID          ADD        ext1       ext2       ext3       编码
----------- ---------- ---------- ---------- ---------- ----------
1           badd1      bext1      bext1      bext1      q
2           badd2      bext2      bext2      bext2      q
3           badd3      bext3      bext3      bext3      q
4           badd1      bext4      bext4      bext4      q
5           badd1      bext5      bext5      bext5      q
1           cadd1      cext1      cext1      cext1      w
2           cadd2      cext2      cext2      cext2      w
3           cadd3      cext3      cext3      cext3      w
4           cadd1      cext4      cext4      cext4      w
5           cadd1      cext5      cext5      cext5      w

(10 行受影响)

*/

作者: qianjin036a   发布时间: 2011-06-13

SQL code
select ID ADD ext1 ext2 ext3,'q' as 编码 from b表 
union all
select ID ADD ext1 ext2 ext3,'w' from c表 

作者: maco_wang   发布时间: 2011-06-13