提供一个SQL请求者的例子(页 1) - Sage ERP X3 - 赛捷软件论坛 ERP SAGE|CRM SAGE|赛捷软件|软件定制开发|移动手机ERP开发

dannis 发表于 2009-2-15 08:34

提供一个SQL请求者的例子

SELECT EEE.GZZX,EEE.GZZXMC,EEE.GDH,EEE.CPDM,ITMMASTER.ITMDES1_0,EEE. XQSL,EEE.LQSL,EEE.XHSL,EEE.THSL,EEE.BSSL,CASE WHEN EEE.SHSL<=0 OR EEE.SHSL=EEE.LQSL  THEN 0 ELSE EEE.SHSL END AS SHSL,ROUND(CASE WHEN EEE.SHSL<=0 OR EEE.SHSL=EEE.LQSL  THEN 0 ELSE EEE.SHSL END /CASE WHEN EEE.XHSL=0 THEN 1 ELSE EEE.XHSL END,2)*100 AS XHL  FROM (SELECT DDD.GZZX,WORKSTATIO.WSTDES_0  AS GZZXMC,DDD.GDH,DDD.CPDM,DDD. XQSL,DDD.LQSL,DDD.XHSL,DDD.THSL,DDD.BSSL,(DDD.LQSL-DDD.XHSL-DDD.THSL-DDD.BSSL) AS SHSL  FROM ( SELECT CCC.GZZX,CCC.GDH,CCC.CPDM,SUM(XQSL) AS XQSL,SUM(LQSL) AS LQSL,SUM(XHSL) AS XHSL,SUM(THSL) AS THSL,SUM(BSSL) AS BSSL FROM ( SELECT MFGOPE.MFGNUM_0 AS GDH,MFGOPE.EXTLAB_0 AS GZZX,MFGMAT.ITMREF_0 AS CPDM,MFGMAT.RETQTY_0 AS XQSL,0 AS LQSL,0 AS XHSL,0 AS THSL,0 AS BSSL FROM MFGOPE LEFT JOIN MFGMAT ON MFGOPE.MFGNUM_0=MFGMAT.MFGNUM_0 AND MFGOPE.OPENUM_0 = MFGMAT.BOMOPE_0 WHERE MFGOPE.XMMSTA_0=2 AND SUBSTR(MFGMAT.ITMREF_0,1,1)='2' AND MFGOPE.EXTLAB_0<>' ' AND MFGOPE.XOPESTR_0>=%1% AND MFGOPE.XOPESTR_0<=%2% UNION ALL SELECT QQQ.GDH AS GDH,MFGOPE.EXTLAB_0 AS GZZX,QQQ.CPDM AS CPDM,0 AS XQSL,QQQ.LQSL AS LQSL,0 AS XHSL,0 AS THSL,0 AS BSSL FROM (SELECT MFGNUM_0 AS GDH,OPENUM_0 AS GXH,ITMREF_0 AS CPDM,SUM(USEQTY_0) AS LQSL FROM MFGMATTRK WHERE SUBSTR(ITMREF_0,1,1)='2' AND MFGNUM_0 IN ( SELECT DISTINCT MFGOPE.MFGNUM_0 FROM MFGMAT LEFT JOIN MFGOPE ON MFGOPE.MFGNUM_0=MFGMAT.MFGNUM_0 AND MFGOPE.OPENUM_0 = MFGMAT.BOMOPE_0 WHERE MFGOPE.XMMSTA_0=2 AND SUBSTR(MFGMAT.ITMREF_0,1,1)='2' AND MFGOPE.EXTLAB_0<>' ' AND MFGOPE.XOPESTR_0>=%1% AND MFGOPE.XOPESTR_0<=%2%) GROUP BY MFGNUM_0,OPENUM_0,ITMREF_0)QQQ LEFT JOIN MFGOPE ON QQQ.GDH=MFGOPE.MFGNUM_0 AND QQQ.GXH=MFGOPE.OPENUM_0 UNION ALLSELECT GGGG.GDH AS GDH,GGGG.GZZX AS GZZX,BOMD.CPNITMREF_0 AS CPDM,0 AS XQSL,0 AS LQSL,BOMD.BOMQTY_0*GGGG.RKSL AS XHSL, 0 AS THSL,0 AS BSSL FROM ( SELECT RRRR.GDH AS GDH,RRRR.GZZX AS GZZX,RRRR.CPDM AS CPDM,MFGITM.BOMALT_0 AS WLQD,RRRR.RKSL AS RKSL FROM (SELECT MFGITMTRK.MFGNUM_0 AS GDH,MFGITMTRK.WST_0 AS GZZX,MFGITMTRK.ITMREF_0 AS CPDM,MFGITMTRK.UOMCPLQTY_0/ITMMASTER.SAUSTUCOE_0 AS RKSL FROM MFGITMTRK LEFT JOIN ITMMASTER ON MFGITMTRK.ITMREF_0=ITMMASTER.ITMREF_0 WHERE SUBSTR(MFGITMTRK.ITMREF_0,1,1)='1' )RRRR LEFT JOIN MFGITM ON RRRR.GDH=MFGITM.MFGNUM_0 )GGGG RIGHT JOIN BOMD ON GGGG.CPDM=BOMD.ITMREF_0 AND GGGG.WLQD=BOMD.BOMALT_0 WHERE SUBSTR(BOMD.CPNITMREF_0,1,1)='2' AND GGGG.GDH IN ( SELECT DISTINCT MFGOPE.MFGNUM_0 FROM MFGMAT LEFT JOIN MFGOPE ON MFGOPE.MFGNUM_0=MFGMAT.MFGNUM_0 AND MFGOPE.OPENUM_0 = MFGMAT.BOMOPE_0 WHERE MFGOPE.XMMSTA_0=2 AND SUBSTR(MFGMAT.ITMREF_0,1,1)='2' AND MFGOPE.EXTLAB_0<>' ' AND MFGOPE.XOPESTR_0>=%1% AND MFGOPE.XOPESTR_0<=%2%)UNION ALL SELECT BBB.MFGNUM_0 AS GDH, MFGOPE.EXTLAB_0 AS GZZX,BBB.ITMREF_0 AS CPDM,0 AS XQSL,0 AS LQSL,0 AS XHSL,BBB.USEQTY_0 AS THSL,0 AS BSSL  FROM (SELECT MFGMATTRK.MFGNUM_0,MFGMATTRK.OPENUM_0,MFGMATTRK.ITMREF_0,MFGMATTRK.USEQTY_0 FROM MFGMATTRK LEFT JOIN STOJOU ON MFGMATTRK.MFGTRKNUM_0=STOJOU.VCRNUM_0 WHERE MFGMATTRK.MATTYP_0=3 AND STOJOU.STA_0='A1' AND SUBSTR(MFGMATTRK.ITMREF_0,1,1)=2)BBB LEFT JOIN MFGOPE ON BBB.MFGNUM_0=MFGOPE.MFGNUM_0 AND BBB.OPENUM_0=MFGOPE.OPENUM_0 WHERE MFGOPE.EXTLAB_0<>' ' AND BBB.MFGNUM_0 IN ( SELECT DISTINCT MFGOPE.MFGNUM_0 FROM MFGMAT LEFT JOIN MFGOPE ON MFGOPE.MFGNUM_0=MFGMAT.MFGNUM_0 AND MFGOPE.OPENUM_0 = MFGMAT.BOMOPE_0 WHERE MFGOPE.XMMSTA_0=2 AND SUBSTR(MFGMAT.ITMREF_0,1,1)='2' AND MFGOPE.EXTLAB_0<>' ' AND MFGOPE.XOPESTR_0>=%1% AND MFGOPE.XOPESTR_0<=%2%) UNION ALL SELECT BBB.MFGNUM_0 AS GDH, MFGOPE.EXTLAB_0 AS GZZX,BBB.ITMREF_0 AS CPDM,0 AS XQSL,0 AS LQSL,0 AS XHSL,BBB.USEQTY_0 AS THSL,0 AS BSSL  FROM (SELECT MFGMATTRK.MFGNUM_0,MFGMATTRK.OPENUM_0,MFGMATTRK.ITMREF_0,MFGMATTRK.USEQTY_0 FROM MFGMATTRK LEFT JOIN STOJOU ON MFGMATTRK.MFGTRKNUM_0=STOJOU.VCRNUM_0 WHERE MFGMATTRK.MATTYP_0=3 AND STOJOU.STA_0='R9' AND SUBSTR(MFGMATTRK.ITMREF_0,1,1)=2)BBB LEFT JOIN MFGOPE ON BBB.MFGNUM_0=MFGOPE.MFGNUM_0 AND BBB.OPENUM_0=MFGOPE.OPENUM_0 WHERE MFGOPE.EXTLAB_0<>' ' AND BBB.MFGNUM_0 IN ( SELECT DISTINCT MFGOPE.MFGNUM_0 FROM MFGMAT LEFT JOIN MFGOPE ON MFGOPE.MFGNUM_0=MFGMAT.MFGNUM_0 AND MFGOPE.OPENUM_0 = MFGMAT.BOMOPE_0 WHERE MFGOPE.XMMSTA_0=2 AND SUBSTR(MFGMAT.ITMREF_0,1,1)='2' AND MFGOPE.EXTLAB_0<>' ' AND MFGOPE.XOPESTR_0>=%1% AND MFGOPE.XOPESTR_0<=%2%) ) CCC WHERE SUBSTR(CCC.CPDM,1,1)='2'  AND  CCC.GZZX >=%3%  AND  CCC.GZZX <=%4% GROUP BY  CCC.GZZX,CCC.GDH,CCC.CPDM ORDER BY CCC.GZZX,CCC.GDH,CCC.CPDM )DDD LEFT JOIN WORKSTATIO ON DDD.GZZX=WORKSTATIO.WST_0 )EEE LEFT JOIN ITMMASTER ON EEE.CPDM=ITMMASTER.ITMREF_0这个例子具有很有代表性,大家可以好好研究一下.<IMG src="http://www.sagesoft.cn/bbs/images/Emotions/11.gif"> 红色字体部分请多注意［此帖子已被 dannis 在 2009-2-15 0:36:05 编辑过］

Jerry.huang 发表于 2009-2-25 22:35

Re:提供一个SQL请求者的例子

晕，谁这么厉害。上面功能干什么用的阿

luoxiong 发表于 2009-6-19 18:29

Re:提供一个SQL请求者的例子

牛人啊，能不能说说这个是查询什么的？工单追踪情况？

dannis 发表于 2009-6-19 22:53

Re:提供一个SQL请求者的例子

是就是工单追踪用的SQL请求者。

页: [1]

赛捷软件论坛's Archiver