//试试
select a.部门,c.类别,sum(price) as price
from Dept a
left join Orders b
on a.部门=b.部门
inner join Class c
on b.cid=c.cid
group by a.部门,c.类别
select a.部门,c.类别,sum(price) as price
from Dept a
left join Orders b
on a.部门=b.部门
inner join Class c
on b.cid=c.cid
group by a.部门,c.类别
to: xw32designer,Oders表中的[部门]对应Dept表中的[部门],Order表中的cid对应Class表中的cid另外,这个功能我已经用存储过程的游标嵌套法实现了,先建一个目标临时表,然后一个个循环添加进这个临时表,但很笨。
不管有没有答案,星期一晚上结贴!按字数多少给分^_^
from 表 a
group by 部门,类别
select 部门,类别,isnull((select sum(price) from Orders where 部门=a.部门 and cid=(select cid from class where 类别=a.类别)),0) as price
from (
select 部门,'基建' as 类别
from Dept
union all
select 部门,'生产' as 类别) a
--这样子Class有点象鸡肋了
from dept join class on 1=1 left join orders on orders.cid=class.cid and dept.部门=orders.部门
group by dept.部门,class.类别,dept.did
order by dept.did
这个可以实现你的要求
以下是测试:
create table dept(did int,部门 varchar(50))
create table orders(ord int,price money,部门 varchar(50),cid int)
create table classs(cid int,类别 varchar(50))insert into dept values(1,'工程部')
insert into dept values(2,'运行部')
insert into dept values(3,'办公室')insert into class values(1,'基建')
insert into class values(2,'生产')insert into orders values(1,7743.60,'工程部',2)
insert into orders values(2,104.88,'运行部',2)
不过还是建议你将dept和class关联一下,在dept中加入cid