如何使用递归查询作为子查询? [英] How to use a recursive query as a subquery?

问题描述

我需要编写一个多次调用递归查询的查询.

I need to write a query that calls a recursive query many times.

我不知道该怎么做.我想我可以通过使用游标来做到这一点，在运行时准备 sql 语句，然后使用 EXEC(mySQLstatement) 在每个游标 FETCH NEXT 上运行它.

I was not able to figure out how to do. I guess I can do this by using a cursor, preparing the sql statement at run time and then use EXEC(mySQLstatement) to run it at every cursor FETCH NEXT.

无论如何这都不是好方法.

Anyway this is not the good approach.

这就是问题所在(当然这里简化了，我只留下必要的列来表达自己):我有一个客户树(一个层次结构)，并且为每个客户定义了一些联系人.

This is the problem (of course here it is simplified and I leave only the necessary columns to express myself): I have a tree of customers (a hierarchy) and for every customer there are some contacts defined.

CUSTOMERS 表包含一个 ID_CUSTOMER 字段和一个 ID_PARENT_CUSTOMER 字段CUSTOMER_CONTACTS 表包含一个 ID_CUSTOMER 字段和一个 ID_CONTACT 字段.

The CUSTOMERS table containts an ID_CUSTOMER field and an ID_PARENT_CUSTOMER field the CUSTOMER_CONTACTS table contains an ID_CUSTOMER field and an ID_CONTACT field.

通过此查询(它有效)，我能够获取客户 308 的所有联系人及其子客户的所有联系人:

With this query (it works) i am able to get all the contacts for customer 308 and all the contacts for its sub-customers:

with [CTE] as (
    select ID_CUSTOMER from CUSTOMERS c where c.ID_CUSTOMER = 308
    union all
    select c.ID_CUSTOMER from [CTE] p, CUSTOMERS c 
        where c.ID_PARENT_CUSTOMER = p.ID_CUSTOMER
)
select ID_CUSTOMER into #Customer308AndSubCustomers from [CTE]

select 308 as ParentCustomer, ID_CUSTOMER, ID_CONTACT,  from CUSTOMER_CONTACTS
WHERE ID_CUSTOMER IN (select * from #Customer308AndSubCustomers)
drop table #Customer308AndSubCustomers

但我希望在单个查询中对所有客户使用相同的查询，而不仅仅是 308.所以这就是我建议使用游标的原因，这样我就可以重用上述语句并只使用一个变量而不是308.

But I would like to have in a single query the same for ALL THE CUSTOMERS, not only for 308. So this is why I was suggesting to use a cursor so I can reuse the above statement and just use a variable instead of 308.

但是你能提出一个更好的查询吗?

But can you suggest a better query?

推荐答案

只需要去掉anchor部分的过滤条件:

Just remove the filtering condition from the anchor part:

WITH    q AS
        (
        SELECT  ID_CUSTOMER, ID_CUSTOMER AS root_customer
        FROM    CUSTOMERS c
        UNION ALL
        SELECT  c.ID_CUSTOMER, q.root_customer
        FROM    q
        JOIN    CUSTOMERS c 
        ON      c.ID_PARENT_CUSTOMER = q.ID_CUSTOMER
        )
SELECT  *
FROM    q

root_customer 会显示链的根.

请注意，同一客户可能会被多次退货.

Note that the same customers may be returned several times.

比如说，一个孙子将至少返回三次:在它的祖父树、它的父树和它自己的树中，但每次都有不同的 root_customer.

Say, a grandchild will be return at least thrice: in its grandparent tree, its parent tree and in its own tree, but each time with a different root_customer.

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