1.top 100 选取表中前100条
改为 limit #{limit},limit 为变量
2.获取当前日期getdate()
改为now()
3.id=#{id,jdbcType=BIGINT}
改为id=#{id},不需要指定数据类型
4.sqlserver查询需要nolock
而mysql不需要
5.字符串拼接
sqlserver：select * from A where contract_no like '%'+'DSP'+'%' order by id desc
mysql：select * from A where contract_no like concat('%','DSP','%') order by id desc
单向拼接用
concat('_%',#{waybillNo})
concat('%',#{waybillNo},'%')
6.分页查询时
mysql不支持ROW_NUMBER() OVER，也不支持with cte1 as(）语法
sqlserver：
<select id="getB" resultMap="BaseResultMap" parameterType="java.util.Map" >
with cte1 as
(
select
<include refid="Base_Column_List_T" />,t2.parta_no,t2.parta_name,t1.parta_num
from a(nolock) t1, b(nolock) t2 where t1.id=t2.contract_info_id
<include refid="Get_Where_Clause_Page" />
and t2.id in(select min(id) from c(nolock)
<where>
<if test="parta_name != null">
AND parta_name like '%'+#{parta_name, jdbcType=VARCHAR}+'%'
</if>
<if test="parta_no != null">
AND parta_no = #{parta_no, jdbcType=VARCHAR}
</if>
</where>
group by contract_info_id )
) ,
cte2 as(
select ROW_NUMBER() OVER (ORDER BY cte1.id desc) as RowNumber,<include refid="Base_Column_List" />,parta_no,parta_name,parta_num from
cte1
)
select <include refid="Base_Column_List" />,parta_no,parta_name,parta_num
from
cte2 where RowNumber between #{startRow} and #{endRow}
order by cte2.id desc
</select>
mysql：
<!-- 合同分页查询 -->
<select id="getB" resultMap="BaseResultMap" parameterType="java.util.Map" >
select
<include refid="Base_Column_List_T" />,t2.parta_no,t2.parta_name,t1.parta_num
from a t1, b t2 where t1.id=t2.contract_info_id
<include refid="Get_Where_Clause_Page" />
and t2.id in(select min(id) from c
<where>
<if test="parta_name != null">
AND parta_name like CONCAT(CONCAT('%', #{parta_name, jdbcType=VARCHAR}), '%')
</if>
<if test="parta_no != null">
AND parta_no = #{parta_no, jdbcType=VARCHAR}
</if>
</where>
group by contract_info_id )
order by t1.id desc
limit #{startRow},#{pageSize}
</select>
7.分页查询时注意 limit #{startRow},#{pageSize}取得startRow必须从0开始,可能在controller层要改变下起始页码，取决于引用的分页法，也要注意查询总数为0时，0-1=-1，起始页面不可以为-1，要区分对待。
8.sqlserver isnull(t1.parta_num,0)转化为mysql ifnull（t1.parta_num,0）
MySQL IFNULL函数是MySQL控制流函数之一，它接受两个参数，如果不是NULL，则返回第一个参数。 否则，IFNULL函数返回第二个参数。
两个参数可以是文字值或表达式。
以下说明了IFNULL函数的语法：
IFNULL(expression_1,expression_2);
SQL
如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果。
IFNULL函数根据使用的上下文返回字符串或数字。
如果要返回基于TRUE或FALSE条件的值，而不是NULL，则应使用IF函数。
9.int 类型 sqlserver默认为0，而mysql默认为null。
10.多表关联update区别
sqlserver：
update a set a.invoice_apply_amount=a.invoice_apply_amount+b.invoicing_amount,a.uninvoiced_balance =a.uninvoiced_balance+b.invoicing_amount,a.invoiced_amount=a.invoiced_amount-b.invoicing_amount from A a,B b where a.contract_no =b.contract_no and b.apply_no='11111'
mysql：
UPDATE A a,B b
SET a.invoice_apply_amount=a.invoice_apply_amount+b.invoicing_amount,a.uninvoiced_balance =a.uninvoiced_balance+b.invoicing_amount,a.invoiced_amount=a.invoiced_amount-b.invoicing_amount
WHERE a.contract_no =b.contract_no and b.apply_no='11111'