select a.a1,a.b1,a.c1,isnull(a.d1,0) d1,b.a2,isnull(b.d2,0) d2,isnull(a.d1,0)-isnull(b.d2,0) d3 from table1 a full join table2 b on a.a1=b.a2
select a.a1,a.b1,a.c1,isnull(a.d1,0) d1,b.a2,isnull(b.d2,0) d2,cast(isnull(a.d1,0) as varchar(10)) + '-' + cast(isnull(b.d2,0) as varchar(10)) d3 from table1 a full join table2 b on a.a1=b.a2
select a.a1,a.b1,a.c1,isnull(a.d1,0) as d1,b.a2,isnull(b.d2,0) as d2, cast(isnull(a.d1,0) as varchar )+'-'+cast(isnull(b.d2,0) as varchar) as d3 from t1 a full join t2 b on a.a1=b.a2 order by isnull(a.a1,b.a2)
select a.a1,a.b1,a.c1,isnull(a.d1,0) as d1,b.a2,isnull(b.d2,0) as d2, cast(isnull(a.d1,0) as varchar )+'-'+cast(isnull(b.d2,0) as varchar) as d3 from t1 a full join t2 b on a.a1=b.a2 order by isnull(a.a1,b.a2)
select table1.a1,table1.b1,table1.c1,isnull(table1.d1,0) as d1,table2.a2,isnull(table2.d2,0) as d2,isnull(table1.d1,0)-isnull(table2.d2,0) as d3 from table1 full join table2 on table1.a1=table2.a2
select isnull(a.a1,''),isnull(a.b1,''),isnull(a.c1,''),isnull(a.d1,0) as d1,b.a2,isnull(b.d2,0) as d2, cast(isnull(a.d1,0) as varchar )+'-'+cast(isnull(b.d2,0) as varchar) as d3 from t1 a full join t2 b on a.a1=b.a2 order by isnull(a.a1,b.a2)
declare @a table(a int,b int) declare @b table(a int,b int) insert @a values(1,1) insert @a values(2,2) insert @b values(1,1) insert @b values(3,3)--左: select * from @a Aa left join @b Bb on Aa.a=Bb.a --右: select * from @a Aa right join @b Bb on Aa.a=Bb.a --内 select * from @a Aa join @b Bb on Aa.a=Bb.a --外 select * from @a Aa full join @b Bb on Aa.a=Bb.a --完全 select * from @a,@b 你考入到查询分析器执行,就可以看到,你的需求就是这里的外连接
select * from @a,@b 几乎没有实际意义 仅仅是笛卡尔积full join
select * from @a Aa full join @b Bb on Aa.a=Bb.a
declare @a table(a int,b int) declare @b table(a int,b int) insert @a values(1,1) insert @a values(2,2) insert @b values(1,1) insert @b values(3,3)--左: select * from @a Aa left join @b Bb on Aa.a=Bb.a --右: select * from @a Aa right join @b Bb on Aa.a=Bb.a --内 select * from @a Aa join @b Bb on Aa.a=Bb.a --外 select * from @a Aa full join @b Bb on Aa.a=Bb.a <<=======这里你看,你懂它的意思吗? --完全 select * from @a,@b 你只要select * from 表1 full join 表2 on 表1.a1=表2.a2根据需求要得到d3select *,isnull(表1.d1,0)-isnull(表2.d2,0) d3 from 表1 full join 表2 on 表1.a1=表2.a2如果你的d3只要得到字符而不是计算的结果select *,cast(isnull(表1.d1,0) as varchar(10))+'-'+cast(isnull(表2.d2,0) as varchar(10)) d3 from 表1 full join 表2 on 表1.a1=表2.a2上面用到的isnull是用来替换null值cast是用来转换类型
select isnull(A.A1,''),isnull(A.B1,''),isnull(A.C1,''),isnull(A.D1,0), isnull(B.A2,''),isnull(B.D2,0),isnull(A.D1,0) - isnull(B.D2,0) D3 from table1 A full join table2 B on A.A1 = B.A2 order by isnull(A.A1,B.A2)
from table1 a full join table2 b
on a.a1=b.a2
from table1 a full join table2 b
on a.a1=b.a2
select a.a1,a.b1,a.c1,isnull(a.d1,0) as d1,b.a2,isnull(b.d2,0) as d2,
cast(isnull(a.d1,0) as varchar )+'-'+cast(isnull(b.d2,0) as varchar) as d3
from t1 a full join t2 b
on a.a1=b.a2
order by isnull(a.a1,b.a2)
select a.a1,a.b1,a.c1,isnull(a.d1,0) as d1,b.a2,isnull(b.d2,0) as d2,
cast(isnull(a.d1,0) as varchar )+'-'+cast(isnull(b.d2,0) as varchar) as d3
from t1 a full join t2 b
on a.a1=b.a2
order by isnull(a.a1,b.a2)
from table1 full join table2
on table1.a1=table2.a2
cast(isnull(a.d1,0) as varchar )+'-'+cast(isnull(b.d2,0) as varchar) as d3
from t1 a full join t2 b
on a.a1=b.a2
order by isnull(a.a1,b.a2)
declare @b table(a int,b int)
insert @a values(1,1)
insert @a values(2,2)
insert @b values(1,1)
insert @b values(3,3)--左:
select * from @a Aa left join @b Bb on Aa.a=Bb.a
--右:
select * from @a Aa right join @b Bb on Aa.a=Bb.a
--内
select * from @a Aa join @b Bb on Aa.a=Bb.a
--外
select * from @a Aa full join @b Bb on Aa.a=Bb.a
--完全
select * from @a,@b
你考入到查询分析器执行,就可以看到,你的需求就是这里的外连接
几乎没有实际意义
仅仅是笛卡尔积full join
declare @b table(a int,b int)
insert @a values(1,1)
insert @a values(2,2)
insert @b values(1,1)
insert @b values(3,3)--左:
select * from @a Aa left join @b Bb on Aa.a=Bb.a
--右:
select * from @a Aa right join @b Bb on Aa.a=Bb.a
--内
select * from @a Aa join @b Bb on Aa.a=Bb.a
--外
select * from @a Aa full join @b Bb on Aa.a=Bb.a <<=======这里你看,你懂它的意思吗?
--完全
select * from @a,@b
你只要select * from 表1 full join 表2 on 表1.a1=表2.a2根据需求要得到d3select *,isnull(表1.d1,0)-isnull(表2.d2,0) d3 from 表1 full join 表2 on 表1.a1=表2.a2如果你的d3只要得到字符而不是计算的结果select *,cast(isnull(表1.d1,0) as varchar(10))+'-'+cast(isnull(表2.d2,0) as varchar(10)) d3 from 表1 full join 表2 on 表1.a1=表2.a2上面用到的isnull是用来替换null值cast是用来转换类型
isnull(B.A2,''),isnull(B.D2,0),isnull(A.D1,0) - isnull(B.D2,0) D3
from table1 A full join table2 B
on A.A1 = B.A2
order by isnull(A.A1,B.A2)