代码之家 › 专栏 › 技术社区 › Scott Craner

创建链接ID

sql-server

Scott Craner · 技术社区 · 6 年前

我有下表:

Account_Number  Parent_Account  Child_Account
R003247         R000355         R000002
R000355         NULL            R003247
R000002         R003247         NULL
R004853         NULL            R028636
R004853         NULL            R028638
R004853         NULL            R028637
R028636         R004853         NULL
R028638         R004853         NULL
R028637         R004853         NULL

可装载:

create table dbo.temptable
(Account_Number varchar(10),
Parent_Account varchar(10),
Child_Account varchar(10))

insert into dbo.temptable
values
('R003247','R000355','R000002'),
('R000355',NULL,'R003247'),
('R000002','R003247',NULL),
('R004853',NULL,'R028636'),
('R004853',NULL,'R028638'),
('R004853',NULL,'R028637'),
('R028636','R004853',NULL),
('R028638','R004853',NULL),
('R028637','R004853',NULL)

此表表示帐号的拆分和重新分配。这与土地分割和组合的跟踪有关。

如上表所示,前三行应分组在一起,因为它来自 R000355 ---GT; R003247 ---GT; R000002

如上表所示,最后6个也应分组,如图所示。 R004853 分成三部分 R028636 , R028637 , R028638 .

我尝试过很多类似的变化:

SELECT CE.*,TT.ID
FROM dbo.temptable CE 
INNER JOIN
    (
    SELECT ACCOUNT_NUMBER,ROW_NUMBER() OVER (ORDER BY ACCOUNT_NUMBER) AS ID
    FROM(
    SELECT DISTINCT ACCOUNT_NUMBER FROM dbo.temptable where Child_Account is not null)AA
    )TT 
ON TT.ACCOUNT_NUMBER = CE.Account_Number OR TT.Account_Number = CE.Child_Account

是的:

Account_Number  Parent_Account  Child_Account   ID
R000355         NULL            R003247         1
R003247         R000355         R000002         2
R000355         NULL            R003247         2
R004853         NULL            R028636         3
R004853         NULL            R028638         3
R004853         NULL            R028637         3

当我真正需要的是:

Account_Number  Parent_Account  Child_Account   ID
R000355         NULL            R003247         1
R003247         R000355         R000002         1
R000002         R003247         NULL            1
R004853         NULL            R028636         2
R004853         NULL            R028638         2
R004853         NULL            R028637         2
R028636         R004853         NULL            2
R028638         R004853         NULL            2
R028637         R004853         NULL            2

1 回复 | 直到 6 年前

Tim Biegeleisen 6 年前

这个问题一点也不可怕,一旦你在手机上打了太多的字,就跳进去给自己打腕管。这只是一个稍微修改过的标准递归层次查询问题。注意,递归中的连接条件是当前帐号是某个父帐号的子帐号。至于编号,我们只使用 DENSE_RANK 上层家长。

WITH cte AS (
    SELECT m.*, DENSE_RANK() OVER (ORDER BY m.Account_Number) AS pos
    FROM temptable m
    WHERE Parent_Account IS NULL
    UNION ALL
    SELECT m.*, cte.pos
    FROM temptable m
    INNER JOIN cte
        ON m.Account_Number = cte.Child_Account
)

SELECT *
FROM cte
ORDER BY pos;

Demo

注:我非常感谢这里的回答。 written by @Quassnoi .