第一个表:
jc1 jc2 jc3 ysbh
2.2 2.3 2.5 0001
第二个表:
jc4 jc5 jc6 ysbh
a b c 0002
ysbh相关联的表是:
ysbh ysxm
0001 张三
0002 李四
如何得出关联结果:
jc1 jc2 jc3 jc4 jc5 jc6 ysbh1 ysbh2
2.2 2.3 2.5 a b c 张三 李四
jc1 jc2 jc3 ysbh
2.2 2.3 2.5 0001
第二个表:
jc4 jc5 jc6 ysbh
a b c 0002
ysbh相关联的表是:
ysbh ysxm
0001 张三
0002 李四
如何得出关联结果:
jc1 jc2 jc3 jc4 jc5 jc6 ysbh1 ysbh2
2.2 2.3 2.5 a b c 张三 李四
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-25 19:40:41
-- 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.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([jc1] numeric(2,1),[jc2] numeric(2,1),[jc3] numeric(2,1),[ysbh] varchar(4))
insert [a]
select 2.2,2.3,2.5,'0001'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([jc4] varchar(1),[jc5] varchar(1),[jc6] varchar(1),[ysbh] varchar(4))
insert [b]
select 'a','b','c','0002'
--> 测试数据:[c]
if object_id('[c]') is not null drop table [c]
go
create table [c]([ysbh] varchar(4),[ysxm] varchar(4))
insert [c]
select '0001','张三' union all
select '0002','李四'
--------------开始查询--------------------------
select
b.jc4,b.jc5,b.jc6,a.jc1,a.jc2,a.jc3
from
a,b,c
where
a.ysbh=c.ysbh
----------------结果----------------------------
/* jc4 jc5 jc6 jc1 jc2 jc3
---- ---- ---- --------------------------------------- --------------------------------------- ---------------------------------------
a b c 2.2 2.3 2.5(1 行受影响)
*/
declare @table1 table([jc1] numeric(2,1),[jc2] numeric(2,1),[jc3] numeric(2,1),[ysbh] varchar(4))
insert @table1
select 2.2,2.3,2.5,'0001'
--> 测试数据:@table2
declare @table2 table([jc4] varchar(1),[jc5] varchar(1),[jc6] varchar(1),[ysbh] varchar(4))
insert @table2
select 'a','b','c','0002'
--> 测试数据:@table3
declare @table3 table([ysbh] varchar(4),[ysxm] varchar(4))
insert @table3
select '0001','张三' union all
select '0002','李四'
select t.jc1,t.jc2,t.jc3,h.jc4,h.jc5,h.jc6,t.ysxm as ysbh1,h.ysxm as ysbh2
from
(select a.jc1,a.jc2,a.jc3,b.ysxm from @table1 a join @table3 b
on a.ysbh = b.ysbh) t ,
(select c.jc4,c.jc5,c.jc6,d.ysxm from @table2 c join @table3 d
on c.ysbh = d.ysbh) h--结果
-----------------
2.2 2.3 2.5 a b c 张三 李四
(select t1.* , px = (select count(1) from (select m.jc1,m.jc2,m.jc3,n.ysbh from [第一个表] m , [ysbh相关联的表] n where m.ysbh = n.ysbh) t2 where t2.ysbh < t1.ysbh) + 1 from
(select m.jc1,m.jc2,m.jc3,n.ysbh from [第一个表] m , [ysbh相关联的表] n where m.ysbh = n.ysbh) t1) q1
full join
(select t1.* , px = (select count(1) from (select m.jc4,m.jc5,m.jc6,n.ysbh from [第一个表] m , [ysbh相关联的表] n where m.ysbh = n.ysbh) t2 where t2.ysbh < t1.ysbh) + 1 from
(select m.jc4,m.jc5,m.jc6,n.ysbh from [第二个表] m , [ysbh相关联的表] n where m.ysbh = n.ysbh) t1) q2
on q1.px = q2.px
insert [a]
select 2.2,2.3,2.5,'0001'
--> 测试数据:[b]
create table [b]([jc4] varchar(1),[jc5] varchar(1),[jc6] varchar(1),[ysbh] varchar(4))
insert [b]
select 'a','b','c','0002'
create table [c]([ysbh] varchar(4),[ysxm] varchar(4))
insert [c]
select '0001','张三' union all
select '0002','李四'
goselect q1.jc1,q1.jc2,q1.jc3,q2.jc4,q2.jc5,q2.jc6,q1.ysbh ysbh1,q2.ysbh ysbh2 from
(select t1.* , px = (select count(1) from (select m.jc1,m.jc2,m.jc3,n.ysxm ysbh from a m , c n where m.ysbh = n.ysbh) t2 where t2.ysbh < t1.ysbh) + 1 from
(select m.jc1,m.jc2,m.jc3,n.ysxm ysbh from a m , c n where m.ysbh = n.ysbh) t1) q1
full join
(select t1.* , px = (select count(1) from (select m.jc4,m.jc5,m.jc6,n.ysxm ysbh from b m , c n where m.ysbh = n.ysbh) t2 where t2.ysbh < t1.ysbh) + 1 from
(select m.jc4,m.jc5,m.jc6,n.ysxm ysbh from b m , c n where m.ysbh = n.ysbh) t1) q2
on q1.px = q2.pxdrop table a , b, c/*
jc1 jc2 jc3 jc4 jc5 jc6 ysbh1 ysbh2
---- ---- ---- ---- ---- ---- ----- -----
2.2 2.3 2.5 a b c 张三 李四(所影响的行数为 1 行)
*/
select
t1.jc1,
t1.jc2,
t1.jc3,
t2.jc4,
t2.jc5,
t2.jc6,
(select t3.ysxm from table3 t3 where t3.ysbh = t1.ysbh) as ysbh1,
(select t3.ysxm from table3 t3 where t3.ysbh = t2.ysbh) as ysbh2
from table1 t1, table2 t2
是没有关联 想不出来能用在什么地方
declare @tb1 table (jc1 nvarchar(5),jc2 nvarchar(5),jc3 nvarchar(5),ysbh nvarchar(10))
insert into @tb1 select '2.2','2.3','2.5','0001'
declare @tb2 table (jc4 nvarchar(10),jc5 nvarchar(10),jc6 nvarchar(10),ysbh nvarchar(10))
insert into @tb2 select 'a','b','c','0002'
declare @tb3 table (ysbh nvarchar(10),ysxm nvarchar(10))
insert into @tb3 select '0001','张三'
union all select '0002','李四'
select jc1,jc2,jc3,jc4,jc5,jc6,a.ysxm,b.ysxm from
(select jc1,jc2,jc3,a.ysxm from @tb3 a join @tb1 b on a.ysbh=b.ysbh) A,
(select jc4,jc5,jc6,a.ysxm from @tb3 a join @tb2 c on a.ysbh=c.ysbh) B
/*
(1 行受影响)(1 行受影响)(2 行受影响)
jc1 jc2 jc3 jc4 jc5 jc6 ysxm ysxm
----- ----- ----- ---------- ---------- ---------- ---------- ----------
2.2 2.3 2.5 a b c 张三 李四(1 行受影响)*/
create table a2(jc4 varchar(4),jc5 varchar(4),jc6 varchar(4),ysbh varchar(4))
insert into a1 select 2.2,2.3,2.5,'0001'
insert into a2 select 'a','b','c','0002'
select a1.*,a2.* from a1 inner join a2 on 1=1
go
drop table a1
drop table a2
declare @tb1 table([jc1] numeric(2,1),[jc2] numeric(2,1),[jc3] numeric(2,1),[ysbh] varchar(4))
insert @tb1
select 2.2,2.3,2.5,'0001'declare @tb2 table([jc4] varchar(1),[jc5] varchar(1),[jc6] varchar(1),[ysbh] varchar(4))
insert @tb2
select 'a','b','c','0002'declare @tb3 table([ysbh] varchar(4),[ysxm] varchar(4))
insert @tb3
select '0001','张三' union all
select '0002','李四'select jc1, jc2, jc3, jc4, jc5, jc6,
(select ysxm from @tb3 where ysbh = a.ysbh) as ysbh1,
(select ysxm from @tb3 where ysbh = b.ysbh) as ysbh2
from @tb1 a, @tb2 b/*
jc1 jc2 jc3 jc4 jc5 jc6 ysbh1 ysbh2
---- ---- ---- ---- ---- ---- ----- -----
2.2 2.3 2.5 a b c 张三 李四(所影响的行数为 1 行)
create table a2(jc4 varchar(4),jc5 varchar(4),jc6 varchar(4),ysbh varchar(4))
create table ysbh(ysbh varchar(4),ysxm varchar(10))
insert into a1 select 2.2,2.3,2.5,'0001'
insert into a2 select 'a','b','c','0002'
insert into ysbh select '0001','张三'
insert into ysbh select '0002','李四'
select jc1,jc2,jc3,jc4,jc5,jc6,a1.ysxm,a2.ysxm
from (select jc1,jc2,jc3,b.ysxm from a1 inner join ysbh b on a1.ysbh=b.ysbh ) a1,(select jc4,jc5,jc6,b.ysxm from a2 inner join ysbh b on a2.ysbh=b.ysbh) a2
go
drop table a1
drop table a2
drop table ysbh
/*
jc1 jc2 jc3 jc4 jc5 jc6 ysxm ysxm
---- ---- ---- ---- ---- ---- ---------- ----------
2.2 2.3 2.5 a b c 张三 李四(所影响的行数为 1 行)
*/