★★★★★★★★高手请进★★★★★★★★

[ju_feng]

有一个企业字典表（存储企业信息）（ｅｄ＿ｎｏ，ｅｄ＿ｎａｍｅ）
一个产品字典表（存储产品信息）（ｐｄ＿ｎｏ，ｐｄ＿ｎａｍｅ）
产品生产数据表（存储每个企业生产产品的数量）（ｅｄ＿ｎｏ，ｐｄ＿ｎｏ，
ｍｏｎｔｈ，ｐｄ＿ｎｕｍ）
我想列出所有企业在一个月份生产某一种产品的数量，如果该企业不生产该产品则生产数量为０

请问怎么实现啊！

[heixiu1980]

select a.ed_name,b.pd_name,
pd_num=case when c.pd_num is null then 0 else c.pd_num end
from 企业表 a
left join
产品表 b on pd_name=...--某种产品
left join
生产表 c on
a.ed_no=c.ed_no and b.pd_no=c.pd_no
and month=...--月份

[HawaiiLeo]

select sum(pd_num) from 产品生产数据表 C
inner join 产品字典表 B on C.pd_no = B.pd_no
inner join 企业字典表 A on C.ed_no = A.ed_no
where pd_name='产品名' and month='3'

[pengdali]

select a.ed_name,a.pd_name,isnull(pd_num,0) pd_num from (select * from 企业字典表 ,产品字典表) a left join 产品生产数据表 b on b.month=10 and a.ed_no=b.ed_no and a.pd_no=b.pd_no

[HawaiiLeo]

select sum(IsNull(pd_num,0)) from 产品生产数据表 C
inner join 产品字典表 B on C.pd_no = B.pd_no
inner join 企业字典表 A on C.ed_no = A.ed_no
where pd_name='产品名' and month='3'

[pengdali]

select a.ed_name,a.pd_name,isnull(pd_num,0) pd_num from (select * from 企业字典表 ,产品字典表) a left join (select ed_no,pd_no,sum(pd_num) pd_num from 产品生产数据表 where month=10 group by ed_no,pd_no) b on a.ed_no=b.ed_no and a.pd_no=b.pd_no

[psxfghost]

select a.ed_name, isnull(b.pd_num,0) from 企业字典表 as a left join 产品生产数据表 as b on a.ed_no = b.ed_no and b.month=10
^_^

[fjw2002]

select isnull(sum(ｐｄ＿ｎｕｍ),0) from 产品生产数据表 where ｍｏｎｔｈ='月份' and ｐｄ＿ｎｏ='产品号'

[caiyunxia]

left join

[playyuer]

select ed_no,pd_no,max([month]),sum(pd_num)
from 企业字典表 a
left join 产品生产数据表 b on a.ed_no =b.ed_no
left join 产品字典表 c on b.pd_no =c.pd_no
group by ed_no,pd_no,datediff(month,[month],getdate())

[caiyunxia]

应该还有日期字段把

[愉快的登山者]

select A.ed_name, isnull(B.pd_num, 0)
from 企业字典表 as A
left join 产品生产数据表 as B on A.ed_no = B.ed_no
left join 产品字典表 as C on B.pd_no = C.pd_no
where B.month = @xmonth
and C.ed_name = @xed_name
order by A.ed_name