在DB2中递归查询以获取链中的所有项目 [英] Recursive query in DB2 to get all items in the chain

问题描述

我必须通过仅提供一个作为输入来检索通过贷款链接的所有客户端。示例我有一个表数据作为

I have to retrieve all clients linked via loans by giving only one as input. Example I have a table data as

TABLEA

LOAN_ID    CLIENT_ID
1          7
1          8
2          7
4          8
4          9
4         10
5          9
5         11
13        2
14        3

如果我只输入了CLIENT_ID = 7的输入，那么查询必须从上表中选择所有列，除了最后两列，因为client_id 7具有1,2个LOAN_ID，而在1个CLIENT_ID 8中具有loan_id = 4，并且在该借用中，CLIENT_id 9再次具有5作为loan_id。

If I have given only input as CLIENT_ID=7 then the query has to select all the columns from above table except last two column because client_id 7 has 1,2 LOAN_ID and in 1 the CLIENT_ID 8 has loan_id=4 and in this loan CLIENT_id 9 has again 5 as loan_id.

我们可以在DB2中编写一个没有存储过程的sql查询吗？

can we write a sql query for this without stored procedure in DB2 ?

推荐答案

使用递归CTE查询的问题的答案：

Here is the answer to your question using recursive CTE query:

WITH links AS
( SELECT 
    loan_id, 
    client_id as c1, 
    client_id as c2, 0 as distance 
  FROM 
    myTable 
  -- recursion 
  UNION ALL
  SELECT 
     t.loan_id, 
     l.c1 as c1, 
     tt.client_id as c2, 
     distance = distance + 1 
  FROM 
    links l INNER JOIN
    myTable t ON l.c2 = t.client_id
    AND l.loan_id != t.loan_id INNER JOIN
    myTable tt  ON t.loan_id = tt.loan_id 
     AND t.client_id != tt.client_id
 )
SELECT * FROM myTable t
WHERE EXISTS 
    (SELECT * FROM links 
     WHERE c2 = t.client_id and c1 = 7);

http://sqlfiddle.com/#!3/8394d/16

我已经离开距离里面的查询，使其更容易理解。

I have left distance inside the query to make it easier to understand.

这篇关于在DB2中递归查询以获取链中的所有项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！