tba
name ck
a 2
b 2
c 3
d 4
e 5
f 6
g 7
tbb
name rk
c 5
h 3
i 2
j 1
tbc
name qkc
c 2
结果
name qkc rk ck now
a 0 0 2 -2
b 0 0 2 -2
c 2 5 3 4
d 0 0 4 -4
e 0 0 5 -5
f 0 0 6 -6
g 0 0 7 -7
h 0 3 0 3
i 0 2 0 2
j 0 1 0 1
name ck
a 2
b 2
c 3
d 4
e 5
f 6
g 7
tbb
name rk
c 5
h 3
i 2
j 1
tbc
name qkc
c 2
结果
name qkc rk ck now
a 0 0 2 -2
b 0 0 2 -2
c 2 5 3 4
d 0 0 4 -4
e 0 0 5 -5
f 0 0 6 -6
g 0 0 7 -7
h 0 3 0 3
i 0 2 0 2
j 0 1 0 1
INSERT INTO TBA(NAME,CK)
SELECT 'a',2
UNION ALL
SELECT 'b',2
UNION ALL
SELECT 'c',3
UNION ALL
SELECT 'd',4
UNION ALL
SELECT 'e',5
UNION ALL
SELECT 'f',6
UNION ALL
SELECT 'g',7 create table tbb(name varchar(4),rk int)
insert into tbb(name,rk)
select 'h',5
UNION ALL
SELECT 'i',3
UNION ALL
SELECT 'j',2
UNION ALL
SELECT 'k',1
create table tbc(name varchar(4),qkc int)
insert into tbc(name,qkc)
select 'l',2
-----------------
;WITH
DD AS(
SELECT name FROM tbb
union all
SELECT name FROM TBA
union all
SELECT name FROM tbC
)
SELECT DD.*,ISNULL(A.qkc,0) QKC,ISNULL(B.rk,0) RK,ISNULL(C.CK,0) CK,
(ISNULL(A.qkc,0)+ISNULL(B.rk,0)-ISNULL(C.CK,0)) AS RESULT
FROM DD LEFT JOIN tbc A
ON DD.name=A.name
LEFT JOIN tbb B
ON DD.name=B.name
LEFT JOIN TBA C
ON DD.name=C.NAME
-----
DROP TABLE TBA,TBB,TBC
/*
name QKC RK CK RESULT
---- ----------- ----------- ----------- -----------
h 0 5 0 5
i 0 3 0 3
j 0 2 0 2
k 0 1 0 1
a 0 0 2 -2
b 0 0 2 -2
c 0 0 3 -3
d 0 0 4 -4
e 0 0 5 -5
f 0 0 6 -6
g 0 0 7 -7
l 2 0 0 2(12 行受影响)
DD.*, ISNULL(A.qkc, 0) AS QKC, ISNULL(B.rk, 0) AS RK, ISNULL(C.ck, 0) AS CK,
ISNULL(A.qkc, 0) + ISNULL(B.rk, 0) - ISNULL(C.ck, 0) AS RESULT
FROM (SELECT name
FROM tbb
UNION ALL
SELECT name
FROM TBA
UNION ALL
SELECT name
FROM tbC) DD LEFT OUTER JOIN
tbc A ON DD.name = A.name LEFT OUTER JOIN
tbb B ON DD.name = B.name LEFT OUTER JOIN
tba C ON DD.name = C.name a 0 0 2 -2
b 0 0 2 -2
c 2 5 3 4
d 0 0 4 -4
e 0 0 5 -5
f 0 0 6 -6
g 0 0 7 -7
h 0 3 0 3
i 0 2 0 2
j 0 1 0 1
select tb.name,
decode(qkc, null, 0, qkc) as qkc,
decode(rk, null, 0, rk) as rk,
decode(ck, null, 0, ck) as ck,
(decode(qkc, null, 0, qkc) + decode(rk, null, 0, rk) -decode(ck, null, 0, ck)) as now
from (select name
from tba
union
select name
from tbb
union
select name from tbc) tb
left join tba on tb.name = tba.name
left join tbb on tb.name = tbb.name
left join tbc on tb.name = tbc.name
order by tb.name;-----
(0-isnull(d.qkc,0)+isnull(c.rk,0)+isnull(b.ck,0)) as [now]
from
(
select name from tba union all
select name from tbb union all
select name from tbc
) a
left join tba b on a.name=b.name
left join tbb c on a.name=c.name
left join tbc d on a.name=d.name
) b
create table tba(
name varchar(10),
ck int
)
goinsert into tba(name,ck)
select 'a',2
union all select 'b',2
union all select 'c',3
union all select 'd',4
union all select 'e',5
union all select 'f',6
union all select 'g',7
gocreate table tbb(
name varchar(10),
rk int
)
goinsert into tbb(name,rk)
select 'c',5
union all select 'h',3
union all select 'i',2
union all select 'j',1
gocreate table tbc(
name varchar(10),
qkc int
)
goinsert into tbc(name,qkc)
select 'c',2
go/*
select * from tba
select * from tbb
select * from tbc
*/--查询方式
select t.name,
qkc=isnull(qkc,0),
rk=isnull(rk,0),
ck=isnull(ck,0),
now=isnull(qkc,0)+isnull(rk,0)-isnull(ck,0)
from (
select name from tba
union
select name from tbb
union
select name from tbc
) t
left join tba on t.name = tba.name
left join tbb on t.name = tbb.name
left join tbc on t.name = tbc.name
order by t.name
godrop table tba,tbb,tbc
go/*
a 0 0 2 -2
b 0 0 2 -2
c 2 5 3 4
d 0 0 4 -4
e 0 0 5 -5
f 0 0 6 -6
g 0 0 7 -7
h 0 3 0 3
i 0 2 0 2
j 0 1 0 1
isnull(tbc.qkc,0) as qkc,
isnull(tbb.rk,0) as rk,
isnull(tba.ck,0) as ck,
isnull(tbc.qkc,0)+isnull(tbb.rk,0)-isnull(tba.ck,0) as now
from tba full join tbb
on tba.name=tbb.name full join tbc
on tbc.name=tbb.name