oracle=》msql sql语句转换 自记录

1.

oracle：

to_char(T_TIME,'yyyy-MM-dd hh24:mi:ss') T_TIME

msql：

date_format(T_TIME,'%Y-%m-%d %H:%i:%S' )T_TIME

2.

oracle：

DECODE(T.T_M_S_SEX,'0','全部人群','1','男性','2','女性','全部') V_M_S_SEX

msql：

CASE WHEN T.T_M_S_SEX='0' THEN '全部人群' WHEN T.T_M_S_SEX='1' THEN '男性'  WHEN T.T_M_S_SEX='2' THEN '女性' 
ELSE '全部' 
end as V_M_S_SEX

3.

oracle：

select a.d_l_id,a.d_l_name,a.parent_id,level from T_LIST a start with d_l_id=? connect by prior d_l_id=parent_id

msql：

SELECT   DATA.d_l_id,DATA.d_l_name,DATA.d_l_parent_id FROM( 
    SELECT
        @ids as _ids, 
        (   SELECT @ids := GROUP_CONCAT(d_l_id) 
            FROM T_DICT_LIST 
            WHERE FIND_IN_SET(parent_id, @ids) 
        ) as cids, 
        @l := @l+1 as level
    FROM T_DICT_LIST, 
        (SELECT @ids :='210', @l := 0 ) b 
    WHERE @ids IS NOT NULL
) id, T_DICT_LIST DATA 
WHERE FIND_IN_SET(DATA.d_l_id, ID._ids) 
ORDER BY level, d_l_id

eg：

CREATE TABLE table1(id int, name varchar(10), parent_id int); 
INSERT table1 VALUES
(1, ‘Home’,        0), 
(2, ‘About’,       1), 
(3, ‘Contact’,     1), 
(4, ‘Legal’,         2), 
(5, ‘Privacy’,      4), 
(6, ‘Products’,   1), 
(7, ‘Support’,     2);

所有父级

SELECT ID.level, DATA.* FROM( 
    SELECT 
        @id as _id,  
        (   SELECT @id := parent_id 
            FROM table1  
            WHERE id = @id  
        ) as _pid,  
        @l := @l+1 as level 
    FROM table1, 
        (SELECT @id := 5, @l := 0 ) b  
    WHERE @id > 0 
) ID, table1 DATA 
WHERE ID._id = DATA.id 
ORDER BY level;

所有子级

SELECT ID.level, DATA.* FROM( 
    SELECT 
        @ids as _ids,  
        (   SELECT @ids := GROUP_CONCAT(id)  
            FROM table1  
            WHERE FIND_IN_SET(parent_id, @ids)  
        ) as cids,  
        @l := @l+1 as level 
    FROM table1,  
        (SELECT @ids :=’1’, @l := 0 ) b  
    WHERE @ids IS NOT NULL
) id, table1 DATA 
WHERE FIND_IN_SET(DATA.id, ID._ids) 
ORDER BY level, id

4.

oracle：

substr( R_U_MOBILE, 0, 3 )

mysql:

substr( R_U_MOBILE, 1, 3 )  从1开始

5.

oracle：

||

mysql:

concat ( str1, '****', str2 )  MOBILE

6.

oracle：

to_number ( sysdate() - t.T_ASK_TIME )   时间差  结果是 天

mysql:

    TIMESTAMPDIFF(SECOND,t.T_ASK_TIME，sysdate() )  T_ASK_TIME

返回类型可选： 

• MICROSECOND
• SECOND 秒
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR