ORACLE

记录一下TIPTOP

#16
回复 yaner 2015-01-16, 8:28 PM
select ima01,ima02,obk03 from obk_file,ima_file where ima01=obk01 and ima02<>obk03
#17
回复 yaner 2015-01-16, 8:47 PM
select unique ecm01 from ecm_file where ecm53 ='Y'
#18
回复 yaner 2015-01-17, 1:50 PM
select shb05 as 工单号,shb081 as 作业编号,ecm45 as 作业名称,shb02 as 开工日,shb03 as 报工日,sum(shb115) as 超量报工,sum(shb113) as 废品数,shbdate as 审核日期 from shb_file,ecm_file where shb081=ecm04
group by shb05,shb081,ecm45,shb02,shb03,shbdate
order by shb05,shb081,ecm45,shb02,shb03,shbdate
#19
回复 yaner 2015-01-19, 8:26 PM
select ecm01 as 工单单号,ecm03_par as 料号,ima02 as 品名,ecm45 as 工序,ecm65 as 标准产量,ecm301 as 良品转入,
         ecm311 as 良品转出,ecm315 as 超量报工,ecm313 as 报废数量,ecm301-ecm311-ecm315-ecm313 as WIP量,ecm66 from ecm_file,ima_file
where ecm03_par = ima01
     and ecm04 in ('A001','A002','A006')
order by ecm04,ecm03_par
#20
回复 yaner 2015-01-21, 5:56 PM
PLSQL乱码
增加一个环境变量
NLS_LANG
select userenv('language')from dual
#21
回复 yaner 2015-01-21, 5:57 PM
ecm03_par ,ima02 ,ecm04,ecm45 ,sum(ecm65),sum(ecm301) ,
         sum(ecm311+ecm315) ,sum(ecm313),sum(ecm301-ecm311-ecm315-ecm313) from ecm_file,ima_file
where ecm03_par = ima01 and ecm04='A006'
    group by ecm03_par,ima02,ecm04,ecm45



select shb04 as 人员,shb05 as 工单,shb111 as 报工量,shb115 as 超报量 from shb_file where shb04='A0066' and  shb05 like 'A51%' and shb081='A002'
#22
回复 yaner 2015-01-23, 9:57 AM
update hy01.ima_file set ima56=0,ima561=0,ima562=0,ima64=0,ima641=0 ;
update sl01.ima_file set ima56=0,ima561=0,ima562=0,ima64=0,ima641=0 ;
update hw99.ima_file set ima56=0,ima561=0,ima562=0,ima64=0,ima641=0 ;
update hw01.ima_file set ima56=0,ima561=0,ima562=0,ima64=0,ima641=0
#23
回复 yaner 2015-01-23, 2:15 PM
select ecm03_par ,ima02 ,ecm04,ecm45 ,sum(ecm65),sum(ecm301) ,
         sum(ecm311+ecm315) ,sum(ecm313),round(sum(ecm313/(ecm311+ecm315+ecm313)*100),2) from ecm_file,ima_file
where ecm03_par = ima01 and ecm311+ecm315+ecm313>0
    group by ecm03_par,ima02,ecm04,ecm45
#24
回复 yaner 2015-01-23, 3:19 PM
---检查开帐库存大于0的资料
select img01,img02,img03,img04,img10 from hw01.img_file where img02 like '%999' and img10>0
union
select img01,img02,img03,img04,img10 from sl01.img_file where img02 like '%999' and img10>0
union
select img01,img02,img03,img04,img10 from hy01.img_file where img02 like '%999' and img10>0
#25
回复 yaner 2015-01-26, 11:25 AM
select img01,img04,img37 from img_file where img01 like 'C851021' and img04 like '150108%' and img37 = to_date('2015/01/23','YYYY/mm/dd')

update img_file set img37 = to_date('2015/01/28','YYYY/mm/dd')
where img01 like 'C851021' and img04 like '150108%' and img37 = to_date('2015/01/23','YYYY/mm/dd')
#26
回复 yaner 2015-01-26, 3:38 PM
select shb10 as 产品代号,ima02 as 产品名称,shb082 as 工序名,shc04 as 不良代码,qce03 as 不良原因,sum(shc05) as 不良数量
,shb05 as 工单号  from shc_file,qce_file,ecm_file,shb_file,ima_file
where ecm04=shb081 and qce01=shc04 and shb01=shc01 and ecm03_par=shb10 and shb05=ecm01 and ima01=shb10

