mysql和oracle的sql语法

1.插入语句：

1）oracle

<insert id="add" parameterType="Object">        <selectKey  resultType="java.lang.Integer" order="BEFORE" keyProperty="id">            SELECT T_TASK_SEQ.NEXTVAL as id from DUAL        </selectKey>        insert into        t_task(id,branch_no,branch_name,task_no,task_name,branch_total,task_type,start_date,end_date)        values(#{id},#{branchNo},#{branchName},#{taskNo},#{taskName},#{branchTotal},#{taskType},#{startDate},#{endDate})    </insert>

oracle进行插入时要进行序列

2.模糊匹配

<sql id="baseWhereClause">        where 1=1        <trim suffixOverrides=",">            <if test="taskName != null and taskName != ''">                and task_name like concat(concat('%','${taskName}'),'%')            </if>            <if test="taskType != null and taskType != ''">                and task_type=#{taskType}            </if>            <if test="branchNo != null and branchNo != ''">                and branch_no=#{branchNo}            </if>        </trim>    </sql>

模糊匹配建议用‘$’美元符号，不要用‘#’符号，这样oracle和mysql都能兼容。

3.子查询

oracle 提供了with as 函数方便多表查询

WITH diff AS ( -- 差异化        SELECT        s.STORE_ID AS ID,        s.STORE_NAME AS NAME,        "SUM" (A .xsje) AS salesAmount,        "SUM" (A .xssl) AS salesNum        FROM        rtrycxf A,        bm c        RIGHT JOIN H_STORE s ON c.DZBM01 = s.STORE_ID        WHERE        A .cxflx = 1        AND A .jzrq = TRUNC (SYSDATE, 'dd')        AND A .bm01 = c.bm01        <include refid="StoreQuery" />        GROUP BY        s.STORE_ID,        s.STORE_NAME        ),        total AS (  -- 销售总额        SELECT        s.STORE_ID AS ID,        s.STORE_NAME AS NAME,        "SUM" (A .xsje) AS salesAmount,        "SUM" (A .xssl) AS salesNum        FROM        rtrycxf A,        bm c        RIGHT JOIN H_STORE s ON c.DZBM01 = s.STORE_ID        WHERE        A .jzrq = TRUNC (SYSDATE, 'dd')        AND A .bm01 = c.bm01        <include refid="StoreQuery" />        GROUP BY        s.STORE_ID,        s.STORE_NAME        ) SELECT        ROW_NUMBER () OVER (        ORDER BY        diff.salesAmount DESC        ) RANK,        diff. ID ID,        diff. NAME NAME,        diff.salesAmount salesAmount,        diff.salesNum salesNum,        "DECODE"(TOTAL.SALESAMOUNT, 0, 0,diff.salesAmount / total.salesAmount) rate        FROM        diff,        total        WHERE        diff. ID = total. ID