oracle数据库 转 mysql 数据库
1、rownum
oracle:select u.name
from u
from wo
where u.login_name = wo.APPLY_USER
and rownum = 1
mysql: select u.name
from u
from wo
where u.login_name = wo.APPLY_USER
LIMIT 1
2、START WITH CONNECT BY PRIOR
需求是根据传入的单位 拿到下面所以的单位集合
oracle: select a.org_no
from o_org a
WHERE instr(a.org_Name, '撤销') = 0
START WITH a.org_no =#{orgNo}
CONNECT BY PRIOR ORG_NO = P_ORG_NO
mysql: #如果省公司我就不传条件查所以
<if test ="orgNo !=null and orgNo !='' and orgNo !='43101' ">
and t.org_no LIKE CONCAT('%',#{orgNo},'%' )
</if>
3、decode
oracle: select
decode(t.DEFECT_STATUS ,'04',t.DEFECT_EXAMINE_TIME,'') as realWorkTime,
decode(t.DEFECT_STATUS,
'01',
'未派发',
'02',
'消缺中',
'03',
'待审核',
'04',
'已消缺',
'') as statusCodeLabel
from t;
mysql: select
if(t.DEFECT_STATUS ='04',t.DEFECT_EXAMINE_TIME,'') as realWorkTime,
(case
when t.DEFECT_STATUS='01' then '未派发'
when t.DEFECT_STATUS='02' then '消缺中'
when t.DEFECT_STATUS='03' then '待审核'
when t.DEFECT_STATUS='04' then '已消缺'
else '' end) as statusCodeLabel
from t
4、to_date
oracle: select to_date('', ' yyyy - MM - dd ') as nedWorkTime from t
mysql: select str_to_date('', ' yyyy - MM - dd ') as nedWorkTime from t
5、nvl
oracle: select nvl(count,0) as workOrderCount from t
mysql: select ifnull(count,0) as workOrderCount from t
6、trunc
oracle: select
*
from t
where
trunc(t.TASK_CREATE_TIME,'yyyy') <= trunc(to_date(#{staticSearchTimeB},'yyyy'),'yyyy')
mysql: select
*
from t
where
DATE_FORMAT(t.TASK_CREATE_TIME,'%Y') <= DATE_FORMAT(str_to_date(#{staticSearchTimeB},'yyyy'),'%Y')
6、to_date
oracle: select
*
from t
where
trunc(t.TASK_CREATE_TIME,'yyyy') <= trunc(to_date(#{staticSearchTimeB},'yyyy'),'yyyy')
mysql: select
*
from t
where
DATE_FORMAT(t.TASK_CREATE_TIME,'%Y') <= DATE_FORMAT(str_to_date(#{staticSearchTimeB},'yyyy'),'%Y')
7、cast
oracle: select
cast(s.name as nvarchar2(255)) as recivedName,
cast('' as DATE) as need_time,
from t
-- 二进制,同带binary前缀的效果 : BINARY
--字符型,可带参数 : CHAR()
--日期 : DATE
--时间: TIME
--期时间型 : DATETIME
--浮点数 : DECIMAL
--整数 : SIGNED
--无符号整数 : UNSIGNED
mysql: select
cast(s.name as char(255)) as recivedName,
cast('' as DATE) as need_time,
from t
8、rank() over(partition by … ) order by
oracle:
select a.indexValue,
rank() over(partition by substr(a.orgNo, 1, 3) order by a.indexValue desc) proviceRank,
rank() over(partition by substr(a.orgNo, 1, 5) order by a.indexValue desc) cityRank
from table
group by
a.org_no
mysql:
(select a.indexValue,
IF ( @orgNo = substr(a.orgNo, 1, 3), @rank := @rank + 1, @rank := 1 ) AS proviceRank,
( @orgNo := substr(a.orgNo, 1, 3) ) o,
IF ( @orgNo1 = substr(a.orgNo, 1, 5), @rank1 := @rank1 + 1, @rank1 := 1 ) AS cityRank,
( @orgNo1 := substr(a.orgNo, 1, 5) ) o1
from table
group by
a.org_no)a,
( select @rank :=0,@orgNo := null,@rank1 :=0,@orgNo1 := null ) b
ORDER BY a.indexValue desc
9、merge into using (修改或新增)
merge into table a using (
<foreach collection="list" item="item" separator=" union all ">
select #{item.id} as id,
#{item.name} as name,
#{itemorgNo} as orgNo
from dual
</foreach>
)t on (a.id = t.id)
where matched then
update set
a.create_time= sysdate
when not matched then
insert( id,name,orgNo,create_time)values(t.id,t.name,t.orgNo,sysdae)
mysql: #需要配合联合唯一索引进行修改或新增
insert into table ( id,name,orgNo,create_time)
<foreach collection="list" item="item" separator=" union all ">
#{item.id} as id,
#{item.name} as name,
#{itemorgNo} as orgNo
</foreach>
ON DUPLICATE KEY UPDATE create_time=
VALUES
( now());