我们的业务场景是根据父栏目id查询所有子栏目信息,这里提供了几种根据父id查询子id集合的一些方法,cms_channel表名,channel_id主键id,#channelId:VARCHAR#参数数据类型,总结下:
oracle数据库IBatis写法
select channel_id from cms_channel start with parent_id =#channelId:VARCHAR#
connect by prior channel_id=parent_id
Mysql数据库IBatis写法
SELECT c.channel_id FROM
(SELECT @id idlist, @lv := @lv + 1 lv,
(SELECT @id := GROUP_CONCAT(channel_id SEPARATOR ',') FROM cms_channel WHERE FIND_IN_SET(parent_id, @id)) sub
FROM cms_channel, (SELECT @id := #channelId:VARCHAR#, @lv := 0) vars WHERE @id IS NOT NULL
) t, cms_channel c
WHERE FIND_IN_SET(c.parent_id, t.idlist)
递归查询函数写法
函数定义:
DROP function IF EXISTS getSubChannelByPId;
create function getSubChannelByPId(pid VARCHAR(32))
returns varchar(4000)
BEGIN
DECLARE oTemp VARCHAR(4000);
DECLARE oTempChild VARCHAR(4000);
SET oTemp = '';
SET oTempChild = CAST(pid AS CHAR);
WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChild);
SELECT GROUP_CONCAT(channel_id) INTO oTempChild FROM cms_channel WHERE FIND_IN_SET(parent_id,oTempChild) > 0;
END WHILE;
RETURN oTemp;
END
IBatis写法
select CHANNEL_ID from cms_channel where FIND_IN_SET(c.parent_id,getSubChannelByPId(#channelId:VARCHAR#))