select year=COALESCE(a.year,b.year),cost1=isnull(a.cost1,0),cost2=isnull(b.cost2,0) from a full join b on a.year=b.year order by year
select isnull(a.year,b.year) ,isnull(a.cost1,0),isnull(b.cost2,0) from a full join b on a.year=b.yearfull的意思是year字段a表中有的b表中有的都显示说可能不太清楚,你试一下以下就知道了drop table test_a go drop table test_b go create table test_a (a int, b int) go create table test_b (a int, b int) go insert test_a values (1,1) insert test_a values (2,1) insert test_a values (3,1) go insert test_b values (4,1) insert test_b values (2,1) insert test_b values (3,1) go select test_a.* ,test_b.* from test_a left join test_b on test_a.a=test_b.a select test_a.* ,test_b.* from test_a right join test_b on test_a.a=test_b.a select test_a.* ,test_b.* from test_a inner join test_b on test_a.a=test_b.a select test_a.* ,test_b.* from test_a full join test_b on test_a.a=test_b.a
select t.year,isnull(a.cost1,0) as cost1,isnull(b.cost2,0) as cost2 from (select year from a group by year union select year from b group by year)t left join a on t.year=a.year left join b on t.year=b.yearorder by t.year
to sky_blue(老衲)and supsuccess(火气不小): 对不起,我没有说清我用的是Access, 请帮帮我,如果不用Full Join 应该怎么办?
to supsuccess(火气不小): 我在Access里面试了,出错: “语法错误(操作符丢失):在查询表达式“t.year=a.year left join b on t.year=b.year”中! 请问是怎么回事?
select a.year,a.cost1,b.cost2 from a inner join b on a.year=b.year
用select a.*,b.* from a,b试试
这么可以么,要两个临时表,两个临时表结构一样,都是: year,cost1,cost2 分别是tmp1、tmp2。delete from tmp1 delete from tmp2 insert into tmp1 select *,0 as cost2 from a insert into tmp1 select *,0 as cost1 from b insert into tmp2 select year,sum(cost1) as cost1,sum(cost2) as cost2 from tmp1 group by year应该没错。
full join 的作用是什么,为什么要用full呢?
from a full join b on a.year=b.year
order by year
go
drop table test_b
go
create table test_a (a int, b int)
go
create table test_b (a int, b int)
go
insert test_a values (1,1)
insert test_a values (2,1)
insert test_a values (3,1)
go
insert test_b values (4,1)
insert test_b values (2,1)
insert test_b values (3,1)
go
select test_a.* ,test_b.* from test_a left join test_b on test_a.a=test_b.a
select test_a.* ,test_b.* from test_a right join test_b on test_a.a=test_b.a
select test_a.* ,test_b.* from test_a inner join test_b on test_a.a=test_b.a
select test_a.* ,test_b.* from test_a full join test_b on test_a.a=test_b.a
我用的是access,我刚才试了一下,好像access里面没有full join,所以不能成功!请问可不可以不用full join?
谢谢你^_^
(select year from a group by year
union
select year from b group by year)t left join a on t.year=a.year
left join b on t.year=b.yearorder by t.year
对不起,我没有说清我用的是Access,
请帮帮我,如果不用Full Join 应该怎么办?
让我思考一下你的方法,
不过Access里面支持 isnull吗?
我在Access里面试了,出错:
“语法错误(操作符丢失):在查询表达式“t.year=a.year
left join b on t.year=b.year”中!
请问是怎么回事?
year,cost1,cost2
分别是tmp1、tmp2。delete from tmp1
delete from tmp2
insert into tmp1 select *,0 as cost2 from a
insert into tmp1 select *,0 as cost1 from b
insert into tmp2 select year,sum(cost1) as cost1,sum(cost2) as cost2 from tmp1 group by year应该没错。