两个数据库三个表的查询

[cookies10wen]

A数据库中的表Table1:

Id JobNo StyleNo ColorNo Size ProduceAmount ProduceLine
1 J01 S01 C01 D 80 2A
2 J02 S02 C02 Z 100 2B
3 J02 S02 C02 D 90 2B
4 J03 S03 C03 D 80 2B


A数据库中的表Table2:
Id JobNo StyleNo ColorNo StyleName ChineseColor ClassProperty Size1 Amount1 Size2 Amount2 Size3
1 J01 S01 C01 SN01 CH01 CP01 D XXX
2 J02 S02 C01 SN02 CH02 CP02 Z XXX D XXX
3 J03 S03 C01 SN03 CH03 CP03 D XXX


B数据库中的表Table3:
Id ProduceLine StyleNo ColorNo Size JobNo Amount
1 2A S01 C01 D J01 100
2 2B S02 C02 Z J02 120
3 2B S02 C02 D J02 100
4 2B S03 C03 D J03 100


查询结果得出:
Id JobNo StyleNo StyleName ColorNo ChineseColor ClassProperty Size Amount ProduceLine ProduceAmount
1 J01 S01 SN01 C01 CH01 CP01 D 100 2A 80
2 J02 S02 SN02 C02 CH02 CP02 Z 120 2B 100
3 J02 S02 SN02 C02 CH02 CP02 D 100 2B 90
4 J03 S03 SN03 C03 CH03 CP03 D 100 2B 80



其中关联条是JobNo、StyleNo、ColorNo、Size相等。。

[子陌红尘]

select
    a.Id,
    a.JobNo,
    a.StyleNo,
    b.StyleName,
    a.ColorNo,
    b.ChineseColor,
    b.ClassProperty,
    a.Size,
    c.Amount,c.ProduceLine,a.ProduceAmount 
from
    A..Table1 a
left join
    A..Table2 b
on
    a.JobNo=b.JobNo and a.StyleNo=b.StyleNo and a.ColorNo=b.ColorNo and (a.Size=b.Size1 or a.Size=b.Size2)
left join
    B..Table1 c
on
    a.JobNo=b.JobNo and c.StyleNo=b.StyleNo and c.ColorNo=b.ColorNo and c.Size=b.Size

[feixianxxx]

select Id , JobNo , StyleNo  ,StyleName ,ColorNo , ChineseColor ,ClassProperty  ,Size ,Amount , ProduceLine , ProduceAmount 
from table1 t1 ,table2 t2 ,table3 t3
where T1.JobNo =t2.JobNo and t1.StyleNo=t2.StyleNo and  and t2.StyleNo=t3.StyleNo
  and  t1.ColorNo=t2.ColorNo and  and t2.ColorNo=t3.ColorNo and t1.Size=t2.Size and  and t2.Size =t3.Size

[feixianxxx]

select a.Id,
    a.JobNo,
    a.StyleNo,
    b.StyleName,
    a.ColorNo,
    b.ChineseColor,
    b.ClassProperty,
    a.Size,
    c.Amount,c.ProduceLine,a.ProduceAmount 

from table1 t1 ,table2 t2 ,table3 t3
where T1.JobNo =t2.JobNo and t1.StyleNo=t2.StyleNo and  and t2.StyleNo=t3.StyleNo
  and  t1.ColorNo=t2.ColorNo and  and t2.ColorNo=t3.ColorNo and t1.Size=t2.Size and  and t2.Size =t3.Size

[Teng_s2000]

SQL2005以上

数据库.Schema.Table 其余的连接写法就如同一个数据库中一样！

[zxkid]

如果这2个数据库在同一台服务器上
可用 dbname.schemaname.tablename格式来引用

[子陌红尘]

declare @t1 Table(Id int,JobNo varchar(10),StyleNo varchar(10),ColorNo varchar(10),Size varchar(10),ProduceAmount int,ProduceLine varchar(10))    
insert into @t1 select 1,'J01','S01','C01','D',80 ,'2A'        
insert into @t1 select 2,'J02','S02','C02','Z',100,'2B'        
insert into @t1 select 3,'J02','S02','C02','D',90 ,'2B'          
insert into @t1 select 4,'J03','S03','C03','D',80 ,'2B'        

declare @t2 Table(Id int,JobNo varchar(10),StyleNo varchar(10),ColorNo varchar(10),StyleName varchar(10),ChineseColor varchar(10),ClassProperty varchar(10),Size1 varchar(10),Amount1 varchar(10),Size2 varchar(10),Amount2 varchar(10),Size3 varchar(10))
insert into @t2 select 1,'J01','S01','C01','SN01','CH01','CP01','D','XXX ',null,null ,null 
insert into @t2 select 2,'J02','S02','C02','SN02','CH02','CP02','Z','XXX ','D' ,'XXX',null 
insert into @t2 select 3,'J03','S03','C03','SN03','CH03','CP03','D','XXX ',null,null ,null

