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());