mysql多层查询

最新推荐文章于 2025-01-05 17:07:07 发布

IMimjx

最新推荐文章于 2025-01-05 17:07:07 发布

阅读量358

点赞数

CC 4.0 BY-SA版权

文章标签： mysql sql 数据结构链表 hive

本文链接：https://blog.csdn.net/JKingyin/article/details/113765356

本文探讨了如何避免MySQL中GROUP_CONCAT函数截断问题，通过调整group_concat_max_len设置，并解析如何在JPA原生SQL中正确转义变量。讨论了父子ID查询的上下层操作，以及解决查询中特定字符串转义的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

附件：city.sql - djmg
mysql查询某节点下层的所有元素节点：

select parentId, areaId, areaCode,areaName, level, center
	  from city_area
	  where find_in_set(areaId,
		 	(select GROUP_CONCAT(childrenIds) from (
			 	select (
			     select @childrenIds:=GROUP_CONCAT(areaId) from city_area where FIND_IN_SET(parentId, @childrenIds)) as childrenIds
--			     ,CEILING( LENGTH(@childrenIds) / 5 )
		        from (select @childrenIds:=?) var,city_area c where  @childrenIds is not null
			) A )
		)
--	and areaName like '%区%';

mysql查询某节点上层的所有元素节点：

select parentId, areaId, areaCode,areaName, level, center
	  from city_area
	  where find_in_set(areaId,
		 	(select GROUP_CONCAT(childrenIds) from (
			 	select (
			     select @childrenIds:=GROUP_CONCAT(parentId) from city_area where FIND_IN_SET(areaId, @childrenIds)) as childrenIds
--			     ,CEILING( LENGTH(@childrenIds) / 5 )
		        from (select @childrenIds:=?) var,city_area c where  @childrenIds is not null
			) A )
		)
--	and areaName like '%区%';

两个查询的不同点在于最内层的查询将父子Id位置对换了一下(select @childrenIds:=GROUP_CONCAT(parentId) from city_area where FIND_IN_SET(areaId, @childrenIds)) as childrenIds(select @childrenIds:=GROUP_CONCAT(areaId) from city_area where FIND_IN_SET(parentId, @childrenIds)) as childrenIds

问题: