mysql转oracle语句_MySql语句转Oracle的坑啊

1.模糊查询

Mysql

SELECT *

FROM USER

WHERE username like CONCAT('%','王','%')

Oracle

SELECT *

FROM USER

WHERE username like '%'||'王'||'%'

Oracle里面也有concat函数，如果要使用CONCAT函数的话，我们需要使用两个CONCAT函数，对模糊查询进行拼接,也是可以的。这里的话，我就不演示了。

image.png

2.按照时间查询

Mysql

SELECT *

FROM USER

WHERE create_time between ''2019-12-10 12:22:29" and "2019-12-11 11:22:29"

Oracle

SELECT *

FROM USER

WHERE create_time between TO_DATE(''2019-12-10 12:22:29" , 'yyyy-mm-dd hh24:mi:ss' ) and TO_DATE("2019-12-11 11:22:29", 'yyyy-mm-dd hh24:mi:ss' )

3.分页查询

MySQL：

SELECT

sheet.*

FROM

sheetA act,

sheetB sheet,

sheetC actop

WHERE

AND sheetB.ACT_INST_ID = sheetA.ACTIVITYINSTID

AND sheetA.REPOID = sheetC.REPO_ID

Limit 0,10

Oracle：

单表分页

SELECT

*

FROM

sheetA

WHERE

ROWID NOT IN ( SELECT ROWID FROM sheetA WHERE ROWNUM <= 0 )

AND ROWNUM <= 10

关联查询分页

SELECT

sheets.*

FROM

(

SELECT

sheet.* ,

ROWNUM "rownum"

FROM

sheetA act,

sheetB sheet,

sheetC actop

WHERE

AND sheetB.ACT_INST_ID = sheetA.ACTIVITYINSTID

AND sheetA.REPOID = sheetC.REPO_ID

) sheets

WHERE

sheets."rownum" >= 0

AND sheets."rownum" <= 5

后续遇到在更新.....