ms访问中的多级查询 [英] Multilevel query in ms access

问题描述

大家好，



我正在尝试在ms access 2013中编写一个查询，该查询将输出下属的下属和下属，以便为给定的经理输出。需要一些提示，因为我无法想到任何解决方案。我只停留在第一级。

任何帮助或暗示或解决方案都会有很大的帮助。我使用ms访问2013与c＃.net。



表格如下。例如，对于经理sm1输出将是m1，m2，e1，e2，e3，对于经理m1输出将是e1，e2。

Hi All,

I am trying to write a query in ms access 2013 which will output subordinates and subordinates of subordinates and so on for a given manager. Need some hint as I am unable to think of any solution. I am stuck at first level only.
Any help or hint or work around will be of great help. I am using ms access 2013 with c#.net.

table is in the following form. E.g for manager sm1 output will be m1,m2,e1,e2,e3 and for manager m1 output will be e1, e2.

emp	mangr
e1	m1
e2	m1
e3	m2
m1	sm1
m2	sm1
m3	sm2
e4	m3

我的尝试：



这是一个简单的查询获得第一级下属：



SELECT emp from test where mangr ='sm1';



但我无法超越这一点。我还需要来自test的emp，其mangr是此查询的输出。

What I have tried:

this is a simple query to get first level subordinates:

SELECT emp from test where mangr='sm1';

But I am not able to go beyond that. I also need emp from test whose mangr is output of this query.

推荐答案

您需要将子查询与IN子句一起使用。我会尝试这样的事情：

You need to use subquery together with IN clause. I'd try something like that:

SELECT emp
FROM Table1 
WHERE mangr IN
    (
     SELECT emp
     FROM Table1
     WHERE mangr='sm1'
     );

详情请见：查询条件示例 - 访问 [ ^ ]

这可以使用公用表表达式（CTE）来实现真正的SQL引擎。


Access对CTE没有任何支持。我不知道在Access中的单个查询中执行此操作的方法。您必须使用VBA代码才能执行此操作。

This can be done is a real SQL engine using a Common Table Expression (CTE).

Access does not have any support for CTEs. I don't know of a way to do this in a single query in Access. You'd have to use VBA code to do it.

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