help_topic_id 和 substring_index

1.help_topic_id

SELECT a.id, substring_index( 
substring_index( a.ancestors, ',', b.help_topic_id + 1 ), ',',- 1 ) AS ancestors 
FROM fc_area a 
JOIN mysql.help_topic

                    

这个是最初的数据                                                            这个是查询的数据 

SELECT c.id, count( c.ancestors ) AS ancestorsCount
FROM ( SELECT a.id, substring_index( 
substring_index( a.ancestors, ',', b.help_topic_id + 1 ), ',',- 1 ) AS ancestors 
FROM fc_area a 
JOIN mysql.help_topic b 
ON b.help_topic_id < ( length( a.ancestors ) - length( REPLACE ( a.ancestors, ',', '' )) + 1 ) ) c 
GROUP BY c.id

 +1 从2 开始计数

SELECT a.id FROM
( SELECT c.id, count( c.ancestors ) AS ancestorsCount
FROM ( SELECT a.id, substring_index( 
substring_index( a.ancestors, ',', b.help_topic_id + 1 ), ',',- 1 ) AS ancestors 
FROM fc_area a 
JOIN mysql.help_topic b 
ON b.help_topic_id < ( length( a.ancestors ) - length( REPLACE ( a.ancestors, ',', '' )) +1 ) ) c 
GROUP BY c.id ) a where a.ancestorsCount <= '3'

 个人数据 4往后就是每层具体位置