数据库多表查询
两个表，表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
*/