a
id a1 a2
001 10 20
002 20 30
003 30 40
004 40 50b
id b1 b2
001 40 50
003 50 60期望的结果:
id a1 b1
001 10 40
002 20
003 30 50
004 40用INNER JOIN b ON b.id = a.id只会显示b表中的记录,怎么写能让显示出所要的结果?
id a1 a2
001 10 20
002 20 30
003 30 40
004 40 50b
id b1 b2
001 40 50
003 50 60期望的结果:
id a1 b1
001 10 40
002 20
003 30 50
004 40用INNER JOIN b ON b.id = a.id只会显示b表中的记录,怎么写能让显示出所要的结果?
declare @a table([id] nvarchar(3),[a1] int,[a2] int)
Insert @a
select N'001',10,20 union all
select N'002',20,30 union all
select N'003',30,40 union all
select N'004',40,50
--Select * from @adeclare @b table([id] nvarchar(3),[b1] int,[b2] int)
Insert @b
select N'001',40,50 union all
select N'003',50,60
--Select * from @bselect * from @a a
left join @b b on a.[id] = b.[id]
/*
id a1 a2 id b1 b2
---- ----------- ----------- ---- ----------- -----------
001 10 20 001 40 50
002 20 30 NULL NULL NULL
003 30 40 003 50 60
004 40 50 NULL NULL NULL(4 row(s) affected)*/
select * from @a a
inner join @b b on a.[id] = b.[id]
/*
id a1 a2 id b1 b2
---- ----------- ----------- ---- ----------- -----------
001 10 20 001 40 50
003 30 40 003 50 60*/
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a (id varchar(11),a1 int,a2 int)
insert into #a
select '001',10,20 union all
select '002',20,30 union all
select '003',30,40 union all
select '004',40,50
--> 测试数据: #b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b (id varchar(11),b1 int,b2 int)
insert into #b
select '001',40,50 union all
select '003',50,60select a.id, a.a1, b.b1 from #a a left join #b b on a.id=b.id
/*
id a1 b1
----------- ----------- -----------
001 10 40
002 20 NULL
003 30 50
004 40 NULL
*/select a.id, a.a1, isnull(ltrim(b.b1),'')b1 from #a a left join #b b on a.id=b.id
/*
id a1 b1
----------- ----------- ------------
001 10 40
002 20
003 30 50
004 40
*/
--> 测试数据: @a
declare @a table (id varchar(3),a1 int,a2 int)
insert into @a
select '001',10,20 union all
select '002',20,30 union all
select '003',30,40 union all
select '004',40,50
--> 测试数据: @b
declare @b table (id varchar(3),b1 int,b2 int)
insert into @b
select '001',40,50 union all
select '003',50,60select a.id,a.a1,b.b1 from @a a left join @b b
on a.id=b.id
left join +isnull(col,'')