从同一个表中提取两次数据。 [英] Pulling data from the same table twice.

问题描述

我正在尝试修复我的表中的一些不良数据，我需要一些SQL视图的帮助。 我有100条记录具有NULL AccountNumber。 这些记录具有accountid和NULL parentaccountid。 这些记录有大约3000美元b $ b儿童记录，这些记录有自己的记录和父母与其相关的记录相同。  以下SQL仅返回100个具有空数的记录。 它不会返回与ParentAccount关联的
子记录。 任何帮助将不胜感激。

I am trying to fix some bad data in a table of mine and I need some help with an SQL view.  I have 100 records that have a NULL AccountNumber.  These records have a accountid and a NULL parentaccountid.  Those records have approximately 3000 child records which have their own accountid and a parentaccountid that is the same as the accountid it is associated too.   The below SQL is only returning the 100 records that have a null number.  It is not returning the child records that are associated to the ParentAccount.  Any help would be appreciated.

推荐答案

您需要使用递归来检索这些行。下面是一个示例：

You need to use recursive to retrieve these rows. Here is an example:

create table Accounts (accountid int,parentaccountid int, Accountnumber int)

Insert into  Accounts values (1,null,null),(2,null,null),(3,null,null),
(4,1,40),
(5,1,50),
(6,2,60),
(7,2,70),
(8,3,80)

;with rcte as (
Select accountid,parentaccountid,Accountnumber , 0 lvl from Accounts
where Accountnumber is null
Union ALL
Select a.accountid,a.parentaccountid,a.Accountnumber  , lvl+1 as lvl
from  rcte r join Accounts a on r.accountid=a.parentaccountid )

Select * from rcte





drop table Accounts

这篇关于从同一个表中提取两次数据。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！