原语句:
SELECT unnest(string_to_array('character_set_client,character_set_connection,character_set_results,character_set_server,init_connect,interactive_timeout,lower_case_table_names,max_allowed_packet,net_buffer_length,net_write_timeout,query_cache_size,query_cache_type,sql_mode,system_time_zone,system_time_zone,time_zonetransaction_isolation,tx_isolation,wait_timeout',',')) as Variable_name, unnest(string_to_array('utf8,utf8,utf8,utf8mb4,,28800,0,4194304,16384,60,1048576,OFF,,CST,SYSTEM,REPEATABLE-READ,REPEATABLE-READ,28800',',')) as VALUE
核心实现:
SELECT rownum id,
regexp_substr('para1,para2,para3', '[^,]+', 1, LEVEL) para_name
FROM dual
CONNECT BY LEVEL <= LENGTH('para1,para2,para3')-LENGTH(REPLACE('para1,para2,para3', ','))+ 1
最终改写结果:
with temp_para AS
( SELECT rownum id,
regexp_substr('character_set_client,character_set_connection,character_set_results,character_set_server,init_connect,interactive_timeout,lower_case_table_names,max_allowed_packet,net_buffer_length,net_write_timeout,query_cache_size,query_cache_type,sql_mode,system_time_zone,system_time_zone,time_zonetransaction_isolation,tx_isolation,wait_timeout', '[^,]+', 1, LEVEL) para_name
FROM dual
CONNECT BY LEVEL <= LENGTH('character_set_client,character_set_connection,character_set_results,character_set_server,init_connect,interactive_timeout,lower_case_table_names,max_allowed_packet,net_buffer_length,net_write_timeout,query_cache_size,query_cache_type,sql_mode,system_time_zone,system_time_zone,time_zonetransaction_isolation,tx_isolation,wait_timeout')-LENGTH(REPLACE('character_set_client,character_set_connection,character_set_results,character_set_server,init_connect,interactive_timeout,lower_case_table_names,max_allowed_packet,net_buffer_length,net_write_timeout,query_cache_size,query_cache_type,sql_mode,system_time_zone,system_time_zone,time_zonetransaction_isolation,tx_isolation,wait_timeout', ','))+ 1
)
,
temp_value as
(SELECT rownum id,
regexp_substr('utf8,utf8,utf8,utf8mb4,,28800,0,4194304,16384,60,1048576,OFF,,CST,SYSTEM,REPEATABLE-READ,REPEATABLE-READ,28800', '[^,]+', 1, LEVEL) para_value
FROM dual
CONNECT BY LEVEL <= LENGTH('utf8,utf8,utf8,utf8mb4,,28800,0,4194304,16384,60,1048576,OFF,,CST,SYSTEM,REPEATABLE-READ,REPEATABLE-READ,28800')-LENGTH(REPLACE('utf8,utf8,utf8,utf8mb4,,28800,0,4194304,16384,60,1048576,OFF,,CST,SYSTEM,REPEATABLE-READ,REPEATABLE-READ,28800', ','))+ 1
)
select para_name,
para_value
from temp_para,
temp_value
where temp_para.id=temp_value.id
order by temp_para.id;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~分享完毕啦~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
更多内容,请访问达梦社区地址:https:eco.dameng.com