有关数据库SQL递归查询在不同数据库中的实现方法
本文给大家介绍有关数据库SQL递归查询在不同数据库中的实现方法,具体内容请看下文。
比如表结构数据如下:
Table:Tree
IDNameParentId
1一级 0
2 二级1
3 三级2
4四级3
SQLSERVER2005查询方法:
//上查 withtmpTree as ( select*fromTreewhereId=2 unionall selectp.*fromtmpTreeinnerjoinTreeponp.Id=tmpTree.ParentId ) select*fromtmpTree //下查 withtmpTree as ( select*fromTreewhereId=2 unionall selects.*fromtmpTreeinnerjoinTreesons.ParentId=tmpTree.Id ) select*fromtmpTree
SQLSERVER2008及以后版本,还可用如下方法:
增加一列TID,类型设为:hierarchyid(这个是CLR类型,表示层级),且取消ParentId字段,变成如下:(表名为:Tree2)
TIdIdName
0x1一级
0x58 2 二级
0x5B40 3 三级
0x5B5E 4 四级
查询方法:
SELECT*,TId.GetLevel()as[level]FROMTree2--获取所有层级 DECLARE@ParentTreehierarchyid SELECT@ParentTree=TIdFROMTree2WHEREId=2 SELECT*,TId.GetLevel()AS[level]FROMTree2WHERETId.IsDescendantOf(@ParentTree)=1--获取指定的节点所有下级 DECLARE@ChildTreehierarchyid SELECT@ChildTree=TIdFROMTree2WHEREId=3 SELECT*,TId.GetLevel()AS[level]FROMTree2WHERE@ChildTree.IsDescendantOf(TId)=1--获取指定的节点所有上级
ORACLE中的查询方法:
SELECT* FROMTree STARTWITHId=2 CONNECTBYPRIORID=ParentId--下查 SELECT* FROMTree STARTWITHId=2 CONNECTBYID=PRIORParentId--上查
MYSQL中的查询方法:
//定义一个依据ID查询所有父ID为这个指定的ID的字符串列表,以逗号分隔 CREATEDEFINER=`root`@`localhost`FUNCTION`getChildLst`(rootIdint,directionint)RETURNSvarchar(1000)CHARSETutf8 BEGIN DECLAREsTempVARCHAR(5000); DECLAREsTempChdVARCHAR(1000); SETsTemp='$'; IFdirection=1THEN SETsTempChd=cast(rootIdasCHAR); ELSEIFdirection=2THEN SELECTcast(ParentIdasCHAR)intosTempChdFROMTreeWHEREId=rootId; ENDIF; WHILEsTempChdisnotnullDO SETsTemp=concat(sTemp,',',sTempChd); SELECTgroup_concat(id)INTOsTempChdFROMTreewhere(direction=1andFIND_IN_SET(ParentId,sTempChd)>0) or(direction=2andFIND_IN_SET(Id,sTempChd)>0); ENDWHILE; RETURNsTemp; END //查询方法: select*fromtreewherefind_in_set(id,getChildLst(1,1));--下查 select*fromtreewherefind_in_set(id,getChildLst(1,2));--上查
补充说明:上面这个方法在下查是没有问题,但在上查时会出现问题,原因在于我的逻辑写错了,存在死循环,现已修正,新的方法如下:
CREATEDEFINER=`root`@`localhost`FUNCTION`getChildLst`(rootIdint,directionint)RETURNSvarchar(1000)CHARSETutf8 BEGIN DECLAREsTempVARCHAR(5000); DECLAREsTempChdVARCHAR(1000); SETsTemp='$'; SETsTempChd=cast(rootIdasCHAR); IFdirection=1THEN WHILEsTempChdisnotnullDO SETsTemp=concat(sTemp,',',sTempChd); SELECTgroup_concat(id)INTOsTempChdFROMTreewhereFIND_IN_SET(ParentId,sTempChd)>0; ENDWHILE; ELSEIFdirection=2THEN WHILEsTempChdisnotnullDO SETsTemp=concat(sTemp,',',sTempChd); SELECTgroup_concat(ParentId)INTOsTempChdFROMTreewhereFIND_IN_SET(Id,sTempChd)>0; ENDWHILE; ENDIF; RETURNsTemp; END
这样递归查询就很方便了。