表A
ID Name Age DeptChina DeptEng
1 A 100 1 2
2 B 100 3 4
表B
ID Dept Team
1 China1 China1
2 Eng1 Eng1
3 China2 China2
4 Eng2 Eng2请问怎么把A表中的DeptChina,DeptEng转换成B表中对应的Dept
本想left outer join B表2次,但是会出现4条记录,而不是2条
ID Name Age DeptChina DeptEng
1 A 100 1 2
2 B 100 3 4
表B
ID Dept Team
1 China1 China1
2 Eng1 Eng1
3 China2 China2
4 Eng2 Eng2请问怎么把A表中的DeptChina,DeptEng转换成B表中对应的Dept
本想left outer join B表2次,但是会出现4条记录,而不是2条
from a,b c,b d
where a.id=c.id and a.id=d.id
insert into A
select 1,'A',100,1,2
union select 2,'B',100,3,4create table B(ID int,Dept varchar(8),Team varchar(8))
insert into b
select 1,'China1','China1'
union select 2,'Eng1','Eng1'
union select 3,'China2','China2'
union select 4,'Eng2','Eng2'select * from a
select * from b
select a.id,a.name,a.age,c.dept as deptchina,d.dept as depteng
from a,b c,b d
where a.id=c.id and a.id=d.id
drop table a,b;
create table A(ID int,Name varchar(8),Age int,DeptChina int,DeptEng int)
insert into A
select 1,'A',100,1,2
union select 2,'B',100,3,4create table B(ID int,Dept varchar(8),Team varchar(8))
insert into b
select 1,'China1','China1'
union select 2,'Eng1','Eng1'
union select 3,'China2','China2'
union select 4,'Eng2','Eng2'select * from a
select * from b
select a.id,a.name,a.age,c.dept as deptchina,d.dept as depteng
from a,b c,b d
where a.deptchina=c.id and a.depteng=d.id
drop table a,b;(2 行受影响)(4 行受影响)
ID Name Age DeptChina DeptEng
----------- -------- ----------- ----------- -----------
1 A 100 1 2
2 B 100 3 4(2 行受影响)ID Dept Team
----------- -------- --------
1 China1 China1
2 Eng1 Eng1
3 China2 China2
4 Eng2 Eng2(4 行受影响)id name age deptchina depteng
----------- -------- ----------- --------- --------
1 A 100 China1 Eng1
2 B 100 China2 Eng2(2 行受影响)
insert into a values(1, 'A', 100, 1, 2 )
insert into a values(2, 'B', 100, 3, 4 )
create table b(ID int, Dept varchar(10), Team varchar(10))
insert into b values(1, 'China1', 'China1' )
insert into b values(2, 'Eng1 ' , 'Eng1' )
insert into b values(3, 'China2', 'China2' )
insert into b values(4, 'Eng2 ' , 'Eng2' )
goselect a.id , a.name ,a.age , b.dept deptchina , depteng = (select b.team from b where b.id = a.depteng) from a , b where a.deptchina = b.id drop table a,b/*
id name age deptchina depteng
----------- ---------- ----------- ---------- ----------
1 A 100 China1 Eng1
2 B 100 China2 Eng2(所影响的行数为 2 行)
*/
刚测过!