--创建测试环境 create table a(aid int,aname varchar(10),class_id int,value int) create table b(class_id int,bname varchar(10),type varchar(10)) insert into a select 1,'1',1,11 union all select 2,'2',2,22 union all select 3,'3',1,33 union all select 4,'4',2,44 union all select 5,'5',3,100 insert into b select 1,'a','A' union all select 2,'b','B' union all select 3,'c','B' union all select 5,'e','A' --测试 select bname, [sum(value)]=isnull((select sum(value) from a where class_id=t.class_id),0), type from b t order by type,[sum(value)]drop table a,b--结果 /* bname sum(value) type ---------- ----------- ---------- e 0 A a 44 A b 66 B c 100 B(所影响的行数为 4 行) */
select b.bname,sum(isnull(value,0)) from a right join b on a.class_id=b.class_id group by b.type,b.bname
楼主的结果是:有几个type就几个结果集还是?
create table a(aid int,aname varchar(10),class_id int,value int)
create table b(class_id int,bname varchar(10),type varchar(10))
insert into a
select 1,'1',1,11 union all
select 2,'2',2,22 union all
select 3,'3',1,33 union all
select 4,'4',2,44 union all
select 5,'5',3,100
insert into b
select 1,'a','A' union all
select 2,'b','B' union all
select 3,'c','B' union all
select 5,'e','A' --测试
select bname,
[sum(value)]=isnull((select sum(value) from a where class_id=t.class_id),0),
type
from b t
order by type,[sum(value)]drop table a,b--结果
/*
bname sum(value) type
---------- ----------- ----------
e 0 A
a 44 A
b 66 B
c 100 B(所影响的行数为 4 行)
*/
from a right join b on a.class_id=b.class_id
group by b.type,b.bname