表A(cname varchar(20))
SELECT cname, adds=count(*) FROM A GROUP BY cname
得到以下结果
cname adds
-----------------
abc 10
bbc 20
aaa 15
……表B(cname varchar(20))
SELECT cname, dels=count(*) FROM A GROUP BY cname
得到以下结果
cname dels
-------------------
abc 25
ccc 35
aaa 12
……
现在想把表A和表B的结果合并为
cname adds dels
----------------------
abc 10 25
bbc 20 0
aaa 15 12
ccc 0 35如何实现?
SELECT cname, adds=count(*) FROM A GROUP BY cname
得到以下结果
cname adds
-----------------
abc 10
bbc 20
aaa 15
……表B(cname varchar(20))
SELECT cname, dels=count(*) FROM A GROUP BY cname
得到以下结果
cname dels
-------------------
abc 25
ccc 35
aaa 12
……
现在想把表A和表B的结果合并为
cname adds dels
----------------------
abc 10 25
bbc 20 0
aaa 15 12
ccc 0 35如何实现?
from
(SELECT cname, adds=count(*) FROM A GROUP BY cname ) A
full outer join
(SELECT cname, dels=count(*) FROM A GROUP BY cname ) B A.name=B.name
SELECT cname, adds=count(*) FROM A GROUP BY cname
得到以下结果
cname adds
-----------------
abc 10
bbc 20
aaa 15
……
表B(cname varchar(20))
SELECT cname, dels=count(*) FROM A GROUP BY cname
得到以下结果
cname dels
-------------------
abc 25
ccc 35
aaa 12
……
from
(SELECT cname, adds=count(*) FROM A GROUP BY cname ) A
full outer join
(SELECT cname, dels=count(*) FROM A GROUP BY cname ) B on A.name=B.name
(case when A.cname=B,name then adds else 0 end) 'adds',
(case when A.cname=B,name then dels else 0 end) 'dels'
from A,B
adds=isnull(adds,0),
dels=isnull(dels,0)
from (
SELECT cname, adds=count(*) FROM A GROUP BY cname
)t1
full join (
SELECT cname, dels=count(*) FROM b GROUP BY cname
)t2
where t1.cname=t2.cname
isnull(a.cname,b.cname) as cname,a.add,b.dels
from
(SELECT cname, adds=count(*) FROM A GROUP BY cname)a
full join
(SELECT cname, dels=count(*) FROM A GROUP BY cname)b
on
a.cname=b.cname
SELECT cname, adds=count(*) FROM A GROUP BY cname 表B(cname varchar(20))
SELECT cname, dels=count(*) FROM A GROUP BY cname 现在看清楚了,是楼主误导了我.
(
cname nvarchar(30),
adds int
)
insert into #A select 'abc',10
insert into #A select 'bbc',20
insert into #A select 'aaa',15
create table #B
(
cname nvarchar(30),
dels int
)
insert into #B select 'abc',25
insert into #B select 'ccc',35
insert into #B select 'aaa',12select isnull(A.cname,B.cname),isnull(adds,0) adds,isnull(dels,0) dels
from #A A
full join #B B
on A.cname=B.cname
(
cname nvarchar(30),
adds int
)
insert into #A select 'abc',10
insert into #A select 'bbc',20
insert into #A select 'aaa',15
create table #B
(
cname nvarchar(30),
dels int
)
insert into #B select 'abc',25
insert into #B select 'ccc',35
insert into #B select 'aaa',12select isnull(A.cname,B.cname) cname,isnull(adds,0) adds,isnull(dels,0) dels
from #A A
full join #B B
on A.cname=B.cnamecname adds dels
------------------------------ ----------- -----------
abc 10 25
bbc 20 0
aaa 15 12
ccc 0 35