急求多表联合语句

[YewPu]

表1、client_info
字段client_no
client_name
表2、shipment
字段,shipment_no
ddate
client_no
bar_code
goods_model
quantity
price
totomoney
表3、unshipment
字段,unshipment_no
ddate
client_no
bar_code
goods_model
quantity
price
totomoney
表4、gathering
字段,odd_no
ddate
client_no
totomoney
要求如下

运用多表联合语句查询出如下样式的联合
client_no client_name ddate shipment_no(或是unshipment或是odd_no) bar_code .....totmoney
必须是横向的排序，每条的明细表

谢谢！急

[让你望见影子的墙]

select * 
from client_info a,shipment b,unshipment  c,gathering d
where a.client_no =b.client_no 
     and a.client_no =c.client_no 
     and a.client_no =d.client_no 

 

[gsk09]

--> Test data : @client_info
declare @client_info table ([client_no] int,[client_name] varchar(12))
insert into @client_info
select 1,'client_name1'
--> Test data : @shipment
declare @shipment table ([shipment_no] varchar(9),[ddate] datetime,[client_no] int,[bar_code] varchar(9),[goods_model] varchar(12),[quantity] int,[price] int,[totomoney] int)
insert into @shipment
select 'shipment1','20091010',1,'bar_code1','goods_model1',1,10000,10000
--> Test data : @unshipment
declare @unshipment table ([unshipment_no] varchar(11),[ddate] datetime,[client_no] int,[bar_code] varchar(9),[goods_model] varchar(12),[quantity] int,[price] int,[totomoney] int)
insert into @unshipment
select 'unshipment1','20091011',1,'bar_code1','goods_model1',2,20000,20000
--> Test data : @gathering
declare @gathering table ([odd_no] varchar(7),[ddate] datetime,[client_no] int,[totomoney] int)
insert into @gathering
select 'odd_no1','20091012',1,30000

--client_no  client_name    ddate  shipment_no(或是unshipment或是odd_no) bar_code .....totmoney 
select c.*,t.ddate
	,t.shipment_no,t.bar_code,t.goods_model,t.quantity,t.price,t.totomoney 
from @client_info c,@shipment t where c.client_no = t.client_no
union all
select c.*,t.ddate
	,t.unshipment_no,t.bar_code,t.goods_model,t.quantity,t.price,t.totomoney 
from @client_info c,@unshipment t where c.client_no = t.client_no
union all
select c.*,t.ddate
	,t.odd_no,null,null,null,null,t.totomoney 
from @client_info c,@gathering t where c.client_no = t.client_no
order by client_no,ddate

client_no   client_name  ddate       shipment_no bar_code  goods_model  quantity  price  totomoney
----------- ------------ ----------- ----------- --------- ------------ --------- ------ ---------
1           client_name1 2009-10-10  shipment1   bar_code1 goods_model1 1         10000  10000
1           client_name1 2009-10-11  unshipment1 bar_code1 goods_model1 2         20000  20000
1           client_name1 2009-10-12  odd_no1     NULL      NULL         NULL      NULL   30000

[SQL77]

SELECT A.*,B.*,C.*,D.* FROM 
client_info A,shipment_no B,
unshipment C,gathering D
WHERE A.client_no=B.client_no AND 
A.client_no=C.client_no AND A.client_no=D.client_no

[xiequan2]

SELECT A.*,B.*,C.*,D.* FROM client_info A,shipment_no B, unshipment C,gathering D WHERE A.client_no=B.client_no AND A.client_no=C.client_no AND A.client_no=D.client_no

混分

[--小F--]

select 
  * 
from 
  client_info A,shipment_no B,unshipment C,gathering D
where 
  A.client_no=B.client_no 
and 
  A.client_no=C.client_no 
and 
  A.client_no=D.client_no

[zhangjiang264]

select A.*,B.*,C.*,D.*
from client_info A inner join 
shipment B on 
A.client_no=B.client_no 
inner join unshipment C
A.client_no=C.client_no 
inner join gathering D
A.client_no=D.client_no 

[zxj828282]

jstl

[fhjzgsy]

根据主外键内连不就好了