常见问题解决.doc

1、查询供应商销售排名(1)select a.gys 供应商,b.sbcname 供应商名称,a.xssr 含税收入,a.hscbjje 含税成本金额,a.hscbtz 含税成本调整,a.supzk 供应商折扣,a.ml_sup 毛利,a.ml_sup/a.xssr 毛利率 from (SELECT scdsupid gys, SUM(XSSR + PFSR ) XSSR, -销售收入SUM(XSHSCBJJE + PFHSCBJJE) HSCBJJE,-含税成本金额SUM(XSHSCBJAD + PFHSCBJAD) HSCBTZ,-含税成本调整SUM(XSSUPZK + PFSUPZK) SUPZK,-供应商折扣SUM(XSSR+PFSR)-SUM(XSHSJJJE+PFHSJJJE+XSHSJJAD+PFHSJJAD)+SUM(XSSUPZK + PFSUPZK) ML_SUP -毛利（含供应商折扣）FROM SALECOSTDAYwhere scddate= to_date(sysdate)and SCDMFID IN (select OSRMFID from operseachrange where osrroleid=1010)group by scdsupid having sum(xssr+pfsr)!=0) a,supplierbase b where a.gys=b.sbid order by a.xssr desc查询供应商销售排名(2)select a.gys 供应商,b.sbcname 供应商名称,a.xssr 含税收入,a.hscbjje 含税成本金额,a.hscbtz 含税成本调整,a.supzk 供应商折扣,a.ml_sup 毛利,a.ml_sup/a.xssr 毛利率 from (SELECT scdsupid gys, SUM(XSSR + PFSR ) XSSR, -销售收入SUM(XSHSCBJJE + PFHSCBJJE) HSCBJJE,-含税成本金额SUM(XSHSCBJAD + PFHSCBJAD) HSCBTZ,-含税成本调整SUM(XSSUPZK + PFSUPZK) SUPZK,-供应商折扣SUM(XSSR+PFSR)-SUM(XSHSJJJE+PFHSJJJE+XSHSJJAD+PFHSJJAD)+SUM(XSSUPZK + PFSUPZK) ML_SUP -毛利（含供应商折扣）FROM SALECOSTDAYwhere scddate=to_date(20080226,yyyymmdd) and scddate= TO_DATE(20080526,YYYYMMDD) AND JXCGOODSLIST.JGLFSDATE TO_DATE(20080526,YYYYMMDD) + 1 ) AND JXCGOODSLIST.JGLMARKET = 1006 AND SUBSTR(JGLCATID,1,2)=GOODSCAT.CATCODE ANDJXCGOODSLIST.JGLTRAN IN (E,F,G,H)AND JXCGOODSLIST.JGLWMID 4 -经营方式不等于联营 - AND JXCGOODSLIST.JGLSJ =to_date(20060413,yyyymmdd) and scddate=to_date(20060413,yyyymmdd) and scddate=to_date(20060801,yyyymmdd) and spldate=SYSDATE-60 AND JGLTRAN IN (E,F,G,H)-从jxcgoodslist表中查询两个月内有销售的商品编码和柜组 AND JGLGDID IN(SELECT GMFGDID FROM GOODSMFRAME WHERE GMFMAINTDATESYSDATE-7) -从goodsmframe表中查询出七天前存在的商品编码-从jxcgoodslist表中查询两个月内有销售的商品编码和柜组，不包括最近一周内录入的新品编码和柜组GROUP BY JGLMFID,JGLGDID HAVING SUM(-1*JGLSL)=1-从jxcgoodslist表中查询两个月内销售数量小于等于1的商品编码和柜组，不包括最近一周内录入的新品编码和柜组UNION (SELECT GMFMFID,GMFGDID FROM GOODSMFRAME WHERE GMFMAINTDATE=SYSDATE-60 AND JGLTRAN IN (E,F,G,H)7、查询部门销售汇总，毛利率select sum(xssr) 销售收入,(SUM(XSSR+PFSR)-SUM(XSHSJJJE+PFHSJJJE+XSHSJJAD+PFHSJJAD)+SUM(XSSUPZK + PFSUPZK)/sum(xssr) 毛利率from salecostday where scdmfid in (select osrmfid from operseachrange where osrroleid=1007)-生鲜-从operseachrange表中查询出角色的查询数据范围and scddate=to_date(20060626,yyyymmdd) and scddate=to_date(20060626,yyyymmdd) and scddate=to_date(20060626,yyyymmdd) and scddate=to_date(20060626,yyyymmdd) and scddate=to_date(20060626,yyyymmdd) and scddate=to_date(20060626,yyyymmdd) and scddate= TO_DATE(2005-12-09,YYYY-MM-DD) AND SALECOSTDAY.SCDDATE =SYSDATE-60 AND JGLTRAN IN (E,F,G,H) AND JGLMFID LIKE 10060401%-jxcgoodslist AND JGLGDID IN(SELECT GMFGDID FROM GOODSMFRAME WHERE GMFMAINTDATESYSDATE-30) -goodsmframe-jxcgoodslistGROUP BY JGLMFID,JGLGDID HAVING SUM(-1*JGLSL)=180/库存大于180天的 AND SYSDATE - BTFFSRQ=TO_DATE(20051226,yyyymmdd) AND SCDDATE0.113、GOODSBATCH 和goodsstock 对比，查出有问题的商品SELECT * FROM goodsbatch WHERE btstatus=Y AND (BTGDID,BTMFID,BTSUPID,BTWMID) IN(select DISTINCT BTGDID,BTMFID,BTSUPID,BTWMID from goodsbatch where btstatus=YMINUSselect GSTGDID, GSTMFID, GSTSUPID, GSTWMID from goodsstock WHERE gstkcsl0)13、毛利小于等于0的经代销有库存商品SELECT TA.GSTGDID,GBCNAME,TA.GSTMFID,TA.GSTHSJJ,TB.GMPSJ,TB.GMPSJ-TA.GSTHSJJ FROM (SELECT GSTGDID,GSTMFID,GSTHSJJ FROM GOODSSTOCK WHERE GSTKCSL0) TA,(SELECT GMPGDID,GMPMFID,GMPSJ FROM GOODSMFPRICE) TB,GOODSBASE WHERE TA.GSTGDID=TB.GMPGDID AND TA.GSTMFID=TB.GMPMFID AND TA.GSTGDID=GBID AND TB.GMPGDID=GBIDAND TB.GMPSJ-TA.GSTHSJJ= TO_DATE(2006-07-10,YYYY-MM-DD) AND JXCGOODSLIST.JGLDATE =365ORDER BY BTMFID,BTFFSRQ16 查询没有配置多包装码例外价的商品明细。select a.gugdid,b.gmpmfid,c.dw,b.jj*c.jhl,b.sj*c.jhl from(select gugdid from goodsunits where gugdid in (select distinct gmpgdid from goodsmfprice where gmpmarket=1006)minus(select gmpgdid from goodsmfprice where gmpuid00 and gmpmarket=1006) a,(select gmpgdid spdm,gmpmfid,gmphsjj jj,gmpsj sj,gmpuid from goodsmfprice where gmpuid=00 and gmpmarket=1006) b,(select gugdid,gubzhl jhl,guunit dw from goodsunits) cwhere a.gugdid=b.spdm and a.gugdid=c.gugdid and b.spdm=c.gugdidorder by b.gmpmfid17 查询供应商当前库存SELECT GOODSSTOCK.GSTGDID商品代码,GOODSBASE.GBBARCODE 商品条码, GOODSBASE.GBCNAME 商品名称, GOODSBASE.GBSPEC规格, GOODSSTOCK.GSTMFID柜组, GOODSSTOCK.GSTKCSL库存数量, GOODSSTOCK.GSTHSJJ进价, FGETGOODSPRICE(GOODSSTOCK.GSTGDID) SJ FROM GOODSBASE, GOODSSTOCK WHERE GOODSBASE.GBID = GOODSSTOCK.GSTGDID AND GOODSSTOCK.GSTSUPID = 100368 AND GOODSSTOCK.GSTMFID like 100602%18、查询一品多供应商的商品库存和销售数量，能否调串销：select ta.gstgdid,ta.gstsupid,ta.dxkcsl,tb.gstsupid,tb.jxkcsl,tc.xssl from(select gstgdid,gstwmid,gstsupid,gstkcsl dxkcsl from goodsstock where gstmfid=1006020104 and gstwmid=2) ta,(select gstgdid,gstwmid,gstsupid,gstkcsl jxkcsl from goodsstock where gstmfid=1006020104 and gstwmid=1) tb,(select jglgdid,sum(-1*jglsl) XSSL from jxcgoodslist where jglsupid=100464 and jgltran in (E,F,G,H) group by jglgdid ) TCwhere ta.gstgdid=tb.gstgdid(+) and ta.gstgdid=tc.jglgdid(+);select ta.cgdcontno,sum(tb.shuliang) from (select cgdcontno,cgdgdid from contgoods where cgdmarket=1006) ta,(select sdgdid bianma,count(*) shuliang from selldetail where sdbillno in(select shbillno from sellhead where shdate=to_date(20060901,yyyymmdd) and shdate= TO_DATE(2006-09-25,YYYY-MM-DD) AND JXCMFRAMEDAY.JMDDATE = TO_DATE(20061202,YYYYMMDD) AND SALECOSTDAY.SCDDATE TO_DATE(20061202,YYYYMMDD) + 1 ) AND SALECOSTDAY.SCDHSRQ=SALECOSTDAY.SCDHSRQ AND SALECOSTDAY.SCDMKT=1006 AND SALECOSTDAY.SCDMKT=B.MFCODE HAVING SUM(XSSR+PFSR)0 GROUP BY SCDMKT,B.MFCODE,B.MFCNAMEUNION ALLSELECT SCDMKT,B.MFCNAME, SUM(XSSR + PFSR ) XSSR, (SUM(XSSR+PFSR)-SUM(XSHSJJJE+PFHSJJJE+XSHSJJAD+PFHSJJAD)+SUM(XSSUPZK + PFSUPZK)/sum(xssr+pfsr) MLl FROM SALECOSTDAY, (SELECT MFCODE,MFCNAME FROM MANAFRAME WHERE MFCLASS=1) B WHERE ( SALECOSTDAY.SCDDATE = TO_DATE(20061202,YYYYMMDD) AND SALECOSTDAY.SCDDATE TO_DATE(20061202,YYYYMMDD) + 1 ) AND SALECOSTDAY.SCDHSRQ=SALECOSTDAY.SCDHSRQ AND SALECOSTDAY.SCDMKT=0001 AND SALECOSTDAY.SCDMKT=B.MFCODE HAVING SUM(XSSR+PFSR)0 GROUP BY SCDMKT,B.MFCODE,B.MFCNAMEUNION ALLSELECT SCDMKT,B.MFCNAME, SUM(XSSR + PFSR ) XSSR, (SUM(XSSR+PFSR)-SUM(XSHSJJJE+PFHSJJJE+XSHSJJAD+PFHSJJAD)+SUM(XSSUPZK + PFSUPZK)/sum(xssr+pfsr) MLl FROM SALECOSTDAY, (SELECT MFCODE,MFCNAME FROM MANAFRAME WHERE MFCLASS=1) B WHERE ( SALECOSTDAY.SCDDATE = TO_DATE(20061202,YYYYMMDD) AND SALECOSTDAY.SCDDATE TO_DATE(20061202,YYYYMMDD) + 1 ) AND SALECOSTDAY.SCDHSRQ=SALECOSTDAY.SCDHSRQ AND SALECOSTDAY.SCDMKT=1004 AND SALECOSTDAY.SCDMKT=B.MFCODE HAVING SUM(XSSR+PFSR)0 GROUP BY SCDMKT,B.MFCODE,B.MFCNAMEUNION ALLSELECT SCDMKT,B.MFCNAME, SUM(XSSR + PFSR ) XSSR, (SUM(XSSR+PFSR)-SUM(XSHSJJJE+PFHSJJJE+XSHSJJAD+PFHSJJAD)+SUM(XSSUPZK + PFSUPZK)/sum(xssr+pfsr) MLl FROM SALECOSTDAY, (SELECT MFCODE,MFCNAME FROM MANAFRAME WHERE MFCLASS=1) B WHERE ( SALECOSTDAY.SCDDATE = TO_DATE(20061202,YYYYMMDD) AND SALECOSTDAY.SCDDATE TO_DATE(20061202,YYYYMMDD) + 1 ) AND SALECOSTDAY.SCDHSRQ=SALECOSTDAY.SCDHSRQ AND SALECOSTDAY.SCDMKT IN (1001,1003,1005,1007,1008,1009,1010,1011) AND SALECOSTDAY.SCDMFID=SALECOSTDAY.SCDMFID AND SALECOSTDAY.SCDWMID=SALECOSTDAY.SCDWMID AND SALECOSTDAY.SCDMKT=B.MFCODE HAVING SUM(XSSR+PFSR)0 GROUP BY SCDMKT,B.MFCODE,B.MFCNAME; 20查询时段交易笔数,交易金额,客单价SELECT COUNT(SHBILLNO) 交易笔数,SUM(SHOUGHTPAY) 交易金额,SUM(SHOUGHTPAY)/COUNT(SHBILLNO) 客单价FROM SELLHEAD WHERE SHDATE=TO_DATE(2006-11-13 08:00:01,YYYY-MM-DD HH24:Mi:SS) ANDSHDATE=TO_DATE(20061124,YYYYMMDD)-AND CSJHVCHR=4 AND CSJHTIME=TO_DATE(2006-11-24 18:00:01,YYYY-MM-DD HH24:Mi:SS)-AND (CSJHNUM/10)*3+CSJHJF0AND CSJHCDMNO IN (6005955420508,6012505250501,8016398180510,7071380700512,8072869100609)ORDER BY CSJHCDMNOSELECT BTMFID, sum(BTHSHSJE)FROM GOODSBATCH WHERE BTSTATUS=YAND GOODSBATCH.BTMFID=GOODSBATCH.BTMFID AND GOODSBATCH.BTSUPID=GOODSBATCH.BTSUPID AND GOODSBATCH.BTWMID = 1 AND SYSDATE - BTFFSRQ=90 AND BTMFID LIKE 000009% AND SYSDATE - BTFFSRQ=180GROUP BY BTMFID21、-门店