mysql實(shí)現(xiàn)connect by start with方式
前言
1、mysql沒(méi)有層級(jí)查詢方法 而 oracle通過(guò)connect by start with語(yǔ)法可以實(shí)現(xiàn)層級(jí)查詢
2、mysql實(shí)現(xiàn)層級(jí)查詢的方式很多,有使用存儲(chǔ)過(guò)程函數(shù)嵌套調(diào)用亦有使用臨時(shí)表進(jìn)行層級(jí)查詢
3、本文使用一種變量循環(huán)賦值方式進(jìn)行,可以套用模版
實(shí)驗(yàn)
-- 創(chuàng)建測(cè)試表 -- 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)建測(cè)試數(shù)據(jù)(根節(jié)點(diǎn)默認(rèn)-1) INSERT INTO `test_tree` VALUES ('1', '中國(guó)', '-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,'想要查所有子集的父級(jí)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 查詢中國(guó)
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ū)別,文中通過(guò)示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2022-03-03MySQL設(shè)置global變量和session變量的兩種方法詳解
這篇文章主要介紹了MySQL設(shè)置global變量和session變量的兩種方法,每種方法給大家介紹的非常詳細(xì) ,需要的朋友可以參考下2018-10-10詳解MySQL數(shù)據(jù)庫(kù)insert和update語(yǔ)句
用于操作數(shù)據(jù)庫(kù)的SQL一般分為兩種,一種是查詢語(yǔ)句,也就是我們所說(shuō)的SELECT語(yǔ)句,另外一種就是更新語(yǔ)句,也叫做數(shù)據(jù)操作語(yǔ)句。接下來(lái)通過(guò)本文給大家介紹MySQL數(shù)據(jù)庫(kù)insert和update語(yǔ)句,需要的朋友一起學(xué)習(xí)吧2016-04-04mysql數(shù)據(jù)庫(kù)超過(guò)最大連接數(shù)的解決方法
當(dāng)mysql超過(guò)最大連接數(shù)時(shí),會(huì)報(bào)錯(cuò)”Too many connections”,本文主要介紹了mysql數(shù)據(jù)庫(kù)超過(guò)最大連接數(shù)的解決方法,具有一定的參考價(jià)值,感興趣的可以了解一下2023-12-12詳解數(shù)據(jù)庫(kù)語(yǔ)言中的null值
這篇文章主要詳解了數(shù)據(jù)庫(kù)語(yǔ)言中的null值,針對(duì)MySQL上的實(shí)例進(jìn)行講解,需要的朋友可以參考下2015-04-04MySQL從命令行導(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ù)庫(kù)復(fù)制Master-Slave架構(gòu)的分析
本篇文章是對(duì)MySQL數(shù)據(jù)庫(kù)復(fù)制Master-Slave架構(gòu)進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下2013-06-06Mysql BinLog存儲(chǔ)機(jī)制與數(shù)據(jù)恢復(fù)方式
這篇文章主要介紹了Mysql BinLog存儲(chǔ)機(jī)制與數(shù)據(jù)恢復(fù)方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-06-06