你好,
我的数据库表结构如下。
TableName:- UserTable
id | empName | reportsTo
-----------------------
1 | XYZ | -
2 | ABC | 1
3 | MNP | 2
4 | IJK | 3
5 | PQR | 4
6 | DEF | 3
7 | STU | 2
如何获取他在最高层次中报告的用户的详细信息。
例如:当我选择id为7
的用户时,输出应该是
id empName reportsTo
2 ABC 1
1 XYZ -
同样,当我选择id为6
的用户时,它应该返回id的3,2,1
的数据。
发布于 2018-07-22 23:45:06
我认为你不理解我发布的可能的重复链接,所以这里是发布的解决方案,并进行了一些微不足道的调整以适合您的数据。
drop table if exists usertable;
create table usertable(id int, empName varchar(3), reportsTo int);
insert into usertable values
(1 , 'XYZ' , null),
(2 , 'ABC' , 1),
(3 , 'MNP' , 2),
(4 , 'IJK' , 3),
(5 , 'PQR' , 4),
(6 , 'DEF' , 3),
(7 , 'STU' , 2);
SELECT T2.id, T2.empname,t2.reportsto
FROM (
SELECT
@r AS _id,
(SELECT @r := reportsto FROM usertable WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 7, @l := 0) vars,
usertable h
WHERE @r <> 0) T1
JOIN usertable T2
ON T1._id = T2.id
where t2.id <> 7
ORDER BY T1.lvl;
+------+---------+-----------+
| id | empname | reportsto |
+------+---------+-----------+
| 2 | ABC | 1 |
| 1 | XYZ | NULL |
+------+---------+-----------+
2 rows in set (0.03 sec)
和id 6
SELECT T2.id, T2.empname,t2.reportsto
FROM (
SELECT
@r AS _id,
(SELECT @r := reportsto FROM usertable WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 6, @l := 0) vars,
usertable h
WHERE @r <> 0) T1
JOIN usertable T2
ON T1._id = T2.id
where t2.id <> 6
ORDER BY T1.lvl;
+------+---------+-----------+
| id | empname | reportsto |
+------+---------+-----------+
| 3 | MNP | 2 |
| 2 | ABC | 1 |
| 1 | XYZ | NULL |
+------+---------+-----------+
3 rows in set (0.00 sec)
注意,我已经将id为1的reportsto从-改为null。只有当report to小于id时才有效,否则您将不得不认真考虑您的数据结构。
https://stackoverflow.com/questions/51466384
复制相似问题