ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 好長的一條SQL啊!

好長的一條SQL啊!

原创 Linux操作系统 作者:ginni_hua 时间:2008-12-23 17:31:28 0 删除 编辑

一條SQL,認為值得記錄下來: 

select scrapdtl011,
       lpad(to_char(c1),
2, '0') || defectname defectname,
       area,
       rate,
       rate1
  from (select rownum c1, scrapdtl011, defectname, area, rate, rate1
          from (select scrapdtl011,
                       defectname,
                       area,
                       rate *
100 rate,
                    
  sum(rate * 100) over(partition by 1 order by rate desc, scrapdtl011) rate1
                  from (select scrd.scrapdtl011,
                               defc.defcode002 defectname,
                               sum(round((scrd.SCRAPDTL009 *
                                         decode(scrd.scrapdtl008,
                                                
99,
                                                 
1,
                                                 decode(nvl(r.pnumrt007,
0),
                                                       
0,
                                                       
1,
                                                        r.pnumrt007)) -
                                         nvl(scrd.scrapdtl023,
0)) *
                                         round(e.EngMain008 * e.EngMain009,
6) /
                                         e.engmain011,
                                         
6)) / 144 area,
                              
ratio_to_report(sum(round((scrd.SCRAPDTL009 *
                                                         decode(scrd.scrapdtl008,
                                                                 99,
                                                                 1,
                                                                 decode(nvl(r.pnumrt007,
                                                                            0),
                                                                        0,
                                                                        1,
                                                                        r.pnumrt007)) -
                                                         nvl(scrd.scrapdtl023,
                                                              0)) *
                                                         round(e.EngMain008 *
                                                               e.EngMain009,
                                                               6) /
                                                         e.engmain011,
                                                         6))) over(partition by 1) rate

                          from (select scrd.scrapdtl004,
                                       scrd.scrapdtl005,
                                       scrd.scrapdtl006,
                                       scrd.scrapdtl007,
                                       scrd.scrapdtl008,
                                       scrd.scrapdtl011,
                                       scrd.scrapdtl013,
                                       scrd.scrapdtl021,
                                       scrd.scrapdtl009,
                                       scrm.confdate,
                                       scrd.scrapdtl023
                                  from qcmscrapbas scrm, qcmscrapdtl scrd
                                 where scrm.paperno = scrd.paperno
                                   and scrm.scrapbas009 =
'1'
                                union all
                                select d.scrapxdtl004,
                                       d.scrapxdtl005,
                                       d.scrapxdtl006,
                                       d.scrapxdtl007,
                                       d.scrapxdtl008,
                                       d.scrapxdtl012,
                                       d.scrapxdtl013,
                                       d.scrapxdtl017,
                                       d.scrapxdtl011,
                                       m.confdate,
                                      
0 scrapxqty
                                  from qcmscrapx m, qcmscrapxdtl d
                                 where m.paperno = d.paperno
                                   and m.scrapx006 =
'1') scrd,
                               edmengmain e,
                               edmprodbom d,
                               QCMDefCode defc,
                               pmmlotinfo l,
                               pmmprocbasic b,
                               qcmdefsort qds,
                               qcmdept qd,
                               (select partnum,
                                       revision,
                                       layer,
                                       pnumrt005,
                                       max(pnumrt007) pnumrt007
                                  from edmpnumrt
                                 group by partnum, revision, layer, pnumrt005) r
                         where scrd.SCRAPDTL005 = d.partnum
                           and scrd.SCRAPDTL006 = d.revision
                           and scrd.SCRAPDTL007 = d.layer
                           and d.prodbom016 =
1
                           and scrd.scrapdtl005 = r.partnum(+)
                           and scrd.scrapdtl006 = r.revision(+)
                           and scrd.scrapdtl007 = r.layer(+)
                           and scrd.scrapdtl008 = r.pnumrt005(+)
                           and scrd.scrapdtl005 = e.partnum
                           and scrd.scrapdtl006 = e.revision
                           and scrd.scrapdtl007 = e.layer
                           and scrd.SCRAPDTL011 = defc.defectcode
                           and defc.sortcode = qds.sortcode
                           and qds.defsort002 = qd.deptno
                           and scrd.scrapdtl013 = b.proccode
                           and scrd.scrapdtl021 = b.proclineid
                           and scrd.scrapdtl004 = l.lotnum
                           and scrd.scrapdtl007 = l.layer
                           and scrd.confdate between
'20080801' and '20080902'
                         group by scrd.scrapdtl011, defc.defcode002
                         order by area desc)))
 where c1 <
11
 order by defectname

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7836221/viewspace-520983/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 隨想
请登录后发表评论 登录
全部评论

注册时间:2008-03-20

  • 博文量
    172
  • 访问量
    361203