declare @t3 Table(Id int,ProduceLine varchar(10),StyleNo varchar(10),ColorNo varchar(10),Size varchar(10),JobNo varchar(10),Amount int)
insert into @t3 select 1,'2A','S01','C01','D','J01',100 
insert into @t3 select 2,'2B','S02','C02','Z','J02',120 
insert into @t3 select 3,'2B','S02','C02','D','J02',100 
insert into @t3 select 4,'2B','S03','C03','D','J03',100 

select
    a.Id,
    a.JobNo,
    a.StyleNo,
    b.StyleName,
    a.ColorNo,
    b.ChineseColor,
    b.ClassProperty,
    a.[Size],
    c.Amount,c.ProduceLine,a.ProduceAmount 
from
    @t1 a
inner join
    @t2 b
on
    a.JobNo=b.JobNo and a.StyleNo=b.StyleNo and a.ColorNo=b.ColorNo and (a.[Size]=b.Size1 or a.[Size]=b.Size2)
inner join
    @t3 c
on
    a.JobNo=c.JobNo and c.StyleNo=c.StyleNo and c.ColorNo=c.ColorNo and a.[Size]=c.[Size]

/*
Id          JobNo      StyleNo    StyleName  ColorNo    ChineseColor ClassProperty Size       Amount      ProduceLine ProduceAmount 
----------- ---------- ---------- ---------- ---------- ------------ ------------- ---------- ----------- ----------- ------------- 
1           J01        S01        SN01       C01        CH01         CP01          D          100         2A          80
2           J02        S02        SN02       C02        CH02         CP02          Z          120         2B          100
3           J02        S02        SN02       C02        CH02         CP02          D          100         2B          90
4           J03        S03        SN03       C03        CH03         CP03          D          100         2B          80
*/

[feixianxxx]

declare @t1 Table (Id int,JobNo varchar(10),StyleNo varchar(10),ColorNo varchar(10),Size varchar(10),ProduceAmount int,ProduceLine varchar(10))    
insert into @t1 select 1,'J01','S01','C01','D',80 ,'2A'        
insert into @t1 select 2,'J02','S02','C02','Z',100,'2B'        
insert into @t1 select 3,'J02','S02','C02','D',90 ,'2B'          
insert into @t1 select 4,'J03','S03','C03','D',80 ,'2B'        

declare @t2 Table(Id int,JobNo varchar(10),StyleNo varchar(10),ColorNo varchar(10),StyleName varchar(10),ChineseColor varchar(10),ClassProperty varchar(10),Size1 varchar(10),Amount1 varchar(10),Size2 varchar(10),Amount2 varchar(10),Size3 varchar(10))
insert into @t2 select 1,'J01','S01','C01','SN01','CH01','CP01','D','XXX ',null,null ,null 
insert into @t2 select 2,'J02','S02','C02','SN02','CH02','CP02','Z','XXX ','D' ,'XXX',null 
insert into @t2 select 3,'J03','S03','C03','SN03','CH03','CP03','D','XXX ',null,null ,null

declare @t3 Table(Id int,ProduceLine varchar(10),StyleNo varchar(10),ColorNo varchar(10),Size varchar(10),JobNo varchar(10),Amount int)
insert into @t3 select 1,'2A','S01','C01','D','J01',100 
insert into @t3 select 2,'2B','S02','C02','Z','J02',120 
insert into @t3 select 3,'2B','S02','C02','D','J02',100 
insert into @t3 select 4,'2B','S03','C03','D','J03',100 

select t1.Id,
    t1.JobNo,
    t1.StyleNo,
    t2.StyleName,
    t1.ColorNo,
    t2.ChineseColor,
    t2.ClassProperty,
    t1.Size,
    t3.Amount,
    t3.ProduceLine,
    t1.ProduceAmount 

from @t1 t1 ,@t2 t2 ,@t3 t3
where T1.JobNo =t2.JobNo and t1.StyleNo=t2.StyleNo and  t2.StyleNo=t3.StyleNo
  and  t1.ColorNo=t2.ColorNo and  t2.ColorNo=t3.ColorNo and t1.Size=t3.size
  /*
  1	J01	S01	SN01	C01	CH01	CP01	D	100	2A	80
2	J02	S02	SN02	C02	CH02	CP02	Z	120	2B	100
3	J02	S02	SN02	C02	CH02	CP02	D	100	2B	90
4	J03	S03	SN03	C03	CH03	CP03	D	100	2B	80
  
  
  */

[ChinaJiaBing]

join 

--同一台服务器不同的数据库查询表格式
select * from 数据库名.dbo.表名