质量表中各类装配件检验结果分类汇总SQL

      质量结果表中存储了各个部门对生产结果的录入信息，经常需要使用这些信息做生产统计。下面语句针对单一的质量表中信息进行分类汇总，虽然看似简单，包含的内容却非常丰富，语句中用到的分类汇总方法，作个简单演示，抛砖引玉，便于以后更多报表中常用到这种方式。

 

select qr.character20 工序代码,
       bso.operation_description 工序说明,
       msi.segment1 装配件编码,
       msi.description 装配件说明,
       sum(decode(qr.character6, '合格', qr.character7, 0)) 合格数量,
       sum(decode(qr.character6, '废品', qr.character7, 0)) 废品数量,
       sum(decode(qr.character6, '次品', qr.character7, 0)) 次品数量,
       sum(decode(qr.character6, '返工', qr.character7, 0)) 返工数量,
       sum(decode(qr.character6, '返工合格', qr.character7, 0)) 返工合格数,
       sum(decode(qr.character6, '亚合格', 0, '返工', 0, qr.character7)) 总数
  from qa_results qr, bom_standard_operations bso, mtl_system_items_b msi
 where qr.plan_id = 100
   and qr.organization_id = 103
      --and qr.qa_creation_date >= trunc(:p_date_start)
      --and qr.qa_creation_date < trunc(:p_date_end + 1)
   and qr.qa_creation_date >= trunc(sysdate - 10)
   and qr.qa_creation_date < trunc(sysdate + 1)
   and qr.character20 in ('JHC1', 'JJY1', 'JMC1', 'JMC2', 'JMC3', 'JPG1', 'JXQ1', 'JPH1',
                                     'JXQC', 'JPS1', 'JSCP', 'JSJP', 'JZJP')
      
   and qr.character20 = bso.operation_code
   and bso.organization_id = 103
   and msi.organization_id = 103
   and msi.inventory_item_id = qr.item_id
 group by msi.segment1,
          msi.description,
          qr.character20,
          bso.operation_description