mysql實(shí)現(xiàn)connect by start with方式
前言
1、mysql沒有層級查詢方法 而 oracle通過connect by start with語法可以實(shí)現(xiàn)層級查詢
2、mysql實(shí)現(xiàn)層級查詢的方式很多,有使用存儲過程函數(shù)嵌套調(diào)用亦有使用臨時(shí)表進(jìn)行層級查詢
3、本文使用一種變量循環(huán)賦值方式進(jìn)行,可以套用模版
實(shí)驗(yàn)
-- 創(chuàng)建測試表 -- DROP TABLE IF EXISTS `test_tree`; CREATE TABLE `test_tree` ( `id` varchar(10) DEFAULT NULL, `name` varchar(10) DEFAULT NULL, `pId` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 創(chuàng)建測試數(shù)據(jù)(根節(jié)點(diǎn)默認(rèn)-1) INSERT INTO `test_tree` VALUES ('1', '中國', '-1'); INSERT INTO `test_tree` VALUES ('2', '福建省', '1'); INSERT INTO `test_tree` VALUES ('3', '海南省', '1'); INSERT INTO `test_tree` VALUES ('4', '泉州市', '2'); INSERT INTO `test_tree` VALUES ('5', '福州市', '2'); INSERT INTO `test_tree` VALUES ('6', '泉港區(qū)', '4'); INSERT INTO `test_tree` VALUES ('7', '惠安縣', '4');
-- 模版 表名代替test_tree 用id替換以下id 用pid替換以下 用其他從屬字段替換name SELECT name, id, pid, @le:= IF (pid = -1 ,0,IF( LOCATE( CONCAT('|',pid,':'),@pathlevel) > 0,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',pid,':'),-1),'|',1) +1,@le+1) ) levels, @pathlevel:= CONCAT(@pathlevel,'|',id,':', @le ,'|') pathlevel, @pathnodes:= IF( pid =-1,',root', CONCAT_WS(',',IF( LOCATE( CONCAT('|',pid,':'),@pathall) > 0 ,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',pid,':'),-1),'|',1),@pathnodes ) ,pid ) )paths, @pathall:=CONCAT(@pathall,'|',id,':', @pathnodes ,'|') pathall FROM test_tree, ( SELECT @le:=0, @pathlevel:='', @pathall:='', @pathnodes:='' ) vv ORDER BY pid,id -- 結(jié)合instr(paths,'想要查所有子集的父級id')>0 驗(yàn)證 SELECT name, id, pid, levels, paths FROM ( SELECT name, id, pid, @le:= IF (pid = -1 ,0,IF( LOCATE( CONCAT('|',pid,':'),@pathlevel) > 0,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',pid,':'),-1),'|',1) +1,@le+1) ) levels, @pathlevel:= CONCAT(@pathlevel,'|',id,':', @le ,'|') pathlevel, @pathnodes:= IF( pid =-1,',root', CONCAT_WS(',',IF( LOCATE( CONCAT('|',pid,':'),@pathall) > 0 ,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',pid,':'),-1),'|',1),@pathnodes ) ,pid ) )paths, @pathall:=CONCAT(@pathall,'|',id,':', @pathnodes ,'|') pathall FROM test_tree, ( SELECT @le:=0, @pathlevel:='', @pathall:='', @pathnodes:='' ) vv ORDER BY pid,id ) src WHERE instr(paths,'-1')>0 ORDER BY pid
驗(yàn)證結(jié)果
1、數(shù)據(jù)
2 查詢中國
SELECT name, id, pid, levels, paths FROM ( SELECT name, id, pid, @le:= IF (pid = -1 ,0,IF( LOCATE( CONCAT('|',pid,':'),@pathlevel) > 0,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',pid,':'),-1),'|',1) +1,@le+1) ) levels, @pathlevel:= CONCAT(@pathlevel,'|',id,':', @le ,'|') pathlevel, @pathnodes:= IF( pid =-1,',root', CONCAT_WS(',',IF( LOCATE( CONCAT('|',pid,':'),@pathall) > 0 ,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',pid,':'),-1),'|',1),@pathnodes ) ,pid ) )paths, @pathall:=CONCAT(@pathall,'|',id,':', @pathnodes ,'|') pathall FROM test_tree, ( SELECT @le:=0, @pathlevel:='', @pathall:='', @pathnodes:='' ) vv ORDER BY pid,id ) src WHERE instr(paths,'1')>0 ORDER BY pid
3 查詢福建省
SELECT name, id, pid, levels, paths FROM ( SELECT name, id, pid, @le:= IF (pid = -1 ,0,IF( LOCATE( CONCAT('|',pid,':'),@pathlevel) > 0,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',pid,':'),-1),'|',1) +1,@le+1) ) levels, @pathlevel:= CONCAT(@pathlevel,'|',id,':', @le ,'|') pathlevel, @pathnodes:= IF( pid =-1,',root', CONCAT_WS(',',IF( LOCATE( CONCAT('|',pid,':'),@pathall) > 0 ,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',pid,':'),-1),'|',1),@pathnodes ) ,pid ) )paths, @pathall:=CONCAT(@pathall,'|',id,':', @pathnodes ,'|') pathall FROM test_tree, ( SELECT @le:=0, @pathlevel:='', @pathall:='', @pathnodes:='' ) vv ORDER BY pid,id ) src WHERE instr(paths,'2')>0 ORDER BY pid
總結(jié)
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
相關(guān)文章
一文搞清楚MySQL count(*)、count(1)、count(col)區(qū)別
本文主要介紹了MySQL count(*)、count(1)、count(col)區(qū)別,文中通過示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2022-03-03MySQL設(shè)置global變量和session變量的兩種方法詳解
這篇文章主要介紹了MySQL設(shè)置global變量和session變量的兩種方法,每種方法給大家介紹的非常詳細(xì) ,需要的朋友可以參考下2018-10-10詳解MySQL數(shù)據(jù)庫insert和update語句
用于操作數(shù)據(jù)庫的SQL一般分為兩種,一種是查詢語句,也就是我們所說的SELECT語句,另外一種就是更新語句,也叫做數(shù)據(jù)操作語句。接下來通過本文給大家介紹MySQL數(shù)據(jù)庫insert和update語句,需要的朋友一起學(xué)習(xí)吧2016-04-04mysql數(shù)據(jù)庫超過最大連接數(shù)的解決方法
當(dāng)mysql超過最大連接數(shù)時(shí),會報(bào)錯(cuò)”Too many connections”,本文主要介紹了mysql數(shù)據(jù)庫超過最大連接數(shù)的解決方法,具有一定的參考價(jià)值,感興趣的可以了解一下2023-12-12MySQL從命令行導(dǎo)入SQL腳本時(shí)出現(xiàn)中文亂碼的解決方法
這篇文章主要介紹了MySQL從命令行導(dǎo)入SQL腳本時(shí)出現(xiàn)中文亂碼的解決方法,分析了中文亂碼出現(xiàn)的原因并給出了兩種解決方法供大家參考,需要的朋友可以參考下2016-09-09mysql使用GROUP BY分組實(shí)現(xiàn)取前N條記錄的方法
這篇文章主要介紹了mysql使用GROUP BY分組實(shí)現(xiàn)取前N條記錄的方法,結(jié)合實(shí)例形式較為詳細(xì)的分析了mysql中GROUP BY分組的相關(guān)使用技巧,需要的朋友可以參考下2016-06-06基于MySQL數(shù)據(jù)庫復(fù)制Master-Slave架構(gòu)的分析
本篇文章是對MySQL數(shù)據(jù)庫復(fù)制Master-Slave架構(gòu)進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下2013-06-06Mysql BinLog存儲機(jī)制與數(shù)據(jù)恢復(fù)方式
這篇文章主要介紹了Mysql BinLog存儲機(jī)制與數(shù)據(jù)恢復(fù)方式,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-06-06