mysql转oracle的方言_有兴趣的话把下面的oracle 转换成 MySQL

1 SELECT A.ROW_ID, -- 门店编码2 A.CUSTOMER_NAME, -- 门店名称3 B.CUSTOMER_STATUS, -- 客户状态4 C.VALUE CUSTOMER_TYPE, -- 门店类别5 NVL(D.MONTH_3_AMT, 0) MONTH_3_AMT, -- 三个月均销售金额6 NVL(E.VISIT_COUNT, 0) BE_VISIT_COUNT, -- 上月拜访次数7 TO_CHAR(TO_DATE(F.VISI_SYS_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') VISI_SYS_DATE, -- 最后拜访时间8 NVL(B.VISIT_COUNT, 0) VISIT_COUNT_LIST, -- 当月拜访次数9 CASE10 WHEN NVL(B.VISIT_COUNT, 0) = 0 THEN11 '0秒'12 ELSE13 DECODE(FLOOR(NVL(NVL(F.SIS, 0) / NVL(B.VISIT_COUNT, 0), 0) / 3600),14 0,15 '',16 FLOOR(NVL(NVL(F.SIS, 0) / NVL(B.VISIT_COUNT, 0), 0) / 3600) || '小时') ||17 DECODE(FLOOR(MOD(NVL(NVL(F.SIS, 0) / NVL(B.VISIT_COUNT, 0), 0),18 3600) / 60),19 0,20 '',21 FLOOR(MOD(NVL(NVL(F.SIS, 0) / NVL(B.VISIT_COUNT, 0), 0),22 3600) / 60) || '分') ||23 ROUND(MOD(NVL(NVL(F.SIS, 0) / NVL(B.VISIT_COUNT, 0), 0), 60), 2) || '秒'24 END AVG_TIME, -- 拜访时间平均时间25 F.USERNAME, -- 拜访人员26 A.SALESMAN, -- 业务员27 BP.NAME SALES_NAME -- 业务员28 FROM BASE_CUSTOMER A -- 客户表29 LEFT JOIN SFA_VISIT_ACC_STATUS B -- 客户状态30 ON A.ROW_ID = B.CUSTOMER_ID31 AND A.DID = B.DID32 AND B.VISIT_MONTH = MONTH33 LEFT JOIN BASE_DICT C -- 客户类型34 ON A.CUSTOMER_TYPE = C.ROW_ID35 AND A.DID = C.DID36 AND C.CODE = '客户类型'37 LEFT JOIN (SELECT D.CUSTOMER_ID, -- 客户编码38 ROUND(SUM(D.VISIT_DN_AMT) / 3, 2) MONTH_3_AMT, -- 此客户三个月内销售的平均值39 D.DID40 FROM SFA_VISIT_ACC_STATUS D41 WHERE VISIT_MONTH >=42 TO_CHAR(ADD_MONTHS(TO_DATE(MONTH, 'yyyymm'), -2),43 'yyyymm')44 GROUP BY CUSTOMER_ID, DID) D45 ON A.ROW_ID = D.CUSTOMER_ID46 AND A.DID = D.DID47 LEFT JOIN SFA_VISIT_ACC_STATUS E48 ON A.ROW_ID = E.CUSTOMER_ID49 AND A.DID = E.DID50 AND E.VISIT_MONTH =51 TO_CHAR(ADD_MONTHS(TO_DATE(MONTH, 'yyyymm'), -1), 'yyyymm')52 LEFT JOIN (SELECT *53 FROM (SELECT ROW_NUMBER() OVER(PARTITION BY STORECODE ORDER BY VISIT_ENDTIME DESC) RN,54 STORECODE, -- 门店编码55 VISIT_STARTTIME, -- 拜访开始时间56 VISIT_ENDTIME, -- 拜访结束时间57 SI,58 SUM(SI) OVER(PARTITION BY /*USERNAME,*/ STORECODE ORDER BY VISI_SYS_DATE) SIS,59 USERNAME,60 VISI_SYS_DATE61 FROM (SELECT STORECODE, -- 客户ID62 VISIT_STARTTIME, -- 开始时间63 NVL(VISIT_ENDTIME, VISIT_STARTTIME) VISIT_ENDTIME, -- 结束时间64 (TO_DATE(NVL(DECODE(T.VISIT_ENDTIME,65 '1900-01-01 00:00:00',66 '',67 T.VISIT_ENDTIME),68 VISIT_STARTTIME),69 'yyyy-mm-dd hh24:mi:ss') -70 TO_DATE(T.VISIT_STARTTIME,71 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60 SI,72 USERNAME, -- 拜访人员73 VISI_SYS_DATE -- 拜访时间74 FROM SFA_VISIT_LIST T75 WHERE SUBSTR(T.VISI_SYS_DATE, 1, 6) = MONTH76 ORDER BY STORECODE)77 ORDER BY STORECODE)78 WHERE RN = 1) F79

80 ON A.ROW_ID = F.STORECODE81 LEFT JOIN BASE_PERSON BP82 ON A.SALESMAN = BP.ROW_ID83 AND A.DID = BP.DID84 WHERE A.DID = pDid85 --if pKeyWord不为空86 --if pKey == "全部"87 AND (UPPER(A.ROW_ID) LIKE '%pKeyWord%'88 OR UPPER(A.CUSTOMER_NAME) LIKE '%pKeyWord%'89 OR UPPER(F.USERNAME) LIKE '%pKeyWord%'90 OR UPPER(BP.Name) LIKE '%pKeyWord%'91 OR UPPER(C.VALUE) LIKE '%pKeyWord%'92 OR UPPER(B.CUSTOMER_STATUS) LIKE '%pKeyWord%'93 )94 --if pKey == "客户"95 AND (UPPER(A.ROW_ID) LIKE '%pKeyWord%'96 OR UPPER(A.CUSTOMER_NAME) LIKE '%pKeyWord%'97 )98 --if pKey == "拜访人员"99 AND (UPPER(F.USERNAME) LIKE '%pKeyWord%' )100 --if pKey == "业务员"101 AND (UPPER(BP.Name) LIKE '%pKeyWord%' )102 --if pKey == "客户类型"103 AND (UPPER(C.VALUE) LIKE '%pKeyWord%')104 --if pKey == "客户状态"105 AND (UPPER(B.CUSTOMER_STATUS) LIKE '%pKeyWord%' )106 --if W_status不为空107 AND a.status=W_status108 ORDER BY VISIT_COUNT_LIST DESC