求合并查询结果的SQL

[fenlin]

表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

如何实现？

[--小F--]

isnull+full join

[bancxc]

select IsNull(A.name,B.name) name,IsNull(A.adds,0) adds,IsNull(B.dels) as dels
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

[dawugui]

用full join可以得到你的结果.

不过,你的描述肯定不正确.

表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
……

[bancxc]

select IsNull(A.name,B.name) name,IsNull(A.adds,0) adds,IsNull(B.dels) as dels
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

[ws_hgo]

select isnull(A.cname,B.cname),
       (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

[jinjazzli]

select cname=isnull(t1.cname,t2.cname),
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

[dawugui]

你们再仔细看看楼主的描述,按你们的写法能得到结果吗?

[--小F--]

select
   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

[dawugui]

靠,是我看错了.是两个表.楼主这次可以完全无视我的回复.

[dawugui]

表A(cname varchar(20))
SELECT cname, adds=count(*) FROM A GROUP BY cname

表B(cname varchar(20))
SELECT cname, dels=count(*) FROM A GROUP BY cname

现在看清楚了,是楼主误导了我.

[ws_hgo]

create table #A
(
 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',12

select 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

[ws_hgo]

create table #A
(
 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',12

select 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.cname

cname                          adds        dels
------------------------------ ----------- -----------
abc                            10          25
bbc                            20          0
aaa                            15          12
ccc                            0           35

[sytdeedee]

[Quote=引用 12 楼 ws_hgo 的回复:]
SQL codecreatetable #A
(
cnamenvarchar(30),
addsint
)insertinto #Aselect'abc',10insertinto #Aselect'bbc',20insertinto #Aselect'aaa',15createtable #B
(
cnamenvarchar(30),
delsint
)insertinto #B?-
[/Quote]

up