求一条 SQL 语句！谢谢！！！

[zglwxb]

入库主表GX
doc（入库单） CUS（车间主任）
rk01 0112
rk02 0215
rk06 0101
rk08 0312
rk011 0215
rk012 0203
rk013 0305
...
rk123 0112

入库细表 GN
doc（入库单） qty（入库数量） cp（成品名称）
rk01 100 dcaaa
rk01 100 fcaaa
rk02 30 dcaaa
rk02 25 zcaaa
rk02 11 dcaaa
rk03 100 zcaaa
rk03 100 dcaaa
rk05 100 zcaaa
rk05 36 fcaaa
rk13 20 dcaaa
rk13 20 fcaaa
rk15 100 dcaaa


统计车间主任的所管的车间的，生产入库数量。
其中 CUS（车间主任） 例如 0112 前面两位是车间主任编号
cp（成品名称） 例如 dcaaa 第一位是代表成品类型（d袋装产品 f 粉装产品 z 针剂产品）

我现在需要统计 车间主任 所负责的入库成品的总数量，袋装产品 数量，粉装产品 数量，针剂产品 数量 。

[-狙击手-]

入库主表GX
doc（入库单） CUS（车间主任）

入库细表 GN
doc（入库单） qty（入库数量） cp（成品名称）

select a.doc,left(cus) as cus,sum(b.入库数量) as 数量
from gx a,gn b
where a.doc = b.doc
group by a.doc,left(cus)

[rivery]

--try
select a.doc,b.cus,sum(a.qty),
dsum=sum(case left(cp,1) when 'd' then qty else 0 end),
fsum=sum(case left(cp,1) when 'f' then qty else 0 end),
zsum=sum(case left(cp,1) when 'z' then qty else 0 end)
from gn a,gx b
where a.doc=b.doc
group by a.doc,b.cus

[rivery]

declare @gx table(doc varchar(20),cus varchar(20))
insert into @gx
select 'rk01','0112' union
select 'rk02','0215' union
select 'rk06','0101' union
select 'rk08','0312' union
select 'rk011','0215' union
select 'rk012','0203' union
select 'rk013','0305'
declare @gn table(doc varchar(20),qty int,cp varchar(20))
insert into @gn
select 'rk01',100,'dcaaa' union
select 'rk01',100,'fcaaa' union
select 'rk02',30,'dcaaa' union
select 'rk02',25,'zcaaa' union
select 'rk02',11,'dcaaa' union
select 'rk03',100,'zcaaa' union
select 'rk03',100,'dcaaa' union
select 'rk05',100,'zcaaa' union
select 'rk05',36,'fcaaa' union
select 'rk03',20,'dcaaa' union
select 'rk03',20,'fcaaa' union
select 'rk05',100,'dcaaa'

select b.cus,sum(a.qty),
dsum=sum(case left(cp,1) when 'd' then qty else 0 end),
fsum=sum(case left(cp,1) when 'f' then qty else 0 end),
zsum=sum(case left(cp,1) when 'z' then qty else 0 end)
from @gn a,@gx b
where a.doc=b.doc
group by b.cus

[rivery]

--如果需要全部车间主任得，那么需要改变表链接得方式。

[zglwxb]

rivery(river)
现在就是要查全部车间主任所负责的入库量。

请各位高手指点！！！
谢谢

[zglwxb]

我还有个问题
假设查询结果是(有01.02.03三个车间主任)：
车间主任 成品类型 入库数量
01 d 200
01 f 100
02 z 18

但我想让SQL 语句的 结果显示为
01 d 200
01 f 100
01 z 0（没有生产）
02 d 0（没有生产）
02 f 0（没有生产）
02 z 18
03 d 0（没有生产）
03 f 0（没有生产）
03 z 0（没有生产）

该怎么写SQL 语句呢！
谢谢

[十一月猪]

select a.sh_di as '车间主任号码',
(select isnull(sum(zj_1),0) from
(

select sh_di,doc,left(ltrim(cp),1) as cp_1,sum(qty) as zj_1 from
(
select #temp2.qty,
#temp2.cp,
b.sh_di,b.doc
from #temp2
right outer join
(select left(ltrim(a.cus),2) as SH_DI,doc from #temp1 a group by left(ltrim(a.cus),2),doc) b
on b.doc = #temp2.doc
) d
where cp is not null
group by sh_di,doc,left(ltrim(cp),1)
) c
where cp_1 = 'd' and c.sh_di = a.sh_di) as '袋装产品',
( select isnull(sum(zj_1),0) from
(
select sh_di,doc,left(ltrim(cp),1) as cp_1,sum(qty) as zj_1 from
(
select #temp2.qty,
#temp2.cp,
b.sh_di,b.doc
from #temp2
right outer join
(select left(ltrim(a.cus),2) as SH_DI,doc from #temp1 a group by left(ltrim(a.cus),2),doc) b
on b.doc = #temp2.doc
) d
where cp is not null
group by sh_di,doc,left(ltrim(cp),1)
) c
where cp_1 = 'f' and c.sh_di = a.sh_di) as '粉装产品',
(select isnull(sum(zj_1),0) from
(
select sh_di,doc,left(ltrim(cp),1) as cp_1,sum(qty) as zj_1 from
(
select #temp2.qty,
#temp2.cp,
b.sh_di,b.doc
from #temp2
right outer join
(select left(ltrim(a.cus),2) as SH_DI,doc from #temp1 a group by left(ltrim(a.cus),2),doc) b
on b.doc = #temp2.doc
) d
where cp is not null
group by sh_di,doc,left(ltrim(cp),1)
) c
where cp_1 = 'z' and c.sh_di = a.sh_di) as '针剂产品'
from
(
select sh_di,doc,left(ltrim(cp),1) as cp_1,sum(qty) as zj_1 from
(
select #temp2.qty,
#temp2.cp,
b.sh_di,b.doc
from #temp2
right outer join
(select left(ltrim(a.cus),2) as SH_DI,doc from #temp1 a group by left(ltrim(a.cus),2),doc) b
on b.doc = #temp2.doc
) d
where cp is not null
group by sh_di,doc,left(ltrim(cp),1)
) a