select isnull(isnull(#a.ksid,#b.ksid),#c.ksid) ksid,#a.sl1 value1,#b.sl2 value2,#c.sl3value3 from #a full join #b on #a.ksid=#b.ksid full join #c on isnull(#a.ksid,#b.ksid)=#c.ksid
CREATE TABLE #a ([ksid] [char] (10),[yyyy] int,[sl1] [int]) CREATE TABLE #b ([ksid] [char] (10),[yyyy] int,[sl2] [int]) CREATE TABLE #c ([ksid] [char] (10),[yyyy] int,[sl3] [int])insert into #a(ksid,yyyy,sl1) values('387',2015,2) insert into #b(ksid,yyyy,sl2) values('387',2014,2) insert into #b(ksid,yyyy,sl2) values('387',2016,1) insert into #b(ksid,yyyy,sl2) values('387',2017,7) insert into #c(ksid,yyyy,sl3) values('387',2014,3) insert into #c(ksid,yyyy,sl3) values('387',2015,2) insert into #c(ksid,yyyy,sl3) values('387',2016,8) select isnull(isnull(#a.ksid,#b.ksid),#c.ksid) ksid,isnull(isnull(#a.yyyy,#b.yyyy),#c.yyyy) yyyy,#a.sl1 value1,#b.sl2 value2,#c.sl3 value3 from #a full join #b on #a.yyyy=#b.yyyy full join #c on isnull(#a.yyyy,#b.yyyy)=#c.yyyy order by #a.ksid ksid yyyy value1 value2 value3 ---------- ----------- ----------- ----------- ----------- 387 2014 NULL 2 3 387 2016 NULL 1 8 387 2017 NULL 7 NULL 387 2015 2 NULL 2(所影响的行数为 4 行)drop table #a drop table #bdrop table #c
CREATE TABLE #a ([ksid] [char] (10),[yyyy] int,[sl1] [int]) CREATE TABLE #b ([ksid] [char] (10),[yyyy] int,[sl2] [int]) CREATE TABLE #c ([ksid] [char] (10),[yyyy] int,[sl3] [int])insert into #a(ksid,yyyy,sl1) values('387',2015,2) insert into #b(ksid,yyyy,sl2) values('387',2014,2) insert into #b(ksid,yyyy,sl2) values('387',2016,1) insert into #b(ksid,yyyy,sl2) values('387',2017,7) insert into #c(ksid,yyyy,sl3) values('387',2014,3) insert into #c(ksid,yyyy,sl3) values('387',2015,2) insert into #c(ksid,yyyy,sl3) values('387',2016,8) select isnull(isnull(#a.ksid,#b.ksid),#c.ksid) ksid,isnull(isnull(#a.yyyy,#b.yyyy),#c.yyyy) yyyy,#a.sl1 value1,#b.sl2 value2,#c.sl3 value3 from #a full join #b on #a.yyyy=#b.yyyy full join #c on isnull(#a.yyyy,#b.yyyy)=#c.yyyy order by #a.ksid ksid yyyy value1 value2 value3 ---------- ----------- ----------- ----------- ----------- 387 2014 NULL 2 3 387 2016 NULL 1 8 387 2017 NULL 7 NULL 387 2015 2 NULL 2(所影响的行数为 4 行)drop table #a drop table #bdrop table #c
select ksid, yyyy , (select sl1 from data1 where maindata.ksid = data1.ksid and maindata.yyyy = data1.yyyy) as sl1 , (select sl2 from data2 where maindata.ksid = data2.ksid and maindata.yyyy = data2.yyyy) as sl2 , (select sl3 from data3 where data3.ksid = maindata.ksid and data3.yyyy = maindata.yyyy) as sl3 from maindata
if not object_id(N'Tempdb..#Tmp_DataA') is null drop table #Tmp_DataA Go Create table #Tmp_DataA( sl1 int, yyyy int, ksid int)Insert #Tmp_DataA select 2,2015,387 if not object_id(N'Tempdb..#Tmp_DataB') is null drop table #Tmp_DataB Go Create table #Tmp_DataB( sl2 int, yyyy int, ksid int)Insert #Tmp_DataB select 2,2014,387 union Select 1,2016,387 union Select 7,2017,387 if not object_id(N'Tempdb..#Tmp_DataC') is null drop table #Tmp_DataC Go Create table #Tmp_DataC( sl3 int, yyyy int, ksid int)Insert #Tmp_DataC select 3,2014,387 union Select 2,2015,387 union Select 8,2016,387Select a.KsID,a.yyyy,b.sl1,c.sl2,d.sl3 From ( Select ksid,yyyy From #Tmp_DataA union Select ksid,yyyy From #Tmp_DataB union Select ksid,yyyy From #Tmp_DataC ) a Left Join #Tmp_DataA b on a.ksid=b.ksid and a.yyyy=b.yyyy Left Join #Tmp_DataB c on a.ksid=c.ksid and a.yyyy=c.yyyy Left Join #Tmp_DataC d on a.ksid=d.ksid and a.yyyy=d.yyyy
借用1楼的回答,其实你根据这条语句,把对应的表改为你的实际的表就行了 select isnull(isnull(#a.ksid,#b.ksid),#c.ksid) ksid,isnull(isnull(#a.yyyy,#b.yyyy),#c.yyyy) yyyy,#a.sl1 value1,#b.sl2 value2,#c.sl3 value3 from #a full join #b on #a.yyyy=#b.yyyy full join #c on isnull(#a.yyyy,#b.yyyy)=#c.yyyy order by #a.ksid(此处应改为yyyy字段)
三张表先合并,再用ksid分组,再用case显示多列就行了
CREATE TABLE #a ([ksid] [char] (10),[yyyy] int,[sl1] [int]) CREATE TABLE #b ([ksid] [char] (10),[yyyy] int,[sl2] [int]) CREATE TABLE #c ([ksid] [char] (10),[yyyy] int,[sl3] [int])insert into #a(ksid,yyyy,sl1) values('387',2015,2) insert into #b(ksid,yyyy,sl2) values('387',2014,2) insert into #b(ksid,yyyy,sl2) values('387',2016,1) insert into #b(ksid,yyyy,sl2) values('387',2017,7) insert into #c(ksid,yyyy,sl3) values('387',2014,3) insert into #c(ksid,yyyy,sl3) values('387',2015,2) insert into #c(ksid,yyyy,sl3) values('387',2016,8) select ksid,yyyy,sum(sl1)sl1,sum(sl2)sl2,sum(sl3)sl3 from (select ksid,yyyy,sl1,null sl2,null sl3 from #a union all select ksid,yyyy,null,sl2,null sl3 from #b union all select ksid,yyyy,null,null,sl3 from #c )t group by ksid,yyyy
from #a full join #b on #a.ksid=#b.ksid
full join #c on isnull(#a.ksid,#b.ksid)=#c.ksid
CREATE TABLE #b ([ksid] [char] (10),[yyyy] int,[sl2] [int])
CREATE TABLE #c ([ksid] [char] (10),[yyyy] int,[sl3] [int])insert into #a(ksid,yyyy,sl1) values('387',2015,2)
insert into #b(ksid,yyyy,sl2) values('387',2014,2)
insert into #b(ksid,yyyy,sl2) values('387',2016,1)
insert into #b(ksid,yyyy,sl2) values('387',2017,7)
insert into #c(ksid,yyyy,sl3) values('387',2014,3)
insert into #c(ksid,yyyy,sl3) values('387',2015,2)
insert into #c(ksid,yyyy,sl3) values('387',2016,8)
select isnull(isnull(#a.ksid,#b.ksid),#c.ksid) ksid,isnull(isnull(#a.yyyy,#b.yyyy),#c.yyyy) yyyy,#a.sl1 value1,#b.sl2 value2,#c.sl3 value3
from #a full join #b on #a.yyyy=#b.yyyy
full join #c on isnull(#a.yyyy,#b.yyyy)=#c.yyyy
order by #a.ksid
ksid yyyy value1 value2 value3
---------- ----------- ----------- ----------- -----------
387 2014 NULL 2 3
387 2016 NULL 1 8
387 2017 NULL 7 NULL
387 2015 2 NULL 2(所影响的行数为 4 行)drop table #a
drop table #bdrop table #c
CREATE TABLE #b ([ksid] [char] (10),[yyyy] int,[sl2] [int])
CREATE TABLE #c ([ksid] [char] (10),[yyyy] int,[sl3] [int])insert into #a(ksid,yyyy,sl1) values('387',2015,2)
insert into #b(ksid,yyyy,sl2) values('387',2014,2)
insert into #b(ksid,yyyy,sl2) values('387',2016,1)
insert into #b(ksid,yyyy,sl2) values('387',2017,7)
insert into #c(ksid,yyyy,sl3) values('387',2014,3)
insert into #c(ksid,yyyy,sl3) values('387',2015,2)
insert into #c(ksid,yyyy,sl3) values('387',2016,8)
select isnull(isnull(#a.ksid,#b.ksid),#c.ksid) ksid,isnull(isnull(#a.yyyy,#b.yyyy),#c.yyyy) yyyy,#a.sl1 value1,#b.sl2 value2,#c.sl3 value3
from #a full join #b on #a.yyyy=#b.yyyy
full join #c on isnull(#a.yyyy,#b.yyyy)=#c.yyyy
order by #a.ksid
ksid yyyy value1 value2 value3
---------- ----------- ----------- ----------- -----------
387 2014 NULL 2 3
387 2016 NULL 1 8
387 2017 NULL 7 NULL
387 2015 2 NULL 2(所影响的行数为 4 行)drop table #a
drop table #bdrop table #c
ksid, yyyy
, (select sl1 from data1 where maindata.ksid = data1.ksid and maindata.yyyy = data1.yyyy) as sl1
, (select sl2 from data2 where maindata.ksid = data2.ksid and maindata.yyyy = data2.yyyy) as sl2
, (select sl3 from data3 where data3.ksid = maindata.ksid and data3.yyyy = maindata.yyyy) as sl3
from maindata
drop table #Tmp_DataA
Go
Create table #Tmp_DataA(
sl1 int,
yyyy int,
ksid int)Insert #Tmp_DataA select 2,2015,387
if not object_id(N'Tempdb..#Tmp_DataB') is null
drop table #Tmp_DataB
Go
Create table #Tmp_DataB(
sl2 int,
yyyy int,
ksid int)Insert #Tmp_DataB
select 2,2014,387 union
Select 1,2016,387 union
Select 7,2017,387
if not object_id(N'Tempdb..#Tmp_DataC') is null
drop table #Tmp_DataC
Go
Create table #Tmp_DataC(
sl3 int,
yyyy int,
ksid int)Insert #Tmp_DataC
select 3,2014,387 union
Select 2,2015,387 union
Select 8,2016,387Select a.KsID,a.yyyy,b.sl1,c.sl2,d.sl3
From (
Select ksid,yyyy From #Tmp_DataA
union
Select ksid,yyyy From #Tmp_DataB
union
Select ksid,yyyy From #Tmp_DataC
) a
Left Join #Tmp_DataA b on a.ksid=b.ksid and a.yyyy=b.yyyy
Left Join #Tmp_DataB c on a.ksid=c.ksid and a.yyyy=c.yyyy
Left Join #Tmp_DataC d on a.ksid=d.ksid and a.yyyy=d.yyyy
select isnull(isnull(#a.ksid,#b.ksid),#c.ksid) ksid,isnull(isnull(#a.yyyy,#b.yyyy),#c.yyyy) yyyy,#a.sl1 value1,#b.sl2 value2,#c.sl3 value3
from #a full join #b on #a.yyyy=#b.yyyy
full join #c on isnull(#a.yyyy,#b.yyyy)=#c.yyyy
order by #a.ksid(此处应改为yyyy字段)
CREATE TABLE #b ([ksid] [char] (10),[yyyy] int,[sl2] [int])
CREATE TABLE #c ([ksid] [char] (10),[yyyy] int,[sl3] [int])insert into #a(ksid,yyyy,sl1) values('387',2015,2)
insert into #b(ksid,yyyy,sl2) values('387',2014,2)
insert into #b(ksid,yyyy,sl2) values('387',2016,1)
insert into #b(ksid,yyyy,sl2) values('387',2017,7)
insert into #c(ksid,yyyy,sl3) values('387',2014,3)
insert into #c(ksid,yyyy,sl3) values('387',2015,2)
insert into #c(ksid,yyyy,sl3) values('387',2016,8) select ksid,yyyy,sum(sl1)sl1,sum(sl2)sl2,sum(sl3)sl3
from
(select ksid,yyyy,sl1,null sl2,null sl3 from #a
union all
select ksid,yyyy,null,sl2,null sl3 from #b
union all
select ksid,yyyy,null,null,sl3 from #c
)t
group by ksid,yyyy