sql - 如何用层次关系表示多列的范围？

Question

我正在将旧的会计软件移植到 SQL。这是由会计科目表组成的示例:


帐户
子账户
子帐目
子子子帐目
帐号
姓名


1110
0
0
0
1110
银行

1110
1
0
0
1110-1
美国银行

1110
1
1
0
1110-1-1
银行一

1110
1
1
1
1110-1-1-1
第一银行 #123456

1110
1
1
2
1110-1-1-2
第一银行#234567

1110
1
1
11
1110-1-1-11
第一银行 #11223344

1110
1
2
0
1110-1-2-0
银行二

1110
1
2
1
1110-1-2-1
第二银行#876543

1110
2
0
0
1110-2
外资银行

1110
2
1
0
1110-2-1
日本一号#556677

1120
0
0
0
1120
应收账款

1120
1
0
0
1120-1
美国应收账款

1120
1
1
0
1120-1-1
一区

1120
1
1
1
1120-1-1-1
客户AAA

1120
1
1
2
1120-1-1-2
客户 BBB

1120
1
1
3
1120-1-1-3
客户CCC

1120
1
2
0
1120-1-2-0
二区

1120
1
2
1
1120-1-2-1
客户万维网

1120
1
2
2
1120-1-2-2
客户YY


我需要查询任何范围的帐户，例如从帐号1110-1-1-2 到帐号1120-1-2。
这有效:

SELECT * FROM Accounts 
WHERE FORMAT(Account,'D8')+'-'+
      FORMAT(SubAcct,'D8')+'-'+
      FORMAT(SubSubAcct,'D8')+'-'+
      FORMAT(SubSubSubAcct,'D8') 
   BETWEEN '00001110-00000001-00000001-00000002' 
   AND     '00001120-00000001-00000002-00000000'
ORDER BY Account,SubAcct,SubSubAcct,SubSubSubAcct

但我认为这不是一个好方法。这是一个带有示例架构和数据的 SQLFiddle。
对于如何表达查询或更好的表定义的任何想法，我将不胜感激。

Answer 1

查看了AccountNumber的结构后，我突然意识到还有另一个有趣的选择。
我们可以添加一个 **persisted**名为 HierID 的列转换您的 AccountNumber成HierarchyID数据类型。那么我们就可以利用HierID.IsDescendantOf甚至应用你的范围
您可以更改您的表格或查看 dbFiddle

Alter Table Accounts add [HierID] as convert(hierarchyid,'/'+replace(AccountNumber,'-','/')+'/')  PERSISTED;

注:创建索引是可选的，但强烈建议。

现在，假设我想要 1110-1-1 Bank One 之间的所有内容和 1120 Receivables (including descendants)查询将如下所示:

Declare @R1 varchar(50) = '1110-1-1'
Declare @R2 varchar(50) = '1120'

Select * 
  from Accounts
  Where HierID between convert(hierarchyid,'/'+replace(@R1,'-','/')+'/')
                   and convert(hierarchyid,'/'+replace(@R2+'-99999','-','/')+'/')

结果

现在，假设我想要 1110-1 US Banks 的后代，查询将如下所示:

 Declare @S varchar(50) = '1110-1'

 Select * 
  From Accounts
  Where HierID.IsDescendantOf( convert(hierarchyid,'/'+replace(@S,'-','/')+'/') ) = 1

结果