select a.Npkh,Ye=isnull(b.Ye,0) from A表 a left join B表 b on a.Npkh=b.Npkh
现在再加一个C表, Npkh Je B123 23而显示结果如下: Npkh Ye Je A123 12.23 0 B123 0 23则SQL要如何写?
select a.Npkh,Ye=isnull(b.Ye,0),Je=IsNULL(c.Je,0) from A表 a Left join B表 b On a.Npkh=b.Npkh Left join C表 c on a.Npkh=c.Npkh
create table m1(a varchar(10)) insert m1 select 'A123' union all select 'B123' go create table m2(a varchar(10),b int) insert m2 select 'A123',20 go select m1.a,b=isnull(m2.b,0) from m1 left join m2 on m1.a=m2.a
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temp1] goif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temp2] gocreate table temp1(bh varchar(20) not null) create table temp2(bh varchar(20) not null,mc varchar(20) null) goinsert into temp1 select 'Npkh' union select 'A123' union select 'B123' insert into temp2 select 'Npkh','Ye' union select 'A123','12.32'
goselect * from temp1 order by 1 /* bh A123 B123 Npkh */select * from temp2 /* A123 12.32 Npkh Ye */select a.bh,isnull(b.mc,'0') as mc from temp1 a left join temp2 b on a.bh=b.bh /* bh mc B123 0 Npkh Ye A123 12.32 */
不好意思,上面寫錯了. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temp1] goif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temp2] gocreate table temp1(Npkh varchar(20) not null) create table temp2(Npkh varchar(20) not null,Ye float null) goinsert into temp1 select 'A123' union select 'B123' insert into temp2 select 'A123','12.32'
goselect * from temp1 order by 1 /* Npkh A123 B123 */select * from temp2 /* Npkh Ye A123 12.32 */select a.Npkh,isnull(b.Ye,'0') as Ye from temp1 a left join temp2 b on a.Npkh=b.Npkh order by 1 /* Npkh Ye A123 12.32 B123 0.0 */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temp1] goif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temp2] gocreate table temp1(Npkh varchar(20) not null) create table temp2(Npkh varchar(20) not null,Ye float null) goinsert into temp1 select 'A123' union select 'B123' insert into temp2 select 'A123','12.32'
goselect * from temp1 order by 1 /* Npkh A123 B123 */select * from temp2 /* Npkh Ye A123 12.32 */select a.Npkh,isnull(cast(b.Ye as varchar(15)),'0') as Ye from temp1 a left join temp2 b on a.Npkh=b.Npkh order by 1 /* Npkh Ye A123 12.32 B123 0 */
from A表 a left join B表 b
on a.Npkh=b.Npkh
Npkh Je
B123 23而显示结果如下:
Npkh Ye Je
A123 12.23 0
B123 0 23则SQL要如何写?
from A表 a
Left join B表 b On a.Npkh=b.Npkh
Left join C表 c on a.Npkh=c.Npkh
insert m1 select 'A123'
union all select 'B123'
go
create table m2(a varchar(10),b int)
insert m2 select 'A123',20
go
select m1.a,b=isnull(m2.b,0) from m1 left join m2 on m1.a=m2.a
drop table [dbo].[temp1]
goif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[temp2]
gocreate table temp1(bh varchar(20) not null)
create table temp2(bh varchar(20) not null,mc varchar(20) null)
goinsert into temp1
select 'Npkh' union
select 'A123' union
select 'B123' insert into temp2
select 'Npkh','Ye' union
select 'A123','12.32'
goselect * from temp1 order by 1
/*
bh
A123
B123
Npkh
*/select * from temp2
/*
A123 12.32
Npkh Ye
*/select a.bh,isnull(b.mc,'0') as mc from temp1 a left join temp2 b on a.bh=b.bh
/*
bh mc
B123 0
Npkh Ye
A123 12.32
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[temp1]
goif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[temp2]
gocreate table temp1(Npkh varchar(20) not null)
create table temp2(Npkh varchar(20) not null,Ye float null)
goinsert into temp1
select 'A123' union
select 'B123' insert into temp2
select 'A123','12.32'
goselect * from temp1 order by 1
/*
Npkh
A123
B123
*/select * from temp2
/*
Npkh Ye
A123 12.32
*/select a.Npkh,isnull(b.Ye,'0') as Ye from temp1 a left join temp2 b on a.Npkh=b.Npkh order by 1
/*
Npkh Ye
A123 12.32
B123 0.0
*/
drop table [dbo].[temp1]
goif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[temp2]
gocreate table temp1(Npkh varchar(20) not null)
create table temp2(Npkh varchar(20) not null,Ye float null)
goinsert into temp1
select 'A123' union
select 'B123' insert into temp2
select 'A123','12.32'
goselect * from temp1 order by 1
/*
Npkh
A123
B123
*/select * from temp2
/*
Npkh Ye
A123 12.32
*/select a.Npkh,isnull(cast(b.Ye as varchar(15)),'0') as Ye from temp1 a left join temp2 b on a.Npkh=b.Npkh order by 1
/*
Npkh Ye
A123 12.32
B123 0
*/