表1
PRODUCT_SN MC_CODE OP_CODE QUALITY_STATUS
20150101 A 001 0
20150102 A 001 0
20150103 A 001 0
20150101 B 002 1
20150102 B 002 0
20150102 B 003 1
20150102 C 004 1
注：PRODUCT_SN--产品序列号；MC_CODE--物料号；OP_CODE--工序号；QUALITY_STATUS--质量状态（0代表合格，1代表不合格，只要一道工序的QUALITY_STATUS=1就代表该产品不合格）
要统计的结果如下（按照物料号分类统计）：
MC_CODE HGS TOTAL HGL
A 1 3 33.33%
B 1 2 50.00%
C 0 1 0.00%
注：HGS--合格数 ； TOTAL--总数；HGL--合格率
如何写出该统计的存储过程求大神帮忙，尤其合格数怎么统计出来，谢谢！
------解决思路----------------------
with tb(PRODUCT_SN,MC_CODE,OP_CODE,QUALITY_STATUS) as
(
select '20150101', 'A', '001',0 union all
select '20150102', 'A', '001',0 union all
select '20150103', 'A', '001',0 union all
select '20150101', 'B', '002',1 union all
select '20150102', 'B', '002',0 union all
select '20150102', 'B', '003',1 union all
select '20150102', 'C', '004',1 union all
select '20150104', 'A', '002',0 union all
select '20150104', 'A', '003',0 union all
select '20150104', 'A', '004',0
),
tb1 as
(
select *,ROW_NUMBER()over(PARTITION by PRODUCT_SN,MC_CODE order by MC_CODE)rn from tb
),
tb2 as
(
select PRODUCT_SN,MC_CODE from tb where PRODUCT_SN not in (select PRODUCT_SN from tb where QUALITY_STATUS=1) group by PRODUCT_SN,MC_CODE
)
select a.MC_CODE,
COUNT(distinct b.PRODUCT_SN)HGS,
count(a.rn)Total,
convert(nvarchar(10),convert(decimal(18,2),convert(decimal(18,2),COUNT(distinct b.PRODUCT_SN))/convert(decimal(18,2),count(a.rn))*100))+'%' HGL from tb1 a
left join tb2 b on a.MC_CODE=b.MC_CODE and a.PRODUCT_SN=b.PRODUCT_SN
where a.rn=1
group by a.MC_CODE
是这样子吗