有两张表:
表ab
col1 col2
---------- -----------
a 10
b 10
c 10
d 10
e 10表cd
col1 col2
---------- -----------
b 10
c 8
d 10
f 10
g 5
h 5我现在要的结果是:
col1 差量
---------- -----------
a 10
b 0
c 2
d 0
e 10
f -10
g -5
h -5就是说要col列相减。
我这样写:
select ab.col1,isnull(ab.col2,0)-isnull(cd.col2,0) 差量
from ab full join cd on ab.col1=cd.col1
col1 差量
---------- -----------
b 0
c 2
d 0
NULL -10
NULL -5
NULL -5
a 10
e 10(所影响的行数为 8 行)
为什么会出现NULL呢?正确的SQL该怎样写?
表ab
col1 col2
---------- -----------
a 10
b 10
c 10
d 10
e 10表cd
col1 col2
---------- -----------
b 10
c 8
d 10
f 10
g 5
h 5我现在要的结果是:
col1 差量
---------- -----------
a 10
b 0
c 2
d 0
e 10
f -10
g -5
h -5就是说要col列相减。
我这样写:
select ab.col1,isnull(ab.col2,0)-isnull(cd.col2,0) 差量
from ab full join cd on ab.col1=cd.col1
col1 差量
---------- -----------
b 0
c 2
d 0
NULL -10
NULL -5
NULL -5
a 10
e 10(所影响的行数为 8 行)
为什么会出现NULL呢?正确的SQL该怎样写?
from ab full join cd on ab.col1=cd.col1
from ab full join cd on ab.col1=cd.col1
from ab full join cd on ab.col1=cd.col1
from ab full join cd on ab.col1=cd.col1
isnull(a.col2,0)-isnull(b.col2,0) as col2
from a
full join b
on a.col1=b.col1
col1=isnull(ab.col1,cd.col1),
差量=isnull(ab.col2,0)-isnull(cd.col2,0)
from ab full join cd
on ab.col1=cd.col1
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-06-17 09:50:44
----------------------------------------------------------------
--> 测试数据:[ab]
if object_id('[ab]') is not null drop table [ab]
create table [ab]([col1] varchar(1),[col2] int)
insert [ab]
select 'a',10 union all
select 'b',10 union all
select 'c',10 union all
select 'd',10 union all
select 'e',10
--> 测试数据:[cd]
if object_id('[cd]') is not null drop table [cd]
create table [cd]([col1] varchar(1),[col2] int)
insert [cd]
select 'b',10 union all
select 'c',8 union all
select 'd',10 union all
select 'f',10 union all
select 'g',5 union all
select 'h',5
--------------开始查询--------------------------
select isnull(ab.col1,cd.col1) as col1,isnull(ab.col2,0)-isnull(cd.col2,0) 差量
from ab full join cd on ab.col1=cd.col1 order by col1 ----------------结果----------------------------
/*
col1 差量
a 10
b 0
c 2
d 0
e 10
f -10
g -5
h -5*/
INSERT @a SELECT 'a', 10
union all select 'b', 10
union all select 'c', 10
union all select 'd', 10
union all select 'e', 10
declare @b table(col1 VARCHAR(20), col2 INT)
insert @b select 'b', 10
union all select 'c', 8
union all select 'd', 10
union all select 'f', 10
union all select 'g' , 5
union all select 'h', 5SELECT COALESCE(a.col1,b.col1) col1,ISNULL(a.col2,0)-ISNULL(b.col2,0) 差量
FROM @a a full join @b b
ON a.col1=b.col1
ORDER BY 1
/*col1 差量
-------------------- -----------
a 10
b 0
c 2
d 0
e 10
f -10
g -5
h -5(所影响的行数为 8 行)*/
insert ab values('a', 10)
insert ab values('b', 10)
insert ab values('c', 10)
insert ab values('d', 10)
insert ab values('e', 10)create table cd(col1 varchar(2),col2 int)
insert cd values('b', 10)
insert cd values('c', 8)
insert cd values('d', 10)
insert cd values('f', 10)
insert cd values('g', 5)
insert cd values('h', 5)
select isnull(a.col1,b.col1) col1 ,isnull(a.col2,0)-isnull(b.col2,0) as col2 from ab a full join cd b on a.col1 = b.col1
/*
col1 col2
---- -----------
a 10
b 0
c 2
d 0
e 10
f -10
g -5
h -5(8 row(s) affected)
*/
create table [xx]([col1] varchar(1),[col2] int)
insert [xx]
select 'a',10 union all
select 'b',10 union all
select 'c',10 union all
select 'd',10 union all
select 'e',10
create table [yy]([col1] varchar(1),[col2] int)
insert [yy]
select 'b',10 union all
select 'c',8 union all
select 'd',10 union all
select 'f',10 union all
select 'g',5 union all
select 'h',5
select isnull(a.col1,b.col1) as a,isnull(a.col2,0)-isnull(b.col2,0) as b
from xx a full join yy b on (a.col1=b.col1)
order by a
---- -----------
a 10
b 0
c 2
d 0
e 10
f -10
g -5
h -5(所影响的行数为 8 行)