临河热电
2017.2.22
1.给煤机线形图对比 ShuL/GeimjChart.aspx 东华系统密码： 4567890
2.给煤机与皮带秤对比 RLReports/GeimjVsPidc.aspx // wifi : nmlx6224051yf
3 正样仲裁化验对比 RLReports/Huaydb_ZhongCList.aspx
2. 交接样管理: 报表: 1. 采制煤样交接(全水) RLReports/ZhiyjjQS.aspx 2.化验煤样交接(全水) RLReports/HuayjjQS.aspx 3.入厂煤采样交接 RLReports/Caiyryxx.aspx 4.采制煤样交接（分析）RLReports/CaizmyjjFxList.aspx 5.化验煤样交接（分析）RLReports/HuaymyjjFxList.aspx
模块: 1. 制样化验交接样表 RLReports/Jiaojy_ZH.aspx 2.入厂煤采样交接 CaiY/CaiyryxxList.aspx 3.化验煤样交接(全水) HuaY/HuayryxxQSList.aspx 4.采制煤样交接(全水) CaiY/ZhiyryList.aspx 5.化验煤样交接(分析) HuaY/HuayryxxList.aspx 6.采制煤样交接(分析) CaiY/SongfxyList.aspx
2.入炉煤管理 ：
模块: 02 入炉煤皮带秤班制数据 RuL/RulmPDC.aspx 03 入炉煤给煤机班制数据 Rul/RulmGMJ.aspx 04 入炉煤皮带秤班制审核 RuL/RulmPDCSH.aspx 05 入炉煤给煤机审核数据Rul/RulmGMJSH.aspx
仲裁抽样一级审核 CunYGL*CunY_ZYShenHList_1.aspx
仲裁抽样二级审核 CunYGL*CunY_ZYShenHList_2.aspx
仲裁抽样三级审核 CunYGL*CunY_ZYShenHList_3.aspx
仲裁抽样四级审核 CunYGL*CunY_ZYShenHList_4.aspx
制样码打印记录 RLReports/Zymdyjlb.aspx 化验码打印查询 RLReports/HuaymDy.aspx
3.远程Tm： 常网ip：10.32.122.11
GPS： 215079146 远程：246705202 燃管：174246788 wifi: nmlx6224051yf
3.远程设备 本地调试 服务地址：
http://localhost:9637/WebServices/NmlxService.asmx/Nmlx_DataService
4.导出数据库语句：
导入 ： exp lhdcmanager/amtf@NMLXRLGL file=d:\lhdc170119.dmp
导入 ： imp lhjk/amtf@NMLXRLGL file=r:\1.dmp full=y
5.
select to_char(sysdate,'yy-mm-dd hh24:mi:ss') from dual //显示：08-11-07 13:22:42 select to_date('2005-12-25,13:25:59','yyyy-mm-dd,hh24:mi:ss') from dual //显示：2005-12-25 13:25:59
2017-3-6
5.燃油管理报表：
1.
select rq.riq yuef, case when ry.yough is null then hy.yough else ry.yough end yough, case when ry.rq is null then hy.rq else ry.rq end rq, nvl(ry.jingz, 0) jinyl, nvl(hy.haoyl, 0) haoyl, (nvl(ry.jingz, 0) - nvl(hy.haoyl, 0)) jiec from (select distinct to_char(to_date('2017-01', 'yyyy-MM') - (level - 1), 'yyyy-MM') riq from dual connect by level <= (to_date('2017-01', 'yyyy-MM') - to_date('2017-01', 'yyyy-MM') + 1)) rq left join qy_ranyb ry on to_char(ry.rq, 'yyyy-mm') = rq.riq left join qy_ranyhyb hy on to_char(hy.rq, 'yyyy-mm') = rq.riq where rq.riq between '2017-01' and '2017-01' order by rq.riq
2017-3-3：
6. 全水(MT)为0不进行化验三级审核：
1. 三条抽查的入炉煤样
select * from QY_ZHILRLB t where to_char(t.huaysj, 'yyyy-mm-dd') = '2017-02-28' and mt = 0
2.
select * from qy_vwzhillsb zll where (abs(zll.shenhzt) = 0 or (to_date(to_char(zll.huaysj, 'yyyy-mm-dd'), 'yyyy-mm-dd') between to_date('2017-03-03', 'yyyy-mm-dd') and to_date('2017-03-03', 'yyyy-mm-dd') and abs(zll.shenhzt) > 0)) and zll.huaysj is not null and daohrq is null order by abs(zll.shenhzt), zll.leib, zll.huaysj desc
2017-3-7:
制样存样间： 192.168.1.170 原煤存样间：192.168.1.165 化验存样间 192.168.1.185
2017-3-10:
100服务器物理地址 78-28-cb-of-c2-9e
2017-3-13:
燃油模块报表新修改
select substr(zt.yuef, 0,7) AA, yough, rq, jinyl, haoyl, jiec from (select rq.riq yuef, case when ry.yough is null then hy.yough else ry.yough end yough, case when ry.rq is null then hy.rq else ry.rq end rq, nvl(ry.jingz, 0) jinyl, nvl(hy.haoyl, 0) haoyl, (nvl(ry.jingz, 0) - nvl(hy.haoyl, 0)) jiec from (select distinct to_char(to_date('2017-03-30', 'yyyy-mm-dd') - (level - 1), 'yyyy-mm-dd') riq from dual connect by level <= (to_date('2017-03-30', 'yyyy-mm-dd') - to_date('2017-01-01', 'yyyy-mm-dd') + 1)) rq left join qy_ranyb ry on to_char(ry.rq, 'yyyy-mm-dd') = rq.riq left join qy_ranyhyb hy on to_char(hy.rq, 'yyyy-mm-dd') = rq.riq where rq.riq between '2017-01' and '2017-03' order by rq.riq) zt where jinyl <> '0' or haoyl <> '0'
2017-3-28 抽查化验数据入炉数据加入
select ls.qnet_ar old_qnet_ar, round_new(ls.qnet_ar / (select t.zhi from qy_xitxxb t where t.mingc = '入厂热值系数'), 0) old_qnet_arkcal, ls.std old_std, ls.HUAYBH old_HUAYBH, ls.HUAYSJ OLD_HUAYSJ, ls.stad old_stad, ls.mt old_mt, ls.had old_had, ls.mad old_mad, ls.had old_had, ls.aad old_aad, ls.ad old_ad, ls.aar old_aar, ls.vad old_vad, ls.var old_var, ls.vdaf old_vdaf, ls.qbad old_qbad, ls.qgrd old_qgrd, ls.qgrad old_qgrad from (select * from qy_zhilrlb t ) zt left join qy_zhilrlb ls on ls.qy_zhilrlb_fk = zt.qy_zhilrlboid where to_char(ls.huaysj, 'yyyy-mm-dd') between '2017-01-27' and '2017-03-27' order by ls.huaysj
完善版本：新旧数据对比
select zt.*, ls.qnet_ar old_qnet_ar, round_new(ls.qnet_ar / (select t.zhi from qy_xitxxb t where t.mingc = '入厂热值系数'), 0) old_qnet_arkcal, ls.std old_std, ls.HUAYBH old_HUAYBH, ls.HUAYSJ OLD_HUAYSJ, ls.stad old_stad, ls.mt old_mt, ls.had old_had, ls.mad old_mad, ls.had old_had, ls.aad old_aad, ls.ad old_ad, ls.aar old_aar, ls.vad old_vad, ls.var old_var, ls.vdaf old_vdaf, ls.qbad old_qbad, ls.qgrd old_qgrd, ls.qgrad old_qgrad from (select * from qy_zhilrlb t ) zt left join qy_zhilrlb ls on ls.qy_zhilrlb_fk = zt.qy_zhilrlboid where to_char(ls.huaysj, 'yyyy-mm-dd') between '2017-01-27' and '2017-03-27' order by ls.huaysj
住：入场煤抽查数据对比：
select tab1.*, ls.qnet_ar old_qnet_ar, round_new(ls.qnet_ar / (select t.zhi from qy_xitxxb t where t.mingc = '入厂热值系数'), 0) old_qnet_arkcal, ls.std old_std, zm.bianm old_HUAYBH, ls.HUAYSJ OLD_HUAYSJ, ls.stad old_stad, ls.mt old_mt, ls.had old_had, ls.mad old_mad, ls.had old_had, ls.aad old_aad, ls.ad old_ad, ls.aar old_aar, ls.vad old_vad, ls.var old_var, ls.vdaf old_vdaf, ls.qbad old_qbad, ls.qgrd old_qgrd, ls.qgrad old_qgrad from (select fh.daohrq, mk.mingc mkmc, pz.mingc pzmc, zlls.qy_zhillsboid, zlls.qy_zhilb_fk, '' leix, decode(zlls.cyfs, 0, '机采', 1, '人工', 2, '抽查', 3, '制样抽样', 4, '化验抽样', '第三方化验') cyfs, decode(zlls.leib, 0, '自动', '平台') leib, zlls.meiylx, z.bianm huaybh, fh.jingz, zlls.qnet_ar, round_new(zlls.qnet_ar / (select t.zhi from qy_xitxxb t where t.mingc = '入厂热值系数'), 0) qnet_arkcal, zlls.std, zlls.huaysj, zlls.stad, zlls.mt, zlls.had, zlls.mad, zlls.aad, zlls.ad, zlls.aar, zlls.vad, zlls.var, zlls.vdaf, zlls.qbad, zlls.qgrd, zlls.qgrad from qy_fahb fh left join QY_ZHILLSB zlls on fh.qy_zhilb_fk = zlls.qy_zhilb_fk left join qy_jh_meikxxb mk on mk.qy_jh_meikxxboid = fh.qy_jh_meikxxb_fk left join qy_jh_pinzb pz on pz.qy_jh_pinzboid = fh.qy_jh_pinzb_fk left join qy_zhuanmb z on z.qy_zhillsb_fk = zlls.qy_zhillsboid where (zlls.cyfs = 3 or zlls.cyfs = 4) and zlls.meiylx = '存样抽样' and z.qy_zhuanmlb_fk = 'A22C1364-441D-D4D8-0A71-9BA6287C71D9') tab1 left join qy_zhillsb ls on (ls.qy_zhilb_fk = tab1.qy_zhilb_fk and ls.cyfs = 0) left join qy_zhuanmb zm on zm.qy_zhillsb_fk = ls.qy_zhillsboid where to_char(daohrq, 'yyyy-mm-dd') between '2017-01-27' and '2017-03-27' and zm.qy_zhuanmlb_fk = 'A22C1364-441D-D4D8-0A71-9BA6287C71D9' order by daohrq
修改2： 备查样管理 序号 4 名称 ： 化验备查样-仲裁 地址：CunYGL/CunY-HY.aspx
2017-3-29:
抽样化验数据对比（入厂入炉表联结）
select zlls.*, ls.qnet_ar old_qnet_ar, round_new(ls.qnet_ar / (select t.zhi from qy_xitxxb t where t.mingc = '入厂热值系数'), 0) old_qnet_arkcal, ls.std old_std, ls.HUAYBH old_HUAYBH, ls.HUAYSJ OLD_HUAYSJ, ls.stad old_stad, ls.mt old_mt, ls.had old_had, ls.mad old_mad, ls.had old_had, ls.aad old_aad, ls.ad old_ad, ls.aar old_aar, ls.vad old_vad, ls.var old_var, ls.vdaf old_vdaf, ls.qbad old_qbad, ls.qgrd old_qgrd, ls.qgrad old_qgrad from (select '' daohrq, '' mkmc, '' pzmc, '' qy_zhillsboid, '' qy_zhilb_fk, '' leix, '' cyfs, '' leib, '' meiylx, zlls.huaybh, '' jingz, zlls.qnet_ar, round_new(zlls.qnet_ar / (select t.zhi from qy_xitxxb t where t.mingc = '入厂热值系数'), 0) qnet_arkcal, zlls.std, zlls.huaysj, zlls.stad, zlls.mt, zlls.had, zlls.mad, zlls.aad, zlls.ad, zlls.aar, zlls.vad, zlls.var, zlls.vdaf, zlls.qbad, zlls.qgrd, zlls.qgrad, zlls.qy_zhilrlboid from qy_zhilrlb zlls ) zlls left join qy_zhilrlb ls on ls.qy_zhilrlb_fk = zlls.qy_zhilrlboid where to_char(ls.huaysj, 'yyyy-mm-dd') between '2017-01-27' and '2017-03-27' union select tab1.*, ls.qnet_ar old_qnet_ar, round_new(ls.qnet_ar / (select t.zhi from qy_xitxxb t where t.mingc = '入厂热值系数'), 0) old_qnet_arkcal, ls.std old_std, zm.bianm old_HUAYBH, ls.HUAYSJ OLD_HUAYSJ, ls.stad old_stad, ls.mt old_mt, ls.had old_had, ls.mad old_mad, ls.had old_had, ls.aad old_aad, ls.ad old_ad, ls.aar old_aar, ls.vad old_vad, ls.var old_var, ls.vdaf old_vdaf, ls.qbad old_qbad, ls.qgrd old_qgrd, ls.qgrad old_qgrad from (select fh.daohrq, mk.mingc mkmc, pz.mingc pzmc, zlls.qy_zhillsboid, zlls.qy_zhilb_fk, '' leix, decode(zlls.cyfs, 0, '机采', 1, '人工', 2, '抽查', 3, '制样抽样', 4, '化验抽样', '第三方化验') cyfs, decode(zlls.leib, 0, '自动', '平台') leib, zlls.meiylx, z.bianm huaybh, fh.jingz, zlls.qnet_ar, round_new(zlls.qnet_ar / (select t.zhi from qy_xitxxb t where t.mingc = '入厂热值系数'), 0) qnet_arkcal, zlls.std, zlls.huaysj, zlls.stad, zlls.mt, zlls.had, zlls.mad, zlls.aad, zlls.ad, zlls.aar, zlls.vad, zlls.var, zlls.vdaf, zlls.qbad, zlls.qgrd, zlls.qgrad, ''qy_zhilrlboid from qy_fahb fh left join QY_ZHILLSB zlls on fh.qy_zhilb_fk = zlls.qy_zhilb_fk left join qy_jh_meikxxb mk on mk.qy_jh_meikxxboid = fh.qy_jh_meikxxb_fk left join qy_jh_pinzb pz on pz.qy_jh_pinzboid = fh.qy_jh_pinzb_fk left join qy_zhuanmb z on z.qy_zhillsb_fk = zlls.qy_zhillsboid where (zlls.cyfs = 3 or zlls.cyfs = 4) and zlls.meiylx = '存样抽样' and z.qy_zhuanmlb_fk = 'A22C1364-441D-D4D8-0A71-9BA6287C71D9') tab1 left join qy_zhillsb ls on (ls.qy_zhilb_fk = tab1.qy_zhilb_fk and ls.cyfs = 0) left join qy_zhuanmb zm on zm.qy_zhillsb_fk = ls.qy_zhillsboid where to_char(daohrq, 'yyyy-mm-dd') between '2017-01-27' and '2017-03-27' and zm.qy_zhuanmlb_fk = 'A22C1364-441D-D4D8-0A71-9BA6287C71D9' order by daohrq
2017-4-5
2.1 列出2016-10-25当天来煤批次信息（9分）
结果集: 供货单位 煤矿 品种 发货日期 车数 来煤总量当前批次第一车重车时间 当前批次第一车轻车时间 当前批次最后一车重车时间 当前批次最后一车重车时间
select t.* from (select to_char(fh.daohrq, 'yyyy-mm-dd') daohrq, gy.mingc as mingc_gy, mk.mingc as mingc_mk, pz.mingc as mingc_pz, fh.ches, fh.jingz as JINGZ, min(cp.zhongcsj) minzcsj, min(cp.qingcsj) minqcsj, max(cp.zhongcsj) maxzcsj, max(cp.qingcsj) maxqcsj from qy_fahb fh, qy_diancxxb dc, qy_gongysb gy, qy_jh_yunsfsb ys, qy_jh_pinzb pz, qy_jh_meikxxb mk, qy_chepb cp where fh.qy_diancxxb_fk = dc.qy_diancxxboid and fh.qy_jh_meikxxb_fk = mk.qy_jh_meikxxboid and fh.qy_jh_pinzb_fk = pz.qy_jh_pinzboid and fh.qy_gongysb_fk = gy.qy_gongysboid and fh.qy_jh_yunsfsb_fk = ys.qy_jh_yunsfsboid and cp.qy_fahb_fk=fh.qy_fahboid group by gy.mingc, mk.mingc , pz.mingc, fh.daohrq, fh.ches, fh.jingz ) t where t.daohrq = '2016-10-25'
2.2 列出2016-10-25当天车辆详细信息,按重车时间升序排列（8分）
结果集:供货单位 煤矿 品种 运输单位 序号 验票时间 重车时间 轻车时间 净重 扣吨
select tab.*, gys.mingc gysMingc, mk.mingc mkMingc, pz.mingc pzMingc, ys.mingc YSMINGC from (select cp.qy_chepboid taboid, cp.xuh, cp.maoz, cp.piz, cp.zongkd, cp.zhongcsj, cp.qingcsj, fh.qy_gongysb_fk, fh.qy_jh_meikxxb_fk, fh.qy_jh_pinzb_fk, cp.qy_yunsdwb_fk, fh.qy_jh_yunsfsb_fk, cp.qy_diancxxb_fk, round((cp.maoz - cp.piz), 2) jingz from qy_chepb cp left join qy_fahb fh on fh.qy_fahboid = cp.qy_fahb_fk where (fh.yundh is null or fh.yundh = '' or fh.yundh = null)) tab left join qy_gongysb gys on gys.qy_gongysboid = tab.qy_gongysb_fk left join qy_jh_meikxxb mk on mk.qy_jh_meikxxboid = tab.qy_jh_meikxxb_fk left join qy_jh_pinzb pz on pz.qy_jh_pinzboid = tab.qy_jh_pinzb_fk left join qy_yunsdwb ys on ys.qy_yunsdwboid = tab.qy_yunsdwb_fk where to_char(tab.qingcsj, 'yyyy-mm-dd') ='2016-10-25' order by tab.zhongcsj
2.8 查询所有打印过2次以上的所有编码打印记录,按打印时间升序排列（5分）
1.
select * from QY_BIANMDYJLB t where dayzs>=2 order by daysj
2.
select sum(bd.dayzs) dayzs, bd.bianm, to_char(bd.daysj, 'yyyy-mm-dd') daysj, case when bd.leix = 'zym' or bd.leix = 'ZYBM' then '制样码' when bd.leix = 'hym' or bd.leix = 'HYBM' then '化验码' when bd.leix = 'cym' or bd.leix = 'CYBM' then '采样码' end as leix, tb.username from qy_bianmdyjlb bd left join tb_user tb on bd.tb_user_fk = tb.userid where dayzs >=2 group by bd.bianm, tb.username, bd.leix, to_char(bd.daysj, 'yyyy-mm-dd') order by daysj
2017-4-6
2.5 如何用一个sql的查询结果,反映出该厂以重车还是轻车时间划分批次（8分）
select fh.fahrq, fh.daohrq, cp.qingcsj, cp.zhongcsj, cp.ppsj yanpsj from qy_fahb fh, qy_chepb cp where cp.qy_fahb_fk=fh.qy_fahboid and fh.fahrq=cp.qingcsj or fh.fahrq=cp.zhongcsj or fh.fahrq=cp.ppsj and to_char(fh.fahrq,'yyyy-mm-dd')='2016-10-25' order by yanpsj
2.3 列出车皮表按批次汇总总和与发货表数据不符的批次信息,以发货日期倒序排列（8分）
select t.* from (select to_char(fh.fahrq, 'yyyy-mm-dd') fhrq, sum(cp.biaoz) biaoz, fh.jingz, mk.quanc, (fh.jingz - sum(cp.biaoz)) chaz from qy_fahb fh, qy_chepb cp, qy_jh_meikxxb mk where cp.qy_fahb_fk = fh.qy_fahboid and fh.qy_jh_meikxxb_fk = mk.qy_jh_meikxxboid group by fh.fahrq, fh.jingz, mk.quanc) t order by t.fhrq desc
2.4 查询批次信息对应的三级编码及化验数据,以发货日期倒序排列（10分）
查询结果集:供货单位 煤矿 品种 发货日期 车数 来煤总量 采样编码 制样编码 化验编码 审核状态 化验时间 qnet_ar ad std mt 一审时间 二审时间 三审时间
提示：三级编码使用行转列 pivot查询
select* from (select to_date(to_char(fh.daohrq, 'yyyy-mm-dd'), 'yyyy-mm-dd') daohrq, gy.mingc as mingc_gy, mk.mingc as mingc_mk, pz.mingc as mingc_pz, fh.ches, fh.jingz as JINGZ , zl.HUAYSJ, zl.QNET_AR, zl.AD, zl.MT, zl.STD, zlls.shenhsj_1, zlls.shenhsj_2, zlls.shenhsj_3, zlls.shenhzt， zm.bianm, zmlb.mingc from qy_zhilb zl, qy_fahb fh, qy_gongysb gy, qy_jh_pinzb pz, qy_jh_meikxxb mk, qy_zhillsb zlls, qy_zhuanmb zm, qy_zhuanmlb zmlb where fh.qy_zhilb_fk = zl.qy_zhilboid and fh.qy_jh_meikxxb_fk = mk.qy_jh_meikxxboid and fh.qy_jh_pinzb_fk = pz.qy_jh_pinzboid and fh.qy_gongysb_fk = gy.qy_gongysboid and zlls.qy_zhilb_fk= zl.qy_zhilboid and zm.qy_zhillsb_fk=zlls.qy_zhillsboid and zm.qy_zhuanmlb_fk=zmlb.qy_zhuanmlboid) pivot(sum(bianm) for mingc in('采样编码' CAIYM, '制样编码'ZHIYM,'化验编码'HUAYM))
详解 ： Pivot 行转列 用法：
先展示几行数据：
select zm.bianm, zmlb.mingc from qy_zhuanmb zm, qy_zhuanmlb zmlb where zm.qy_zhuanmlb_fk = zmlb.qy_zhuanmlboid
此时要把这些数据行转列：
select * from (select zm.bianm, zmlb.mingc from qy_zhuanmb zm, qy_zhuanmlb zmlb where zm.qy_zhuanmlb_fk = zmlb.qy_zhuanmlboid) pivot(sum(bianm) for mingc in('采样编码'caiym, '制样编码' zhiym,'化验编码' huaym))
原作者解释：http://blog.csdn.net/xiaokui_wingfly/article/details/42419207
2017-4-7
2.7 查询3个月内（时间区间：2016-08-25至2016-10-25,以重车时间为查询依据）未拉煤的车辆信息,按照最后一次重车时间倒序排列,对应时间为空要求排在后面（9分）
查询结果集：车号 rfid卡号 最后一次入厂的重车时间
提示：对应的重车时间为空时，按照该列倒序排列，为空的排列在最前，可使用一个小值替换空值，排序后再替换回来实现。
方法1：
select c.cardid, c.numplate cheph, tab.zhongcsj lasttime from card_plate c left join (select cp.cheph, max(cp.zhongcsj) zhongcsj from qy_chepb cp where to_char(cp.zhongcsj,'yyyy-mm-dd')< '2016-08-25' or to_char(cp.zhongcsj,'yyyy-mm-dd') > '2016-10-25' group by cp.cheph) tab on tab.cheph = c.numplate order by lasttime desc nulls last
注意：
1、ORDER BY 中关于NULL的处理
缺省处理，Oracle在Order by 时认为null是最大值，所以如果是ASC升序则排在最后，DESC降序则排在最前。
当然，你也可以使用nulls first 或者nulls last 语法来控制NULL的位置。
Nulls first和nulls last是Oracle Order by支持的语法
如果Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)
如果Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)
使用语法如下：
--将nulls始终放在最前
select * from zl_cbqc order by cb_ld nulls first
--将nulls始终放在最后
select * from zl_cbqc order by cb_ld desc nulls last
方法二：用 decode 处理null值
select c.cardid, c.numplate cheph, tab.zhongcsj lasttime from card_plate c left join (select cp.cheph, max(cp.zhongcsj) zhongcsj from qy_chepb cp where to_char(cp.zhongcsj,'yyyy-mm-dd')< '2016-08-25' or to_char(cp.zhongcsj,'yyyy-mm-dd') > '2016-10-25' group by cp.cheph) tab on tab.cheph = c.numplate order by decode(lasttime,'null','1900-01-01',lasttime) desc
2.6查询所有修改过煤矿的车辆信息,按重车时间倒序排列(不能对比qy_chep_backb表查询)（
1. select tab.*, 2. (select mk.mingc 3. from qy_jh_meikxxb mk 4. where mk.qy_jh_meikxxboid = tab.jiuk) jiukm, 5. (select mk.mingc MC2 6. from qy_jh_meikxxb mk 7. where mk.qy_jh_meikxxboid = tab.xink) xinkm 8. from (select cp.cheph, 9. cp.zhongcsj, 10. cp.qingcsj, 11. cp.jingz, 12. jk.qy_jh_meikxxb_fk jiuk, 13. fh.qy_jh_meikxxb_fk xink 14. from qy_chepb cp, qy_jihkb jk, qy_jihkzb jkz, qy_fahb fh 15. where cp.piaojh = jkz.xuh 16. and jkz.qy_jihkb_fk = jk.qy_jihkboid 17. and cp.qy_fahb_fk = fh.qy_fahboid) tab 18. where tab.jiuk <> tab.xink 19. order by tab.zhongcsj desc
3.1 计划卡主表增加插入前触发器,实现对应计划卡子表的数据插入
要求：编写计划卡表的触发器,再往计划卡表插入数据,煤矿,品种的外键可为空,只要计划卡子表的编码正确即可,按开始日期最大加1开始本次序号
1. create or replace trigger tr_ins_jihk 2. before insert on qy_jihkb 3. for each row 4. declare 5. xuh_id varchar2(50); 6. str_oid varchar2(36); 7. begin 8. select BAR_CODE_SEQUENCE.nextval into :new.ID from dual; 9. select qy_jihkboid into str_oid from qy_jihkb where （kaisrq-sysdate）=0; 10. select to_number(to_char(max(kaisrq),'yyyymmddhhmiss'))+1 into xuh_id from qy_jihkb; 11. insert into qy_jihkzb 12. (qy_jihkzboid, 13. id, 14. jihkb_id, 15. xuh, 16. shifsy, 17. qy_jihkb_fk) 18. values 19. (:new.ID, 20. null, 21. null, 22. xuh_id, 23. 0, 24. str_oid); 25. exception 26. when others then 27. rollback; 28. end;
2017-4-18
序号 7 全厂数量汇总表 RLReports/Shulhzxx.aspx
2017-4-20
展示入炉煤数据 ： 7896321
select t.tesyoid, t.mingc mingc, t.caiybm cym, t.zhiybm zym, t.huaybm hym, t.jiz jiz, t.shul shul, (select huaysj from qy_ranmsyb where huaybh=t.huaybm and flag=0) HUAYSJ, t.sj caiysj from TESYXXB t where t.mingc = '入炉煤' and t.shenhzt = 3 order by t.sj desc
2017-04-24
数量台账新试图：
create or replace view qy_vwshultz as select "QY_VWHUAYTZOID", "QY_DIANCXXBOID", "DAOHRQ", "MINGC_YS", "MINGC_GY", "MINGC_MK", "MINGC_PZ", QY_JH_MEIKXXBOID, "CHES", "JINGZ", "MAOZ", "PIZ", "KOUD", "BIAOZ" from (select '00000aec-0000-0000-0000-000000000000' qy_vwhuaytzoid, dc.qy_diancxxboid, to_date(to_char(fh.daohrq, 'yyyy-mm-dd'), 'yyyy-mm-dd') daohrq, ys.mingc as mingc_ys, gy.mingc as mingc_gy, MK.QY_JH_MEIKXXBOID, mk.mingc as mingc_mk, pz.mingc as mingc_pz, fh.ches, fh.jingz as JINGZ, fh.maoz as MAOZ, fh.piz as PIZ, nvl(fh.koud, 0) as KOUD, fh.biaoz as BIAOZ from qy_fahb fh left join qy_diancxxb dc on fh.qy_diancxxb_fk = dc.qy_diancxxboid left join qy_gongysb gy on fh.qy_gongysb_fk = gy.qy_gongysboid left join qy_jh_yunsfsb ys on fh.qy_jh_yunsfsb_fk = ys.qy_jh_yunsfsboid left join qy_jh_pinzb pz on fh.qy_jh_pinzb_fk = pz.qy_jh_pinzboid left join qy_jh_meikxxb mk on fh.qy_jh_meikxxb_fk = mk.qy_jh_meikxxboid ) tab
旧视图：
create or replace view qy_vwshultz as select "QY_VWHUAYTZOID", "QY_DIANCXXBOID", "DAOHRQ", "MINGC_YS", "MINGC_GY", "MINGC_MK", "MINGC_PZ", QY_JH_MEIKXXBOID, "CHES", "JINGZ", "MAOZ", "PIZ", "KOUD", "BIAOZ" from (select '00000aec-0000-0000-0000-000000000000' qy_vwhuaytzoid, dc.qy_diancxxboid, to_date(to_char(fh.daohrq,'yyyy-mm-dd'),'yyyy-mm-dd') daohrq, ys.mingc as mingc_ys, gy.mingc as mingc_gy, MK.QY_JH_MEIKXXBOID, mk.mingc as mingc_mk, pz.mingc as mingc_pz, fh.ches, fh.jingz as JINGZ, fh.maoz as MAOZ, fh.piz as PIZ, nvl(fh.koud,0) as KOUD, fh.biaoz as BIAOZ from qy_fahb fh, qy_diancxxb dc, qy_gongysb gy, qy_jh_yunsfsb ys, qy_jh_pinzb pz, qy_jh_meikxxb mk where fh.qy_diancxxb_fk = dc.qy_diancxxboid and fh.qy_jh_meikxxb_fk = mk.qy_jh_meikxxboid and fh.qy_jh_pinzb_fk = pz.qy_jh_pinzboid and fh.qy_gongysb_fk = gy.qy_gongysboid and fh.qy_jh_yunsfsb_fk = ys.qy_jh_yunsfsboid) tab
2017-4-27
火车皮检测服务：表结构
-- Create table create table QY_HUOCJCB ( qy_huocjcboid VARCHAR2(36) not null, xuh NUMBER, cheh VARCHAR2(10), chex VARCHAR2(20), ziz NUMBER(10,3), bzaiz NUMBER(10,3), biaoc NUMBER(10,3), biaok NUMBER(10,3), biaog NUMBER(10,3), biaotj NUMBER(10,3), biaopz NUMBER(10,3), jcsj VARCHAR2(50), sud NUMBER(10,3), zl NUMBER(10,3), zaiz NUMBER(10,3), celkd NUMBER(10,3), celgd NUMBER(10,3), chebgd NUMBER(10,3), mtj NUMBER(10,3), mpz NUMBER(10,3), pzpc NUMBER(10,3), hm VARCHAR2(50), faz VARCHAR2(50), daoz VARCHAR2(50), yc VARCHAR2(50), tup VARCHAR2(100), guohsj VARCHAR2(50) ) tablespace NMLXDB pctfree 10 initrans 1 maxtrans 255 storage ( initial 64 next 104 minextents 1 maxextents unlimited pctincrease 0 ); -- Create/Recreate primary, unique and foreign key constraints alter table QY_HUOCJCB add constraint QY_HUOCJCBOID primary key (QY_HUOCJCBOID) using index tablespace NMLXDB pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 104K minextents 1 maxextents unlimited pctincrease 0 );
2017-5-2
火车皮采检测：http://192.168.1.100/HCWebservice/HCWebService.asmx
2017-06-08
select * from waring where miaos='{"SBZT": { "TIANX": "正常", "DUIS1": "正常", "DUIS2": "正常", "LED": "正常", "ZHUANGT": "正常" }, "LOCATION": "#1轻车衡" }' and to_char(baojsj,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd') and chuljg is null
2017-06-27
皮带秤给煤机视图：
create or replace view qy_vwhuaypdcgmj as select 'pdc' leib, rlm.luh jiz, rlm.banz, rlm.ruldate, t1.shul, zlrl.qnet_ar, zlrl.std, zlrl.mt, zlrl.had, zlrl.mad, zlrl.aad, zlrl.aar, zlrl.vad, zlrl.vdaf, zlrl.qbad, zlrl.qgrad from qy_rulmpdcb rlm left join qy_zhilrlb zlrl on to_char(rlm.ruldate,'yyyy-mm-dd') = to_char(zlrl.huaysj,'yyyy-mm-dd') left join (select sum(gmj.shul) shul, gmj.xt, gmj.ruldate from qy_rulmpdcb gmj group by gmj.xt, gmj.ruldate) t1 on t1.ruldate = rlm.ruldate where zlrl.shenhzt = 3 union select 'gmj' leib, rlm.luh jiz, rlm.banz, rlm.ruldate, t1.shul, zlrl.qnet_ar, zlrl.std, zlrl.mt, zlrl.had, zlrl.mad, zlrl.aad, zlrl.aar, zlrl.vad, zlrl.vdaf, zlrl.qbad, zlrl.qgrad from qy_rulmgmjb rlm left join qy_zhilrlb zlrl on rlm.qy_zhilrlb_fk = zlrl.qy_zhilrlboid left join (select sum(gmj.shul) shul, gmj.xt, gmj.ruldate from qy_rulmgmjb gmj group by gmj.xt, gmj.ruldate) t1 on t1.ruldate = rlm.ruldate where zlrl.shenhzt = 3;
触发器：
create or replace trigger tri_Addsanjmb --同一批次大于20车自动生产两套化验编码 after INSERT OR UPDATE ON COAL_SAMPLER FOR EACH ROW declare cs_count int; --cs_count 记录一个批次采样车数 hy_count int; --hy_count 记录一个批次有几套码 fahoid VARCHAR2(36); --记录发货表主键 rel int; PRAGMA AUTONOMOUS_TRANSACTION; BEGIN select sum(ches) cs into cs_count from COAL_SAMPLER where coal in (select zmb.bianm from qy_zhillsb zlls left join qy_zhuanmb zmb on zmb.qy_zhillsb_fk = zlls.qy_zhillsboid where zlls.qy_zhilb_fk = (select zll.qy_zhilb_fk from qy_zhuanmb zm left join qy_zhillsb zll on zll.qy_zhillsboid = zm.qy_zhillsb_fk where zm.bianm = :old.coal)) and coal_sampleroid <> :old.coal_sampleroid; cs_count := cs_count + :new.ches; select fh.qy_fahboid, count(distinct zlls.qy_zhillsboid) gs into fahoid, hy_count from qy_fahb fh left join qy_zhillsb zlls on zlls.qy_zhilb_fk = fh.qy_zhilb_fk where zlls.qy_zhilb_fk = (select zll.qy_zhilb_fk from COAL_SAMPLER cs left join qy_zhuanmb zm on zm.bianm = cs.coal left join qy_zhillsb zll on zll.qy_zhillsboid = zm.qy_zhillsb_fk where cs.coal = :old.coal and rownum = 1) and (zlls.leib = 0 and zlls.cyfs = 0 or zlls.leib = 1 and zlls.cyfs = 6) group by fh.qy_fahboid; --如果车数大于20且只有一套化验三级编码 if cs_count > 20 and hy_count = 1 then sp_createzhilsys_pinttj(fahoid, 6, rel); end if; END;
2017-09-14
select s.QY_SHEBTZBOID qy_shebtzb_fk, s.SBMC, s.CJMC, s.JXZQ, s.CJLXR, s.PHION, o.orgname SSBM, u.username BMFZR, s.BZ, s.TB_ORGINFO_FK, s.TB_USER_FK, sbjx.qy_shebjxboid, sbjx.jxsj, sbjx.jxry, s.jxsj lastjxsj, tu.username jxxm, sbjx.jxjg, sbjx.shenhzt, sbjx.shenhry, sbjx.fileurl FROM QY_SHEBTZB s left join tb_orginfo o on o.orgid = s.TB_ORGINFO_FK left join tb_user u on u.userid = s.TB_USER_FK left join (select t.qy_shebjxboid, t.qy_shebtzb_fk, t.jxsj, t.jxry, t.jxjg, t.shenhzt, t.shenhry, t.fileurl from qy_shebjxb t, (select t.qy_shebtzb_fk, max(t.jxsj) jxsj from QY_SHEBJXB t group by t.qy_shebtzb_fk) tt where tt.qy_shebtzb_fk = t.qy_shebtzb_fk and t.jxsj = tt.jxsj) sbjx on sbjx.qy_shebtzb_fk = s.qy_shebtzboid left join tb_user tu on tu.userid = sbjx.jxry where 1 = 1 and (select tbu.deparetmentid from tb_user tbu where tbu.userid = 'c1931ad5-2cb6-450b-973f-fd2cae7831b0') = s.tb_user_fk
2017-09-23:
抽查样代码打印：
select t.* from (select DAOHRQ, MKMINGC, PZMINGC, CAIYBH, ZHIYBH, HUAYBH, ISCHOUY, CYFS, FL, ISCYDY, ISZYDY, ISHYDY FROM QY_VWGETSANJBM_HB where to_char(DAOHRQ, 'yyyy-mm-dd') >= '2017-09-01' and to_char(DAOHRQ, 'yyyy-mm-dd') <= '2017-09-23') t where t.CYFS <> '机械采样' and t.CYFS <> '人工采样' order by t.DAOHRQ desc
仲裁机械化验对比：
select tab1.*, ls.qnet_ar old_qnet_ar, round_new(ls.qnet_ar / (select t.zhi from qy_xitxxb t where t.mingc = '入厂热值系数'), 0) old_qnet_arkcal, ls.std old_std, zm.bianm old_HUAYBH, ls.HUAYSJ OLD_HUAYSJ, ls.stad old_stad, ls.mt old_mt, ls.had old_had, ls.mad old_mad, ls.had old_had, ls.aad old_aad, ls.ad old_ad, ls.aar old_aar, ls.vad old_vad, ls.var old_var, ls.vdaf old_vdaf, ls.qbad old_qbad, ls.qgrd old_qgrd, ls.qgrad old_qgrad from (select fh.daohrq, mk.mingc mkmc, pz.mingc pzmc, zlls.qy_zhillsboid, zlls.qy_zhilb_fk, '' leix, decode(zlls.cyfs, 0, '机采', 1, '人工', 2, '抽查', 3, '制样抽样', 4, '化验抽样', '第三方化验') cyfs, decode(zlls.leib, 0, '自动', '平台') leib, zlls.meiylx, z.bianm huaybh, fh.jingz, zlls.qnet_ar, round_new(zlls.qnet_ar / (select t.zhi from qy_xitxxb t where t.mingc = '入厂热值系数'), 0) qnet_arkcal, zlls.std, zlls.huaysj, zlls.stad, zlls.mt, zlls.had, zlls.mad, zlls.aad, zlls.ad, zlls.aar, zlls.vad, zlls.var, zlls.vdaf, zlls.qbad, zlls.qgrd, zlls.qgrad from qy_fahb fh left join QY_ZHILLSB zlls on fh.qy_zhilb_fk = zlls.qy_zhilb_fk left join qy_jh_meikxxb mk on mk.qy_jh_meikxxboid = fh.qy_jh_meikxxb_fk left join qy_jh_pinzb pz on pz.qy_jh_pinzboid = fh.qy_jh_pinzb_fk left join qy_zhuanmb z on z.qy_zhillsb_fk = zlls.qy_zhillsboid where (zlls.cyfs = 3 or zlls.cyfs = 4) and zlls.meiylx <> '存样抽样' and z.qy_zhuanmlb_fk = 'A22C1364-441D-D4D8-0A71-9BA6287C71D9') tab1 left join qy_zhillsb ls on (ls.qy_zhilb_fk = tab1.qy_zhilb_fk and ls.cyfs = 0) left join qy_zhuanmb zm on zm.qy_zhillsb_fk = ls.qy_zhillsboid where to_char(daohrq, 'yyyy-mm-dd') between '2017-01-23' and '2017-09-23' and zm.qy_zhuanmlb_fk = 'A22C1364-441D-D4D8-0A71-9BA6287C71D9' order by daohrq
机械抽查样化验对比：
select tab1.*, ls.qnet_ar old_qnet_ar, round_new(ls.qnet_ar / (select t.zhi from qy_xitxxb t where t.mingc = '入厂热值系数'), 0) old_qnet_arkcal, ls.std old_std, zm.bianm old_HUAYBH, ls.HUAYSJ OLD_HUAYSJ, ls.stad old_stad, ls.mt old_mt, ls.had old_had, ls.mad old_mad, ls.had old_had, ls.aad old_aad, ls.ad old_ad, ls.aar old_aar, ls.vad old_vad, ls.var old_var, ls.vdaf old_vdaf, ls.qbad old_qbad, ls.qgrd old_qgrd, ls.qgrad old_qgrad from (select fh.daohrq, mk.mingc mkmc, pz.mingc pzmc, zlls.qy_zhillsboid, zlls.qy_zhilb_fk, '' leix, decode(zlls.cyfs, 0, '机采', 1, '人工', 2, '抽查', 3, '制样抽样', 4, '化验抽样', '第三方化验') cyfs, decode(zlls.leib, 0, '自动', '平台') leib, zlls.meiylx, z.bianm huaybh, fh.jingz, zlls.qnet_ar, round_new(zlls.qnet_ar / (select t.zhi from qy_xitxxb t where t.mingc = '入厂热值系数'), 0) qnet_arkcal, zlls.std, zlls.huaysj, zlls.stad, zlls.mt, zlls.had, zlls.mad, zlls.aad, zlls.ad, zlls.aar, zlls.vad, zlls.var, zlls.vdaf, zlls.qbad, zlls.qgrd, zlls.qgrad from qy_fahb fh left join QY_ZHILLSB zlls on fh.qy_zhilb_fk = zlls.qy_zhilb_fk left join qy_jh_meikxxb mk on mk.qy_jh_meikxxboid = fh.qy_jh_meikxxb_fk left join qy_jh_pinzb pz on pz.qy_jh_pinzboid = fh.qy_jh_pinzb_fk left join qy_zhuanmb z on z.qy_zhillsb_fk = zlls.qy_zhillsboid where (zlls.cyfs = 3 or zlls.cyfs = 4) and zlls.meiylx = '存样抽样' and z.qy_zhuanmlb_fk = 'A22C1364-441D-D4D8-0A71-9BA6287C71D9') tab1 left join qy_zhillsb ls on (ls.qy_zhilb_fk = tab1.qy_zhilb_fk and ls.cyfs = 0) left join qy_zhuanmb zm on zm.qy_zhillsb_fk = ls.qy_zhillsboid where to_char(daohrq, 'yyyy-mm-dd') between '2017-09-01' and '2017-09-23' and zm.qy_zhuanmlb_fk = 'A22C1364-441D-D4D8-0A71-9BA6287C71D9' order by daohrq
当你无法控制自己的情绪
将时间一分一秒地花在随大流、追热点、逞能斗气、不干实事
人生就会像一架坏掉的机器，创造不出优质的产品
