select 字段 from a with(nolock) union select 字段 from b with(nolock)Null——>0 case when 字段 is null then 0 end
不行,有错误,还有你这里没有cy=sp-kc合并成这个。
-->生成表aif object_id('a') is not null drop table a Go Create table a([id] smallint,[tm] nvarchar(2),[sp] smallint,[kc] nvarchar(50),[cy] nvarchar(50),[danh] nvarchar(2)) Insert into a Select 1,N'02',2,null,null,N'aa' Union all Select 2,N'01',3,null,null,N'aa' Union all Select 3,N'03',2,null,null,N'aa' Union all Select 4,N'05',4,null,null,N'bb' Union all Select 5,N'10',3,null,null,N'cc'-->生成表bif object_id('b') is not null drop table b Go Create table b([id] smallint,[tm] nvarchar(2),[sp] nvarchar(50),[kc] smallint,[cy] nvarchar(50),[danh] nvarchar(2)) Insert into b Select 1,N'02',null,1,null,N'aa' Union all Select 2,N'01',null,2,null,N'aa' Union all Select 3,N'10',null,3,null,N'dd' select ISNULL(a.tm,b.tm) AS id ,COALESCE(a.sp,b.sp,0) AS sp ,COALESCE(a.kc,b.kc,0) AS kc ,COALESCE(a.sp,b.sp,0)-COALESCE(a.kc,b.kc,0) AS cy ,ISNULL(a.danh,b.danh) AS danh from a FULL JOIN b ON a.tm=b.tm AND a.danh=b.danh WHERE ISNULL(a.danh,b.danh)='aa' /* id sp kc cy danh ---- ----------- ----------- ----------- ---- 02 2 1 1 aa 01 3 2 1 aa 03 2 0 2 aa */ Go
select a.tm tm,a.sp sp,Isnull(b.kc,0) kc,a.sp-Isnull(b.kc,0) cy,a.danh danh from a left join b on a.tm=b.tm where a.danh='aa'
select ISNULL(a.tm,b.tm) AS id ,COALESCE(a.sp,b.sp,0) AS sp ,COALESCE(a.kc,b.kc,0) AS kc ,COALESCE(a.sp,b.sp,0)-COALESCE(a.kc,b.kc,0) AS cy ,ISNULL(a.danh,b.danh) AS danh from a FULL JOIN b ON a.tm=b.tm AND a.danh=b.danh WHERE ISNULL(a.danh,b.danh)='aa'这是3楼那位兄弟做出来的一种方法。可以实现了。 我看看海有其他朋友用其他的方法不,真的学习了。
union
select 字段 from b with(nolock)Null——>0
case when 字段 is null then 0 end
drop table a
Go
Create table a([id] smallint,[tm] nvarchar(2),[sp] smallint,[kc] nvarchar(50),[cy] nvarchar(50),[danh] nvarchar(2))
Insert into a
Select 1,N'02',2,null,null,N'aa'
Union all Select 2,N'01',3,null,null,N'aa'
Union all Select 3,N'03',2,null,null,N'aa'
Union all Select 4,N'05',4,null,null,N'bb'
Union all Select 5,N'10',3,null,null,N'cc'-->生成表bif object_id('b') is not null
drop table b
Go
Create table b([id] smallint,[tm] nvarchar(2),[sp] nvarchar(50),[kc] smallint,[cy] nvarchar(50),[danh] nvarchar(2))
Insert into b
Select 1,N'02',null,1,null,N'aa'
Union all Select 2,N'01',null,2,null,N'aa'
Union all Select 3,N'10',null,3,null,N'dd'
select
ISNULL(a.tm,b.tm) AS id
,COALESCE(a.sp,b.sp,0) AS sp
,COALESCE(a.kc,b.kc,0) AS kc
,COALESCE(a.sp,b.sp,0)-COALESCE(a.kc,b.kc,0) AS cy
,ISNULL(a.danh,b.danh) AS danh
from a
FULL JOIN b ON a.tm=b.tm AND a.danh=b.danh
WHERE ISNULL(a.danh,b.danh)='aa'
/*
id sp kc cy danh
---- ----------- ----------- ----------- ----
02 2 1 1 aa
01 3 2 1 aa
03 2 0 2 aa
*/
Go
from a
left join b
on a.tm=b.tm
where a.danh='aa'
ISNULL(a.tm,b.tm) AS id
,COALESCE(a.sp,b.sp,0) AS sp
,COALESCE(a.kc,b.kc,0) AS kc
,COALESCE(a.sp,b.sp,0)-COALESCE(a.kc,b.kc,0) AS cy
,ISNULL(a.danh,b.danh) AS danh
from a
FULL JOIN b ON a.tm=b.tm AND a.danh=b.danh
WHERE ISNULL(a.danh,b.danh)='aa'这是3楼那位兄弟做出来的一种方法。可以实现了。
我看看海有其他朋友用其他的方法不,真的学习了。