[ppas query]<\/p>\n
WITH RECURSIVE help_page(help_id, help_name, help_page, help_tag, help_main_key, help_sub_key, help_location, parent_id, show_type, depth_level, menu_seq) AS (\r\nSELECT\r\nhm.help_id, hm.help_name, hm.help_page, hm.help_tag, hm.help_main_key, hm.help_sub_key, concat(hm.help_name) as help_location, hm.parent_id, hm.show_type, 0 as depth_level, lpad(concat(hm.menu_seq),3,'0') as menu_seq\r\nFROM tb_m00s22_help_meta hm\r\nWHERE hm.parent_id is null or hm.parent_id = '' \r\nUNION ALL\r\nSELECT\r\nhm.help_id, hm.help_name, hm.help_page, hm.help_tag, hm.help_main_key, hm.help_sub_key, hp.help_location || ' > ' || concat(hm.help_name) as help_location, hm.parent_id, hm.show_type, depth_level + 1 as depth_level, hp.menu_seq || '-' || lpad(concat(hm.menu_seq),3,'0') as menu_seq\r\nFROM tb_m00s22_help_meta hm\r\nINNER JOIN help_page AS hp ON hm.parent_id = hp.help_id\r\n)\r\nSELECT *\r\nFROM help_page\r\nORDER BY menu_seq<\/pre>\n<\/p>\n
[mysql query]<\/p>\n
select original_value as help_id, help_location, depth_level, menu_seq\r\nfrom (\r\n\tselect @ov as original_value, help_id, help_location, depth_level, menu_seq\r\n\tfrom (\r\n\t\tselect t.help_id as help_id, @pv:=t.parent_id as parent_id, t.help_name\r\n\t\t\t , @pv2:=(case when @pv2 = '' then concat(t.help_name,@pv2) else concat(t.help_name,' > ',@pv2) end) as help_location\r\n\t\t\t , (@rownum := @rownum+1) as depth_level\r\n\t\t\t , @menu_seq:=(case when @menu_seq = '' then concat(t.menu_seq,@menu_seq) else concat(t.menu_seq,'-',@menu_seq) end) as menu_seq\r\n\t\tfrom (select help_id, parent_id, help_name, lpad(concat(menu_seq),3,'0') as menu_seq from tb_m00s22_help_meta order by help_id desc LIMIT 100000) t\r\n\t\t\t join (select @ov:='H300020006', @pv:='H300020006', @pv2:='', @rownum:=-1, @menu_seq:='' ) tmp\r\n\t\twhere t.help_id = @pv\r\n\t) tt\r\n\torder by help_id asc\r\n\tlimit 1\r\n) ttt<\/pre>\n** mysql\uc740 subquery\uc5d0\uc11c orderby\ub97c \ud588\ub294\ub370, \uc678\ubd80\ub85c \ub098\uc624\uba74 \ub2e4\uc2dc orderby\uac00 \uc6d0\ubcf5\ub41c\ub2e4.
\nlimit\ub97c \uc0ac\uc6a9\ud558\uba74 \uc815\ub82c\uc774 \uc720\uc9c0\ub41c\ub2e4.
\nhttps:\/\/mariadb.com\/kb\/en\/library\/why-is-order-by-in-a-from-subquery-ignored\/<\/a><\/p>\n[select query]<\/p>\n
select\r\n\thelp_id, help_name, help_page, help_tag, help_main_key, help_sub_key, FN_SPLIT(help_data,'|',1) as help_location, parent_id, show_type, FN_SPLIT(help_data,'|',2) as depth_level, FN_SPLIT(help_data,'|',3) as menu_seq\r\nfrom (\r\nselect help_id, help_name, help_page, help_tag, help_main_key, help_sub_key, parent_id, show_type,FN_GET_HELP_DATA(help_id) as help_data from tb_m00s22_help_meta\r\n) t<\/pre>\n[mysql function]<\/p>\n
DELIMITER $$\r\nCREATE DEFINER=`poswork`@`%` FUNCTION `FN_GET_HELP_DATA`(pv_help_id varchar(50)) RETURNS varchar(1000) CHARSET utf8\r\n DETERMINISTIC\r\nBEGIN\r\n DECLA RE v_pv_help_id varchar(50);\r\n\tDECLA RE v_ori_help_id varchar(500);\r\n DECLA RE v_help_location varchar(500);\r\n DECLA RE v_depth_level varchar(500);\r\n DECLA RE v_menu_seq varchar(500);\r\n DECLA RE v_rtn_value varchar(1000);\r\n\tDECLA RE EXIT HANDLER FOR NOT FOUND RETURN null;\r\n \r\n SET v_pv_help_id = pv_help_id;\r\n\t\t\r\n IF v_pv_help_id IS NULL OR v_pv_help_id = '' THEN\r\n RETURN null;\r\n END IF;\r\n\r\n\t\tselect original_value as help_id, help_location, depth_level, menu_seq\r\n INTO v_ori_help_id,v_help_location,v_depth_level,v_menu_seq\r\n\t\tfrom (\r\n\t\t\tselect @ov as original_value, help_id, help_location, depth_level, menu_seq\r\n\t\t\tfrom (\r\n\t\t\t\tselect t.help_id as help_id, @pv:=t.parent_id as parent_id, t.help_name\r\n\t\t\t\t\t , @pv2:=(case when @pv2 = '' then concat(t.help_name,@pv2) else concat(t.help_name,' > ',@pv2) end) as help_location\r\n\t\t\t\t\t , (@rownum := @rownum+1) as depth_level\r\n\t\t\t\t\t , @menu_seq:=(case when @menu_seq = '' then concat(t.menu_seq,@menu_seq) else concat(t.menu_seq,'-',@menu_seq) end) as menu_seq\r\n\t\t\t\tfrom (select help_id, parent_id, help_name, lpad(concat(menu_seq),3,'0') as menu_seq from tb_m00s22_help_meta order by help_id desc LIMIT 100000) t\r\n\t\t\t\t\t join (select @ov:=v_pv_help_id, @pv:=v_pv_help_id, @pv2:='', @rownum:=-1, @menu_seq:='') tmp\r\n\t\t\t\twhere t.help_id = @pv\r\n\t\t\t) tt\r\n\t\t\torder by help_id asc\r\n\t\t\tlimit 1\r\n\t\t) ttt;\r\n\t\t\r\n\t\tSET v_rtn_value = concat(v_help_location,'|',v_depth_level,'|',v_menu_seq);\r\n\r\n\tRETURN v_rtn_value;\r\n \r\nEND$$\r\nDELIMITER ;\r\n<\/pre>\n<\/p>\n
[split function]<\/p>\n
DELIMITER $$\r\nCREATE DEFINER=`poswork`@`%` FUNCTION `FN_SPLIT`(srcvalue varchar(500), split_char varchar(50), split_index int) RETURNS varchar(500) CHARSET utf8\r\n DETERMINISTIC\r\nreturn if(srcvalue is null or srcvalue = '',null,substring_index(substring_index(srcvalue,split_char,split_index),split_char,-1))$$\r\nDELIMITER ;\r\n<\/pre>\n<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
[ppas query] WITH RECURSIVE help_page(help_id, help_name, help_page, help_tag, help_main_key, help_sub_key, help_location, parent_id, show_type, depth_level, menu_seq) AS ( SELECT hm.help_id, hm.help_name, hm.help_page, hm.help_tag, hm.help_main_key, hm.help_sub_key, concat(hm.help_name) as help_location, hm.parent_id, hm.show_type, 0 as depth_level, lpad(concat(hm.menu_seq),3,’0′) as menu_seq FROM tb_m00s22_help_meta hm WHERE hm.parent_id is null or hm.parent_id = ” UNION ALL SELECT hm.help_id, hm.help_name, hm.help_page, hm.help_tag, hm.help_main_key, hm.help_sub_key, … Continue reading mysql recusive query<\/span>