Mysql、Oracle、Sql Server、达梦之间sql的差异

1：分页查询

Sql Server：

<bind name="startRow" value="(page - 1) * limit + 1"/>
<bind name="endRow" value="page * limit"/>
SELECT *FROM (SELECT ROW_NUMBER() OVER (<if test="sortZd!= null and sortZd!= ''">ORDER BY ${sortZd}<if test="sort!= null and sort!= ''">${sort}</if></if><!-- 默认排序 --><if test="sortZd == null or sortZd == ''">ORDER BY jy.JYSJ</if>) AS RowNum,<!-- 具体的查询sql -->
) AS PagedResults
WHERE RowNum BETWEEN #{startRow} AND #{endRow}

分页Count查询：

SELECT COUNT(*) AS total_count
FROM ( <!-- 具体的查询sql -->
) subquery;

Oracle：

<bind name="startRow" value="(page - 1) * limit + 1"/>
<bind name="endRow" value="page * limit"/>
SELECT * FROM (SELECT inn.*, ROWNUM AS rn FROM (SELECT * FROM ( ) a<!-- 动态排序逻辑（if标签实现） --><if test="sortZd != null and sortZd != ''">ORDER BY ${sortZd} <if test="sort != null and sort != ''">${sort}</if></if><!-- 默认排序 --><if test="(sortZd == null or sortZd == '') and sort == null">ORDER BY FYSJ DESC</if>) innWHERE ROWNUM &lt;= #{endRow})
WHERE rn &gt;= #{startRow}

分页Count查询：

SELECT COUNT(*) AS total_count
FROM ( ) subquery

达梦：

<bind name="startRow" value="(page - 1) * limit"/><bind name="endRow" value="limit"/>SELECT * FROM () a<if test="sortZd != null and sortZd != ''">ORDER BY ${sortZd}<if test="sort != null and sort != ''">${sort}</if></if><if test="sortZd == null or sortZd == ''">ORDER BY BATJ_XGRZ.xgsj DESC</if>
OFFSET #{startRow} ROWS FETCH FIRST #{endRow} ROWS ONLY

分页Count查询：

SELECT COUNT(*) AS total_count
FROM ( ) subquery

mysql：

SELECT * FROM table<br>ORDER BY col<br>LIMIT 10 OFFSET 10;

2：时间处理

功能	Oracle	SQL Server	达梦	MySQL
日期格式化	TO_CHAR(date, 'YYYY-MM-DD')	CONVERT(VARCHAR, date, 23)	TO_CHAR(date, 'YYYY-MM-DD')	DATE_FORMAT(date, '%Y-%m-%d')
当前日期时间	SYSDATE	GETDATE()	SYSDATE	NOW()
日期加减	date + INTERVAL '1' DAY	DATEADD(DAY, 1, date)	date + INTERVAL '1' DAY	date + INTERVAL 1 DAY

Oracle：

BETWEEN TO_DATE(#{start}, 'YYYY-MM-DD') AND TO_DATE(#{end}, 'YYYY-MM-DD')TO_DATE(#{begDate}, 'YYYY-MM-DD')TO_DATE(#{endDate}, 'YYYY-MM-DD')TO_CHAR(DateColumn, 'YYYY-MM-DD') AS FormattedDate

Sql server：

CONVERT(VARCHAR(10), DateColumn, 23) AS FormattedDateCYRQ >= #{paramMap.begdate}AND CYRQ < #{paramMap.plusDays}

达梦：

3：模糊查询

sqlserver:

<if test="dto.keyword != null and dto.keyword != ''">AND (SRM1 LIKE #{dto.keyword} + '%'OR MDMM LIKE '%' + #{dto.keyword} + '%'OR MBZM10 LIKE #{dto.keyword} + '%')</if>

oracle:

<if test="dto.keyword != null and dto.keyword != ''">AND (SRM1 LIKE #{dto.keyword} || '%'OR MDMM LIKE '%' || #{dto.keyword} || '%'OR MBZM10 LIKE #{dto.keyword} || '%')</if>

3：函数和语法差异

功能	Oracle	SQL Server	达梦	MySQL
空值处理	NVL(col, default)	ISNULL(col, default)	NVL(col, default)	IFNULL(col, default)
条件表达式	DECODE(col, val1, res1, res2)	CASE WHEN col=val1 THEN res1...	DECODE() 或 CASE	CASE WHEN...
自增主键	SEQUENCE + TRIGGER	IDENTITY(1,1)	IDENTITY 或 SEQUENCE	AUTO_INCREMENT
注释	-- 单行 / /* 多行 */	同上	同上	同上