27,579
社区成员
发帖
与我相关
我的任务
分享
;with tbl1(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
),
tbl2 as
(
select
PRODUCT_SN, MC_CODE,
max(QUALITY_STATUS) as QUALITY_STATUS
from tbl1
group by PRODUCT_SN, MC_CODE
)
select
MC_CODE,
case max(QUALITY_STATUS) when 1 then 0 else 1 end as HGS,
count(MC_CODE) as TOTAL,
convert(nvarchar(10), convert(decimal(8,2),
(100.0 * (case max(QUALITY_STATUS) when 1 then 0 else 1 end)/count(MC_CODE)))) + '%' as HGL
from tbl2
group by MC_CODE
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
是这样子吗