分层SQL查询

发布于 2024-08-03 19:41:24 字数 657 浏览 4 评论 0原文

我有一个自引用表（客户）和一个将链接到该表（公司）中的一条记录的表，即

Customers      Companies
*********      *********
ID             ID
ManagerID  --> DirectorID

ManagerID 引用客户表中的另一条记录。

我需要执行一个查询，通过给定特定的客户 ID，它将找到客户所属的公司。如果我在 C# 中执行此操作，它将看起来像这样（这是示例代码，不是功能性的）：

public static Company FindCompany(Customer customer)
{
     while (customer.ManagerID != null)
     {
          customer = customer.GetManager();
     }
     return Company.FindByDirector(customer.ID);
}

因此有 2 个步骤：

1) 遍历 Customer 表（通过 ManagerID），直到找到没有 ManagerID 的 Customer。（董事）

2) 找到与该客户相关的公司。

有人可以帮我吗？

谢谢。

原文

I have a self-referencing table (Customers) and a table that will link to one record in that table (Companies) i.e.

Customers      Companies
*********      *********
ID             ID
ManagerID  --> DirectorID

ManagerID refers to another record in the Customer table.

I need to perform a query where by given a specific customer ID, it will find the Company that customer belongs to. If I was to do this in C# it would look something like (this is sample code, not functional):

public static Company FindCompany(Customer customer)
{
     while (customer.ManagerID != null)
     {
          customer = customer.GetManager();
     }
     return Company.FindByDirector(customer.ID);
}

So there are 2 steps:

1) Traverse up the Customer table (via ManagerID) until we find a Customer with no ManagerID. (The Director)

2) Find the Company relating to that Customer.

Can anyone help me out?

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

葮薆情 2024-08-10 19:41:24

像这样的东西，虽然未经测试。

基于 CTE 使用公用表表达式的递归查询

WITH cTE AS
(
    SELECT --Get manager of given customer
        ManagerID
    FROM
        Customers
    WHERE
        ID = @MyCustomerID
    UNION ALL
    SELECT --Get manager of the manager etc
        Customers.ManagerID
    FROM
        cTE
        JOIN
        Customers ON cTE.ManagerID = Customers.ID
)
SELECT
    *
FROM
    cTE
    JOIN
    Company ON cTE.ManagerID = Company.DirectorID

Something like this, untested though.

Based on CTEs Recursive Queries Using Common Table Expressions

WITH cTE AS
(
    SELECT --Get manager of given customer
        ManagerID
    FROM
        Customers
    WHERE
        ID = @MyCustomerID
    UNION ALL
    SELECT --Get manager of the manager etc
        Customers.ManagerID
    FROM
        cTE
        JOIN
        Customers ON cTE.ManagerID = Customers.ID
)
SELECT
    *
FROM
    cTE
    JOIN
    Company ON cTE.ManagerID = Company.DirectorID

回复收藏 0 原文

~没有更多了~