如何用层次关系表示多列的范围？
我正在将旧的会计软件移植到 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 |
回答
在查看了 的结构后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
结果
回答
为了完整起见，这是一种简单的方法。性能应该比你现在的更好。
SELECT *
FROM Accounts
WHERE
(
account > 1110 OR
account = 1110 AND subacct > 1 OR
account = 1110 AND subacct = 1 AND subsubacct > 1 OR
account = 1110 AND subacct = 1 AND subsubacct = 1 AND subsubsubacct >= 2
) AND (
account < 1120 OR
account = 1120 AND subacct < 1 OR
account = 1120 AND subacct = 1 AND subsubacct < 2 OR
account = 1120 AND subacct = 1 AND subsubacct = 2 AND subsubsubacct <= 0
)
account BETWEEN 1110 AND 1120如果优化器未能找到合适的范围扫描，您可以添加到条件中。
回答
冗余数据是旧会计表定义的主要问题。例如，它有 SubAcct、SubSubAcct、SubSubSubAcct 和 Sub...Acct 列。我相信这张表没有遵守规范化规则。
如果你想创建一个更好的表定义，那么我可以假设你定义了 3 列而不是 6 列，因为你可以管理更多的子账户而不是 3 个子账户。
CREATE TABLE [dbo].[Accounts](
[AccountID] [int] NOT NULL,
[ParentAccountID] [int] NULL,
[Name] [VARCHAR](100) NOT NULL,
CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED
(
[AccountID] ASC
),
CONSTRAINT FK_ParentAccount FOREIGN KEY (ParentAccountID)
REFERENCES Accounts(AccountID)
);
我通过递归关系更改了您的结构和值，以便更好地维护。
INSERT INTO Accounts
([AccountID], [ParentAccountID], [Name])
VALUES
(1110,null, 'Banks'),
(11101,1110, 'US Banks'),
(111011,11101, 'Bank One'),
(1110111,111011, 'Bank One #123456'),
(1110112,111011, 'Bank One #234567'),
(11101111,1110111 , 'Bank One #11223344'),
(1110120, 1110112, 'Bank Two'),
(1110121, 1110112, 'Bank Two #876543'),
(11101211, 1110121, 'Bank Two #876543')
;
通过此查询，您可以找到“Level”、“Path”、“Root”
此外，您可以通过“之间”语法过滤它
WITH CTE_TreeAccounts
AS ( SELECT ParentAccountID ,
Name ,
Name AS FullPathName ,
CAST(AccountID AS VARCHAR(100)) AS FullPathID ,
0 AS lvl ,
AccountID,
AccountID AS rootid
FROM Accounts
WHERE ParentAccountID IS NULL
UNION ALL
SELECT ac.ParentAccountID ,
ac.Name AS name ,
CAST(CONCAT(ISNULL(actree.FullPathName, ''), ' / ',
ac.Name) AS VARCHAR(100)) AS name ,
CAST(CONCAT(ISNULL(actree.FullPathID, ''), '-',
ac.AccountID) AS VARCHAR(100)) AS name ,
actree.lvl + 1 ,
ac.AccountID,
actree.rootid
FROM Accounts AS ac
INNER JOIN CTE_TreeAccounts actree ON actree.AccountID = ac.ParentAccountID
)
Select * from CTE_TreeAccounts
这是一个带有示例架构和数据
的SQLFiddle