第一个表: table1
xh jc1 jc2 jc3 ysbh
1 aa bb cc 0001
第二个表: table2
xh jc4 jc5 jc6 ysbh
1 a b c 0002 第三个是总表,关联表一和表二 table3
xh xm
1 测试姓名
ysbh相关联的表是: table4
ysbh ysxm
0001 张三
0002 李四
我想要的结果是
xh xm jc1 jc2 jc2 ysxm1 jc4 jc5 jc6 ysxm2
1 测试姓名 aa bb cc 张三 a b c 李四用left 语句怎么写?我自己写法是:
select xh,xm from table3
left join table1 on table3.xh=table1.xh
left join table2 on table3.xh=table2.xh
但是,得出的,不是我想要的,
我不知道,怎么把table4的ysxm字段加进去!
xh jc1 jc2 jc3 ysbh
1 aa bb cc 0001
第二个表: table2
xh jc4 jc5 jc6 ysbh
1 a b c 0002 第三个是总表,关联表一和表二 table3
xh xm
1 测试姓名
ysbh相关联的表是: table4
ysbh ysxm
0001 张三
0002 李四
我想要的结果是
xh xm jc1 jc2 jc2 ysxm1 jc4 jc5 jc6 ysxm2
1 测试姓名 aa bb cc 张三 a b c 李四用left 语句怎么写?我自己写法是:
select xh,xm from table3
left join table1 on table3.xh=table1.xh
left join table2 on table3.xh=table2.xh
但是,得出的,不是我想要的,
我不知道,怎么把table4的ysxm字段加进去!
left join table1 on table3.xh=table1.xh
left join table2 on table3.xh=table2.xh
left join table4 t4 on table3.ysbh=table1.ysbh
left join table4 t5 on table4.ysbh=table2.ysbh
-- Author: T.O.P
-- Create date: 2009/11/26
-- Version: SQL SERVER 2005
-- =============================================
declare @TB1 table([xh] int,[jc1] varchar(2),[jc2] varchar(2),[jc3] varchar(2),[ysbh] varchar(4))
insert @TB1
select 1,'aa','bb','cc','0001'declare @TB2 table([xh] int,[jc4] varchar(1),[jc5] varchar(1),[jc6] varchar(1),[ysbh] varchar(4))
insert @TB2
select 1,'a','b','c','0002'declare @TB3 table([xh] int,[xm] varchar(8))
insert @TB3
select 1,'测试姓名' declare @TB4 table([ysbh] varchar(4),[ysxm] varchar(4))
insert @TB4
select '0001','张三' union all
select '0002','李四'select c.xh, c.xm, a.jc1, a.jc2, a.jc3, (select [ysxm] from @tb4 where ysbh=a.ysbh) as [ysxm1],
b.jc4, b.jc5, b.jc6, (select [ysxm] from @tb4 where ysbh=b.ysbh) as [ysxm2]
from @tb1 a, @tb2 b, @tb3 c
where a.xh = b.xh and a.xh = c.xh
--测试结果:
/*
xh xm jc1 jc2 jc3 ysxm1 jc4 jc5 jc6 ysxm2
----------- -------- ---- ---- ---- ----- ---- ---- ---- -----
1 测试姓名 aa bb cc 张三 a b c 李四(1 row(s) affected)
*/
declare @table1 table (xh int,jc1 varchar(2),jc2 varchar(2),jc3 varchar(2),ysbh varchar(4))
insert into @table1
select 1,'aa','bb','cc','0001'
--> 测试数据: @table2
declare @table2 table (xh int,jc4 varchar(1),jc5 varchar(1),jc6 varchar(1),ysbh varchar(4))
insert into @table2
select 1,'a','b','c','0002'
--> 测试数据: @table3
declare @table3 table (xh int,xm varchar(8))
insert into @table3
select 1,'测试姓名'
--> 测试数据: @table4
declare @table4 table (ysbh varchar(4),ysxm varchar(4))
insert into @table4
select '0001','张三' union all
select '0002','李四'select c.xh, c.xm,
jc1,jc2,jc2,ysxm1=d.ysxm,
jc4,jc5,jc6,ysxm2=e.ysxm
from @table3 c
join @table1 a on a.xh=c.xh
join @table2 b on b.xh=c.xh
join @table4 d on d.ysbh=a.ysbh
join @table4 e on e.ysbh=b.ysbh
xh xm jc1 jc2 jc2 ysxm1 jc4 jc5 jc6 ysxm2
----------- -------- ---- ---- ---- ----- ---- ---- ---- -----
1 测试姓名 aa bb bb 张三 a b c 李四(1 行受影响)
select
t1.xh,t1.jc1,t1.jc2,t1.jc3,
t3.ysxm,
t2.jc4,t2.jc5,t2.jc6,
t4.ysxm
from
table1 t1 join table2 on t1.xh=t2.xh
left join
table4 t3 on t3.ysbh=t1.ysbh
left join
table3 t4 on t4.ysbh=t2.ysbh
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-26 13:43:58
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[table1]
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([xh] int,[jc1] varchar(2),[jc2] varchar(2),[jc3] varchar(2),[ysbh] varchar(4))
insert [table1]
select 1,'aa','bb','cc','0001'
--> 测试数据:[table2]
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([xh] int,[jc4] varchar(1),[jc5] varchar(1),[jc6] varchar(1),[ysbh] varchar(4))
insert [table2]
select 1,'a','b','c','0002'
--> 测试数据:[table3]
if object_id('[table3]') is not null drop table [table3]
go
create table [table3]([xh] int,[xm] varchar(8))
insert [table3]
select 1,'测试姓名'
--> 测试数据:[table4]
if object_id('[table4]') is not null drop table [table4]
go
create table [table4]([ysbh] varchar(4),[ysxm] varchar(4))
insert [table4]
select '0001','张三' union all
select '0002','李四'
--------------开始查询--------------------------
select
t3.*,t1.jc1,t1.jc2,t1.jc3,t4.ysbh, t2.jc4,t2.jc5,t2.jc6,t5.ysbh
from
table3 t3
left join
table1 t1 on t3.xh=t1.xh
left join
table2 t2 on t3.xh=t2.xh
left join
table4 t4 on t4.ysbh=t1.ysbh
left join
table4 t5 on t5.ysbh=t2.ysbh
----------------结果----------------------------
/* xh xm jc1 jc2 jc3 ysbh jc4 jc5 jc6 ysbh
----------- -------- ---- ---- ---- ---- ---- ---- ---- ----
1 测试姓名 aa bb cc 0001 a b c 0002(1 行受影响)
*/