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
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
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'
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
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
---检查开帐库存大于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
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')
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
--不良率横向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
--全检数量
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
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 --过期胶料查询
group by shb05,shb081,ecm45,shb02,shb03,shbdate
order by shb05,shb081,ecm45,shb02,shb03,shbdate
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
增加一个环境变量
NLS_LANG
select userenv('language')from dual
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'
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
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
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
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')
,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
(select ima01 from ima_file where ima01 like '50%') a
where a.ima01 like '%E%' --多重查询
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
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
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 --过期胶料查询