表a
姓名 一月份工资
张飞 1500
关羽 2000
赵云 1200
黄忠 1100
表b
姓名 二月份工资
姜维 1000
关羽 2000
赵云 3000
表c
姓名 三月份工资
诸葛亮 3000
徐庶 2000
赵云 3000
需要得到以下查询结果:
姓名 一月份工资 二月份工资 三月份工资
关羽 2000 2000
张飞 1500
赵云 1200 3000 3000
黄忠 1100
诸葛亮 3000
徐庶 2000
姜维 1000这样的sql语句怎么写?
姓名 一月份工资
张飞 1500
关羽 2000
赵云 1200
黄忠 1100
表b
姓名 二月份工资
姜维 1000
关羽 2000
赵云 3000
表c
姓名 三月份工资
诸葛亮 3000
徐庶 2000
赵云 3000
需要得到以下查询结果:
姓名 一月份工资 二月份工资 三月份工资
关羽 2000 2000
张飞 1500
赵云 1200 3000 3000
黄忠 1100
诸葛亮 3000
徐庶 2000
姜维 1000这样的sql语句怎么写?
select a.*,b.一月份工资,c.二月份工资,d.三月份工资
from(
select 姓名 from 表a
union
select 姓名 from 表b
union
select 姓名 from 表c) a
left join 表a b on a.姓名=b.姓名
left join 表B c on a.姓名=c.姓名
left join 表c d on a.姓名=d.姓名
t1.姓名,a.一月份工资,b.二月份工资,c.三月份工资
from
(select 姓名 from a union select 姓名 from b union select 姓名 from c)as t1
left join a on t1.姓名=a.姓名
left join b on t1.姓名=b.姓名
left join c on t1.姓名=c.姓名
create table #a
([姓名] nvarchar(10),[一月] int)
insert into #a values('张飞', 1500)
insert into #a values('关羽', 2000)
insert into #a values('赵云', 1200)
insert into #a values('黄忠', 1100)go
create table #b
([姓名] nvarchar(10),[二月] int)
insert into #b values('姜维', 1000)
insert into #b values('关羽', 2000)
insert into #b values('赵云', 3000)go
create table #c
([姓名] nvarchar(10),[三月] int)
insert into #c values('诸葛亮', 3000)
insert into #c values('徐庶', 2000)
insert into #c values('赵云', 3000)with nameT as (select [姓名] from #a union select [姓名] from #b union select [姓名] from #c)
select t.[姓名],isnull(a.[一月],'') as '一月',isnull(b.[二月],'') as '二月',isnull(c.[三月],'') as '三月' from nameT as t
left join #a as a on t.[姓名]=a.[姓名]
left join #b as b on t.[姓名]=b.[姓名]
left join #c as c on t.[姓名]=c.[姓名]
use tempdb;
/*
create table a
(
[姓名] nvarchar(20) not null,
[一月份工资] int not null
);
insert into a([姓名],[一月份工资])
values
('张飞',1500),
('关羽',2000),
('赵云',1200),
('黄忠',1100);create table b
(
[姓名] nvarchar(20) not null,
[二月份工资] int not null
);
insert into b([姓名],[二月份工资])
values
('姜维',1000),
('关羽',2000),
('赵云',3000);create table c
(
[姓名] nvarchar(20) not null,
[三月份工资] int not null
);
insert into c([姓名],[三月份工资])
values
('诸葛亮',3000),
('徐庶',2000),
('赵云',3000);
*/select
t.[姓名],
a.[一月份工资],
b.[二月份工资],
c.[三月份工资]
from
(
select [姓名] from a
union
select [姓名] from b
union
select [姓名] from c
) as t
left join a on t.[姓名] = a.[姓名]
left join b on t.[姓名] = b.[姓名]
left join c on t.[姓名] = c.[姓名];--如果没有工资显示为0
select
t.[姓名],
isnull(a.[一月份工资],0) as [一月份工资],
isnull(b.[二月份工资],0) as [二月份工资],
isnull(c.[三月份工资],0) as [三月份工资]
from
(
select [姓名] from a
union
select [姓名] from b
union
select [姓名] from c
) as t
left join a on t.[姓名] = a.[姓名]
left join b on t.[姓名] = b.[姓名]
left join c on t.[姓名] = c.[姓名];
[/code]
use tempdb;
/*
create table a
(
[姓名] nvarchar(20) not null,
[一月份工资] int not null
);
insert into a([姓名],[一月份工资])
values
('张飞',1500),
('关羽',2000),
('赵云',1200),
('黄忠',1100);create table b
(
[姓名] nvarchar(20) not null,
[二月份工资] int not null
);
insert into b([姓名],[二月份工资])
values
('姜维',1000),
('关羽',2000),
('赵云',3000);create table c
(
[姓名] nvarchar(20) not null,
[三月份工资] int not null
);
insert into c([姓名],[三月份工资])
values
('诸葛亮',3000),
('徐庶',2000),
('赵云',3000);
*/select
t.[姓名],
a.[一月份工资],
b.[二月份工资],
c.[三月份工资]
from
(
select [姓名] from a
union
select [姓名] from b
union
select [姓名] from c
) as t
left join a on t.[姓名] = a.[姓名]
left join b on t.[姓名] = b.[姓名]
left join c on t.[姓名] = c.[姓名];--如果没有工资显示为0
select
t.[姓名],
isnull(a.[一月份工资],0) as [一月份工资],
isnull(b.[二月份工资],0) as [二月份工资],
isnull(c.[三月份工资],0) as [三月份工资]
from
(
select [姓名] from a
union
select [姓名] from b
union
select [姓名] from c
) as t
left join a on t.[姓名] = a.[姓名]
left join b on t.[姓名] = b.[姓名]
left join c on t.[姓名] = c.[姓名];
[/code]