mysql和oracle的sql语法_mysql sql加#的功能 oracle怎末用

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>
1
2
3
4
5
6
7
8

oracle进行插入时要进行序列

2.模糊匹配

oracle:

<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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

mysql:

<if test="driver != null and driver != ''">
                and (t.driverNo=#{driver} OR t.driver_name LIKE concat(concat('%',#{driver}),'%'))
            </if>
1
2
3

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52