mysql 父类子类查询
来源:互联网 发布:网络热播电视剧排行榜 编辑:程序博客网 时间:2024/05/22 05:26
查出子
DELIMITER $$
DROP FUNCTION IF EXISTS `getDeptChildList`$$
CREATE FUNCTION `getDeptChildList`(rootId INT) RETURNS varchar(1000) CHARSET utf8
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE pTemp VARCHAR(1000);
DECLARE cTemp VARCHAR(1000);
SET pTemp = '$';
SET cTemp =cast(rootId as CHAR);
WHILE cTemp is not null DO
SET pTemp = concat(pTemp,',',cTemp);
SELECT group_concat(id) INTO cTemp FROM roll_work_comment
WHERE FIND_IN_SET(mes_id,cTemp)>0;
END WHILE;
RETURN pTemp;
END;
$$
DELIMITER ;
SELECT * FROM roll_work_comment WHERE FIND_IN_SET(id,getDeptChildList(1)) ORDER BY id and mes_id;
查出父
SELECT T2.*
FROM (
SELECT
@r AS _id,
(SELECT @r := mes_id FROM roll_work_comment WHERE id = _id) AS mes_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 3, @l := 0) vars,
roll_work_comment h
WHERE @r <> 0) T1
JOIN roll_work_comment T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC
DELIMITER $$
DROP FUNCTION IF EXISTS `getDeptChildList`$$
CREATE FUNCTION `getDeptChildList`(rootId INT) RETURNS varchar(1000) CHARSET utf8
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE pTemp VARCHAR(1000);
DECLARE cTemp VARCHAR(1000);
SET pTemp = '$';
SET cTemp =cast(rootId as CHAR);
WHILE cTemp is not null DO
SET pTemp = concat(pTemp,',',cTemp);
SELECT group_concat(id) INTO cTemp FROM roll_work_comment
WHERE FIND_IN_SET(mes_id,cTemp)>0;
END WHILE;
RETURN pTemp;
END;
$$
DELIMITER ;
SELECT * FROM roll_work_comment WHERE FIND_IN_SET(id,getDeptChildList(1)) ORDER BY id and mes_id;
查出父
SELECT T2.*
FROM (
SELECT
@r AS _id,
(SELECT @r := mes_id FROM roll_work_comment WHERE id = _id) AS mes_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 3, @l := 0) vars,
roll_work_comment h
WHERE @r <> 0) T1
JOIN roll_work_comment T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC
0 0
- mysql 父类子类查询
- mysql 父类查询所有子类以及从子类查询父类
- mysql从子类id查询所有父类
- mysql 根据子类id查询所有父类id
- mysql递归查询,mysql中从子类ID查询所有父类(做无限分类经常用到)
- mysql中从子类ID查询所有父类(做无限分类经常用到)
- mysql 更具父类id 查询所有子类id
- 【jsp案例】通过select查询父类子类数据表
- mybatis+mysql查询类别下的所有子类别(递归)
- C++ 子类转父类 父类确定子类
- 子类与父类,
- 父类子类接口
- 子类继承父类
- 子类继承父类
- LIST父类子类
- 父类与子类
- 子类复写父类
- 父类与子类
- SystemUI下拉通知栏的源码分析
- top 内存字段解析
- 使用springmvc+urlRewrite实现404,500以及异常的处理
- CSS写箭头
- RedHat Enterprise 7.2(RHEL7)安装Oracle 12.X报compat-libstdc++包的解决办法
- mysql 父类子类查询
- 微软2016校园招聘:#1239 : Fibonacci
- 小记——linux目录
- Android布局中实现圆角边框
- ListView 或是 GridView中。Canvas: trying to use a recycled bitmap 异常
- 【转】 java 运算符优先级
- 《统计学习方法》学习笔记--k近邻法及常用的距离(or 相似度)度量
- 纯代码封装自定义View和XIB封装自定义View的区别
- float类型在内存中的存储