数据库多表查询
两个表，表1，和表2
表1字段如下
name riqi gongzi1
aaa 2013 50
bbb 2013 60
aaa 2014 55
表2字段如下
name riqi gongzi2
aaa 2013 40
aaa 2015 50
如何联合查询两表得到如下
name riqi gongzi1 gongzi2
aaa 2013 50 40
aaa 2014 55 0
aaa 2015 0 50
bbb 2013 60 0
这个样子的结果，用视图可以写出来吗
数据库
[解决办法]
select t1.[name],t1.riqi,gongzi1,gongzi2
from 表1 t1
inner join 表2 t2 on t1.[name]=t2.[name] and t1.riqi=t2.riqi
--当然也可以把上述语句放到视图里
if OBJECT_ID('tempA', 'u') is not null drop table tempA;
go
create table tempA( [name] varchar(100), [riqi] varchar(100), [gongzi1] varchar(100));
insert tempA
select 'aaa','2013','50' union all
select 'bbb','2013','60' union all
select 'aaa','2014','55'
if OBJECT_ID('tempB', 'u') is not null drop table tempB;
go
create table tempB( [name] varchar(100), [riqi] varchar(100), [gongzi2] varchar(100));
insert tempB
select 'aaa','2013','40' union all
select 'aaa','2015','50'
--SQL:
CREATE VIEW v_test
AS
SELECT
name = COALESCE(a.name,b.name),
riqi = COALESCE(a.riqi,b.riqi),
gongzi1 = ISNULL(a.gongzi1,0),
gongzi2 = ISNULL(b.gongzi2, 0)
FROM tempA a
FULL JOIN tempB b
ON a.name = b.name
AND a.riqi = b.riqi
GO
SELECT * FROM v_test
ORDER BY name,riqi
/*
nameriqigongzi1gongzi2
aaa20135040
aaa2014550
aaa2015050
bbb2013600
*/