27,579
社区成员
发帖
与我相关
我的任务
分享
---此段代码是在sql server 2012上创建并运行的
--创建表1
create table a
(item_name char(1),
data1 tinyint
)
--向表1插入数据
insert into a
values( 'A',10),
( 'B',20),
( 'C',30),
( 'D',40);
--创建表2
create table b
(item_name char(1),
data2 tinyint
)
--向表2插入数据
insert into b
values( 'A',20),
( 'C',30),
( 'E',50);
--解决语句
;with aAndB as
(
select item_name,data1 as data,1 as item from a
union all
select item_name,data2 as data,2 as item from b
)
select item_name, max(case item when 1 then data else 0 end )as item_A,max(case item when 2 then data else 0 end) as item_B
from aAndB
group by item_name
--结果展示
/*
item_name item_A item_B
--------- ----------- -----------
A 10 20
B 20 0
C 30 30
D 40 0
E 0 50
(5 行受影响)
*/
if object_id('tb1') is not null drop table tb1
go
create table tb1 (项目名称 nvarchar(2),数据 int)
insert into tb1
select 'A',10 union all
select 'B',20 union all
select 'C',30 union all
select 'D',40
if object_id('tb2') is not null drop table tb2
go
create table tb2 (项目名称 nvarchar(2),数据 int)
insert into tb2
select 'A',20 union all
select 'C',30 union all
select 'E',50
;with t
as
(
select 项目名称
from tb1
union
select 项目名称
from tb2
)
select t.项目名称,
coalesce(t1.数据,0) as 数据1 ,
coalesce(t2.数据,0) as 数据2
from t
left join tb1 t1 on t.项目名称 =t1.项目名称
left join tb2 t2 on t.项目名称 =t2.项目名称
/*
项目名称 数据1 数据2
A 10 20
B 20 0
C 30 30
D 40 0
E 0 50
*/
if object_id('[表1]') is not null drop table [表1]
go
create table [表1] (项目名称 nvarchar(2),数据 int)
insert into [表1]
select 'A',10 union all
select 'B',20 union all
select 'C',30 union all
select 'D',40
if object_id('[表2]') is not null drop table [表2]
go
create table [表2] (项目名称 nvarchar(2),数据 int)
insert into [表2]
select 'A',20 union all
select 'C',30 union all
select 'E',50
select * from [表1]
select * from [表2]
select s.项目名称,isnull(a.数据,0) as 数据1 ,isnull(b.数据,0) as 数据2 from (
select 项目名称
from [表1]
union
select 项目名称
from [表2]) S
left join [表1] A on s.项目名称 =A.项目名称
left join [表2] B on s.项目名称 =B.项目名称
/*
A 10 20
B 20 0
C 30 30
D 40 0
E 0 50*/
--> 测试数据:[表1]
if object_id('[表1]') is not null drop table [表1]
go
create table [表1]([项目名称] varchar(1),[数据] int)
insert [表1]
select 'A',10 union all
select 'B',20 union all
select 'C',30 union all
select 'D',40
--------------开始查询--------------------------
--> 测试数据:[表2]
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([项目名称] varchar(1),[数据] int)
insert [表2]
select 'A',20 union all
select 'C',30 union all
select 'E',50
select ISNULL(a.[项目名称],b.[项目名称]) AS [项目名称],ISNULL(a.数据,0)[数据1],ISNULL(b.数据 ,0)[数据2]
from [表1] a full JOIN [表2] b ON a.[项目名称]=b.[项目名称]
----------------结果----------------------------
/*
项目名称 数据1 数据2
---- ----------- -----------
A 10 20
B 20 0
C 30 30
D 40 0
E 0 50
*/