放弃递归! 一条sql查出用户表中指定用户所有上级数据
首先表结构如下:
CREATE TABLE `user_organization` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uuid` int(11) DEFAULT NULL COMMENT '用户id', `parent_uuid` int(11) DEFAULT '0' COMMENT '上级用户id', `level` int(1) DEFAULT '7' COMMENT '社区等级', PRIMARY KEY (`id`) USING BTREE, ) ENGINE=MyISAM AUTO_INCREMENT=257 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
当需要根据一个uuid查出他的所有上级用户数据时:
SELECT T2.uuid,T2.level,T2.parent_uuid FROM ( SELECT @r AS uid, ( SELECT @r := parent_uuid FROM user_organization WHERE uuid = uid ) AS parent_id FROM ( SELECT @r := 24 ) _, user_organization WHERE @r != 0 ) T1 JOIN user_organization T2 ON T1.uid = T2.uuid
核心是中间的子查询:
SELECT @r AS uid, ( SELECT @r := parent_uuid FROM user_organization WHERE uuid = uid ) AS parent_id FROM ( SELECT @r := 24 ) _, user_organization WHERE @r != 0;
这段子查询的作用是查出所有的parent_id, 然后外部根据这段子查询集来联表查询其他字段信息;
其中( SELECT @r := 24) 的作用就是申明变量r等于要查询上级的id,然后根据这个id去递归查询所有上级的id;
如果要改成查询下级就把
( SELECT @r := parent_uuid FROM user_organization WHERE uuid = uid )
改成
( SELECT @r := parent_uuid FROM user_organization WHERE parent_uuid = uid )