group by shb10,shb05,qce03,shc04,shb082,ima02
#27
回复 yaner 2015-02-05, 11:25 AM
select a.ima01 from
(select ima01 from ima_file where ima01 like '50%') a
where a.ima01 like '%E%'  --多重查询
#28
回复 yaner 2015-02-05, 3:50 PM
--不良率横向SQL
select unique ashb_file.shb10,a20001.shc05,a20002.shc05,a20003.shc05,a20004.shc05,a20005.shc05,a20006.shc05,a20008.shc05,
                                a20009.shc05,a20010.shc05,a20011.shc05,a20012.shc05,a20013.shc05,a20014.shc05,a00000.shc05 from
(select unique shb10 from shb_file,shc_file where shb01=shc01 and shbconf='Y') ashb_file,
(select shb10,SUM(shc05) shc05 from shb_file,shc_file where shb01=shc01 and shc04='20001'  and shbconf='Y' group by shb10) a20001,
(select shb10,SUM(shc05) shc05 from shb_file,shc_file where shb01=shc01 and shc04='20002'  and shbconf='Y' group by shb10) a20002,
(select shb10,SUM(shc05) shc05 from shb_file,shc_file where shb01=shc01 and shc04='20003'  and shbconf='Y' group by shb10) a20003,
(select shb10,SUM(shc05) shc05 from shb_file,shc_file where shb01=shc01 and shc04='20004'  and shbconf='Y' group by shb10) a20004,
(select shb10,SUM(shc05) shc05 from shb_file,shc_file where shb01=shc01 and shc04='20005'  and shbconf='Y' group by shb10) a20005,
(select shb10,SUM(shc05) shc05 from shb_file,shc_file where shb01=shc01 and shc04='20006'  and shbconf='Y' group by shb10) a20006,
(select shb10,SUM(shc05) shc05 from shb_file,shc_file where shb01=shc01 and shc04='20008'  and shbconf='Y' group by shb10) a20008,
(select shb10,SUM(shc05) shc05 from shb_file,shc_file where shb01=shc01 and shc04='20009'  and shbconf='Y' group by shb10) a20009,
(select shb10,SUM(shc05) shc05 from shb_file,shc_file where shb01=shc01 and shc04='20010'  and shbconf='Y' group by shb10) a20010,
(select shb10,SUM(shc05) shc05 from shb_file,shc_file where shb01=shc01 and shc04='20011'  and shbconf='Y' group by shb10) a20011,
(select shb10,SUM(shc05) shc05 from shb_file,shc_file where shb01=shc01 and shc04='20012'  and shbconf='Y' group by shb10) a20012,
(select shb10,SUM(shc05) shc05 from shb_file,shc_file where shb01=shc01 and shc04='20013'  and shbconf='Y' group by shb10) a20013,
(select shb10,SUM(shc05) shc05 from shb_file,shc_file where shb01=shc01 and shc04='20014'  and shbconf='Y' group by shb10) a20014,            
(select shb10,SUM(shc05) shc05 from shb_file,shc_file where shb01=shc01 and shc04 not in ('20001','20002','20003','20004','20005','20006','20008','20009','20010','20011','20012','20013','20014') and shbconf='Y' group by shb10) a00000    
where ashb_file.shb10=a20001.shb10(+) and ashb_file.shb10=a20002.shb10(+) and ashb_file.shb10=a20003.shb10(+) and ashb_file.shb10=a20004.shb10(+)
   and ashb_file.shb10=a20005.shb10(+) and ashb_file.shb10=a20006.shb10(+) and ashb_file.shb10=a20008.shb10(+) and ashb_file.shb10=a20009.shb10(+)
   and ashb_file.shb10=a20010.shb10(+) and ashb_file.shb10=a20011.shb10(+) and ashb_file.shb10=a20012.shb10(+) and ashb_file.shb10=a20013.shb10(+)
   and ashb_file.shb10=a20014.shb10(+) and ashb_file.shb10=a00000.shb10(+)
order by 1
#29
回复 yaner 2015-02-05, 5:01 PM
--全检数量
select shb04 as 工号,gen02 as 人员,shb10 as 料号,ima02 as 品名,sum(shb111) as 报工量,sum(shb115) as 超报量,sum(shb112) as 不良品 from
shb_file,gen_file,ima_file
where gen01=shb04 and ima01=shb10 and shb081='A006' and shb03<=to_date('2015/01/31','YYYY/mm/dd')
group by shb04,gen02,shb10,ima02
order by shb04,shb10
#30
回复 yaner 2015-04-11, 3:47 PM
select img01 as 料号,img02 as 仓库 ,img03 as 库位,img04 as 批次,img10 as 库存量,img09 as 单,img18 as 库存有效期,img37 as 入库日期,img05 as 参考单号,img06 as 项次
from img_file where img02='A102' and img10>0 and img18<=to_date(to_char(sysdate,'yyyy-mm-dd'), 'YYYY-MM-DD')
order by img18   --过期胶料查询
发表评论