MySQL递归子查询 [英] MySQL recursive subquery

问题描述

不确定标题是否准确反映了我需要做的事情，这个问题可能超出了 Stack Overflow 的范围，因为除了我的问题之外，我没有太多工作要做.我将解释我的场景......

Not sure the title accurately reflects what I need to do and this question is possibly out of the scope of Stack Overflow as I do not have much to work with, other than my problem. I will explain my scenario ...

我有一个报告网络应用程序.用户分为高级经理、经理和团队成员三个级别.每个经理负责一个团队.每个高级经理负责一部分经理.

I have a reporting web application. There are three levels of users Senior Managers, Managers and Team Members. Each Manager is responsible for a team. Each Senior Manager is responsible for a subset of Managers.

高级经理和经理写一份关于他们下属的报告.目前系统只允许经理查看其直属下属的报告.然而，该系统将被调整为允许高级经理查看关于他们的经理的报告以及查看经理编写的关于他们的团队成员的任何报告.

Senior Managers and Managers write a report on their subordinates. Currently the system only allows Manager to view reports on their immediate subordinates. However the system is to be adapted to allow Senior Managers to view reports on their Managers as well as view any reports the Managers have written about their team members.

我有一个表 team_members 表来管理用户所在的团队.所以......

I have table team_members table which governs which team a user is in. So ...

Users
--------
uid
forename
surname

team_members
------------
id
leader_id
member_id

leader_id 和 member_id 与 users 表的主键相关；用户名.

leader_id and member_id relate to the primary key of the users table; uid.

因此，作为高级经理，我需要能够生成所有直接团队成员(经理)的列表，以及他们的下属列表.有没有办法在一个查询中做到这一点?

So as a Senior Manager I need to be able to generate a list of all immediate team members (Managers), plus a list of their subordinates. Is there a way to do this within one query?

推荐答案

我不确定 Herachial 数据是否是您需要/想要的，但这是您需要的

i'm not sure if herachial data is what you need/want, but here's what you need

所有上、中、普通会员，DEMO，SQL:

all tops, middle, an regular members, DEMO, SQL :

select 
IFNULL((SELECT name FROM users where uid=smng.leader_id),(SELECT name FROM users where uid=mng.leader_id)) 
         as top, 
(SELECT name FROM users where uid = mng.leader_id) 
         as middle,
usr.name as regular
from 
 users usr inner join managers mng on usr.uid=mng.member_id 
    inner join managers smng on mng.leader_id = smng.member_id

为指定的高层经理添加:

for specified top manager add :

where smng.leader_id =1

对于指定的中层经理更改为:

for a specified middle manager change to :

where mng.leader_id =2

这篇关于MySQL递归子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！