mysql层级关系查询 我使用这条语句查询出来的结果depth的值不是我预想中的

我代码中有一段类似的，也是网上找的，你可以参考下

SELECT CATE_CODE AS ID,PARENT_CODE AS 父ID ,levels AS 级数, paths AS 路径 FROM (

SELECT CATE_CODE,PARENT_CODE,

@le:= IF (PARENT_CODE = 0 ,0,

IF( LOCATE( CONCAT('|',PARENT_CODE,':'),@pathlevel)   >0  ,    

SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',PARENT_CODE,':'),-1),'|',1) +1

,@le+1) ) levels

, @pathlevel:= CONCAT(@pathlevel,'|',CATE_CODE,':', @le ,'|') pathlevel

, @pathnodes:= IF( PARENT_CODE =0,',0',

CONCAT_WS(',',

IF( LOCATE( CONCAT('|',PARENT_CODE,':'),@pathall) >0  ,

SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',PARENT_CODE,':'),-1),'|',1)

,@pathnodes ) ,PARENT_CODE  ) )paths

,@pathall:=CONCAT(@pathall,'|',CATE_CODE,':', @pathnodes ,'|') pathall

FROM  doc_category,

(SELECT @le:=0,@pathlevel:='', @pathall:='',@pathnodes:='') vv

ORDER BY  PARENT_CODE,CATE_CODE

) src

ORDER BY CATE_CODE

去提是否关联正确问题，细查之下，看到二个不同的as a，这是一条SQL语句中不允许的，所以把下面代码：

SELECT * FROM (SELECT a.intID, a.vhrCode AS 编码, a.vhrName AS 名称, a.vhrShortCode AS 简码,

a.intProItemType, b.vhrName AS 分类, a.intUnit, c.vhrName AS 单位, a.decCost AS 成本价,

a.decOutPrice AS 零售单价, a.intGainType, d.vhrName AS 提成方式, a.decGainValue AS 提成参数,

a.bitIsStop, case when a.bitIsStop then `停用` else `启用` end AS 使用状态, a.vhrRemark AS 说明,

a.intDataType AS 数据类型

FROM ((ProductItem AS a LEFT JOIN BaseData AS b ON a.intProItemType=b.intID) LEFT JOIN BaseData AS c ON a.intUnit=c.intID)

LEFT JOIN BaseData AS d ON a.intGainType=d.intID

WHERE (((a.intDataType)=0))ORDER BY a.vhrCode) AS a

改为下面代码：

SELECT * FROM (SELECT a.intID, a.vhrCode AS 编码, a.vhrName AS 名称, a.vhrShortCode AS 简码,

a.intProItemType, b.vhrName AS 分类, a.intUnit, c.vhrName AS 单位, a.decCost AS 成本价,

a.decOutPrice AS 零售单价, a.intGainType, d.vhrName AS 提成方式, a.decGainValue AS 提成参数,

a.bitIsStop, case when a.bitIsStop then `停用` else `启用` end AS 使用状态, a.vhrRemark AS 说明,

a.intDataType AS 数据类型

FROM ((ProductItem AS a LEFT JOIN BaseData AS b ON a.intProItemType=b.intID) LEFT JOIN BaseData AS c ON a.intUnit=c.intID)

LEFT JOIN BaseData AS d ON a.intGainType=d.intID

WHERE (((a.intDataType)=0))ORDER BY a.vhrCode)

---