数据库多表查询

[lc12d]

两个表，表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

--当然也可以把上述语句放到视图里

[Shawn]

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
/*
name	riqi	gongzi1	gongzi2
aaa	2013	50	40
aaa	2014	55	0
aaa	2015	0	50
bbb	2013	60	0
*/