表A
ID1 ID2 Num1
1 2 3
1 4 5
1 3 6表B
ID1 ID2 Num2
1 3 7表C
ID1 ID2 Num3
1 3 8
1 4 2表A、表B、表C的关键字都是ID1和ID2。得到结果:
ID1 ID2 Num1 Num2 Num3
1 2 3
1 4 5 2
1 3 6 7 8
ID1 ID2 Num1
1 2 3
1 4 5
1 3 6表B
ID1 ID2 Num2
1 3 7表C
ID1 ID2 Num3
1 3 8
1 4 2表A、表B、表C的关键字都是ID1和ID2。得到结果:
ID1 ID2 Num1 Num2 Num3
1 2 3
1 4 5 2
1 3 6 7 8
Select A.*, B.Num2, C.Num3
From A
Left Join B On A.ID1 = B.ID1 And A.ID2 = B.ID2
Left Join C On A.ID1 = C.ID1 And A.ID2 = C.ID2
isnull(t.id1,c.id1) as id1,
isnull(t.id2,c.id2) as id2,
t.num1,
t.num2,
c.num3
from (
select
isnull(a.id1,b.id1) as id1,
isnull(a.id2,b.id2) as id2,
a.num1,
b.num2
from a full join b
on a.id1=b.id1 and a.id2=b.id2
) as t
full join c
on t.id1=c.id1 and t.id2=c.id2
(
ID1 int ,
ID2 int ,
Num1 int
)
insert into EastHoo1select
1 ,2 ,3 union all select
1 ,4 ,5 union all select
1 ,3 ,6
create table EastHoo2
(
ID1 int ,
ID2 int ,
Num2 int
)
insert into EastHoo2 select
1 ,3 ,7
create table EastHoo3
(
ID1 int ,
ID2 int ,
Num3 int
)insert into EastHoo3
select
1 ,3 ,8 union all select
1 ,4 ,2select EastHoo1.*, isnull(cast(EastHoo2.Num2 as char),'' ) as Num2, isnull(cast(EastHoo3.Num3 as char),' ') as Num3 from
EastHoo1 left join EastHoo2
on EastHoo1.ID1 =EastHoo2.ID1 and EastHoo1.ID2 =EastHoo2.ID2
left join EastHoo3
on EastHoo1.ID1 =EastHoo3.ID1 and EastHoo1.ID2 =EastHoo3.ID2
假如
表C
ID1 ID2 Num3
1 3 8
1 4 2
1 2 2我需要的结果是:
ID1 ID2 Num1 Num2 Num3
1 2 3
1 4 5 2
1 3 6 7 8
2 2 2
这样
select a.id1,a.id2,num1,num2,num3 from A Left Join B on a.id1=b.id1 and a.id2=b.id2 Left Join C on a.id1=c.id1 and a.id2=c.id2
是不正确的。
Coalesce(A.ID1, B.ID1, C.ID1) As ID1,
Coalesce(A.ID2, B.ID2, C.ID2) As ID2,
Num1,
Num2,
Num3
From
A
Full Join
B
On
A.ID1 = B.ID1 And A.ID2 = B.ID2
Full Join
C
On
A.ID1 = C.ID1 And A.ID2 = C.ID2
(ID1 Int ,
ID2 Int ,
Num1 Int )Insert Into A
Select 1 ,2 ,3
Union All Select 1 ,4 ,5
Union All Select 1 ,3 ,6
Union All Select 1 ,6 ,6Create Table B
(ID1 Int ,
ID2 Int ,
Num2 Int)
Insert Into B Select 1 ,3 ,7
Union All Select 1 ,7 ,6Create Table C
(ID1 Int ,
ID2 Int ,
Num3 Int )Insert Into C Select 1 ,3 ,8
Union All Select 1 ,4 ,2
Union All Select 1 ,5 ,3
GO
Select
Coalesce(A.ID1, B.ID1, C.ID1) As ID1,
Coalesce(A.ID2, B.ID2, C.ID2) As ID2,
Num1,
Num2,
Num3
From
A
Full Join
B
On
A.ID1 = B.ID1 And A.ID2 = B.ID2
Full Join
C
On
A.ID1 = C.ID1 And A.ID2 = C.ID2
Order By
ID1, ID2
GO
Drop Table A, B, C
--Result
/*
ID1 ID2 Num1 Num2 Num3
1 2 3 NULL NULL
1 3 6 7 8
1 4 5 NULL 2
1 5 NULL NULL 3
1 6 6 NULL NULL
1 7 NULL 6 NULL
*/
id1,id2,
sum(num1) as num1,
sum(num2) as num2,
sum(num3) as num3
from (
select
id1,id2
num1,
cast(null as int) as num2,
cast(null as int) as num3
from a
union all
select
id1,id2
null as num1,
num2,
cast(null as int) as num3
from b
union all
select
id1,id2
null as num1,
cast(null as int) as num2,
num3
from c
) as t
group by id1,id2ps:信誉是以前得的,没见邹建600多吗
实际上我的就是7个表:(
谢谢你!来这里主要是学习嘛,分只是荣誉和动力。
insert into a select 1,2,3
insert into a select 1,4,5
insert into a select 1,3,6create table b(id1 int,id2 int,num2 int)
insert into b select 1,3,7
create table c(id1 int,id2 int,num3 int)
insert into c select 1,3,8
insert into c select 1,4,2select a.id1,a.id2,a.num1,b.num2,'' as num3 from a,b
where a.id1=b.id1 and a.id2=b.id2
union all
select a.id1,a.id2,a.num1, '' as num2, c.num3 from a,c
where a.id1=c.id1 and a.id2=c.id2id1 id2 num1 num2 num3
----------- ----------- ----------- ----------- -----------
1 3 6 7 0
1 4 5 0 2
1 3 6 0 8(3 行受影响)
假如我的表 关键字不只是ID1和ID2两列而已,比如3列
你写的SQL还正确吗?Coalesce函数 返回其参数中第一个非空表达式
TO paoluo(一天到晚游泳的鱼)
假如我的表 关键字不只是ID1和ID2两列而已,比如3列
你写的SQL还正确吗?Coalesce函数 返回其参数中第一个非空表达式
-------------
一樣可以用的,要修改的只是你的關聯條件。