大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说Oracle迁移MySQL 8特殊SQL处理「终于解决」,希望您对编程的造诣更进一步.
create table nayi_180328_connect_test(
dept_id varchar2(50),
parent_id varchar2(50),
dept_name varchar2(100),
dept_rank varchar2(2000),
val number);代码100分
插入语句
代码100分insert into nayi_180328_connect_test
select "root", "", "全国", "", 0 from dual
union all
select "root_1", "root", "北京市", "", 2000 from dual
union all
select "ln_root", "root", "辽宁省", "", 200 from dual
union all
select "ln_ys", "ln_root", "辽宁省沈阳市", "", 1000 from dual
union all
select "ln_sy_hp", "ln_ys", "辽宁省沈阳和平区", "", 500 from dual
union all
select "ln_ys_dd", "ln_ys", "辽宁省沈阳大东区", "", 600 from dual
union all
select "jl_root", "root", "吉林省", "", 0 from dual
union all
select "jl_jl", "jl_root", "吉林省吉林市", "", 200 from dual
union all
select "jl_cc", "jl_root", "吉林省长春市", "", 500 from dual
;
Oracle的递归查询语句如下
select t1.*
from nayi_180328_connect_test t1
where 1=1
connect by prior t1.dept_id = t1.parent_id
start with t1.dept_id = "root"
;结果如下
迁移MySQL 8,建表如下
代码100分create table nayi_180328_connect_test(
dept_id varchar(50),
parent_id varchar(50),
dept_name varchar(100),
dept_rank varchar(2000),
val int,
PRIMARY key (dept_id)
)
插入语句与Oracle相同
insert into nayi_180328_connect_test
select "root", "", "全国", "", 0 from dual
union all
select "root_1", "root", "北京市", "", 2000 from dual
union all
select "ln_root", "root", "辽宁省", "", 200 from dual
union all
select "ln_ys", "ln_root", "辽宁省沈阳市", "", 1000 from dual
union all
select "ln_sy_hp", "ln_ys", "辽宁省沈阳和平区", "", 500 from dual
union all
select "ln_ys_dd", "ln_ys", "辽宁省沈阳大东区", "", 600 from dual
union all
select "jl_root", "root", "吉林省", "", 0 from dual
union all
select "jl_jl", "jl_root", "吉林省吉林市", "", 200 from dual
union all
select "jl_cc", "jl_root", "吉林省长春市", "", 500 from dualMySQL 8递归查询语句如下
with recursive t1(dept_id,parent_id,dept_name,dept_rank,val,rownum, order_str) as (
select t0.*,@rownum := 1 rn, cast(@rownum as char) sdfsf from nayi_180328_connect_test t0 where t0.dept_id="root"
union all
select t2.*,@rownum := @rownum + 1 rn, concat(t1.order_str, "-", @rownum) st from nayi_180328_connect_test t2,t1
where t2.parent_id = t1.dept_id
)
select * from t1 order by order_str结果如下
- 日期转换字符串
Oracle中
select to_char(sysdate,"yyyy-mm-dd") from dual结果
2020-05-07
MySQL 8中
select date_format(now(),"%Y-%m-%d") from dual结果
2020-05-07
- 字符串转换日期
Oracle中
select to_date("2020-01-01","yyyy-mm-dd") from dual结果
2020-01-01 00:00:00
MySQL 8中
select str_to_date("2020-01-01","%Y-%m-%d") from dual结果
2020-01-01
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/8585.html
