从SQL(特定格式)的数字映射表创建树查询 [英] Create Tree Query From Numeric Mapping Table in SQL (Specific Format)
本文介绍了从SQL(特定格式)的数字映射表创建树查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个从会计软件中导出的表格，如下所示.
I have an exported table from accounting software like below.
AccountID AccountName
--------- -----------
11 Acc11
12 Acc12
13 Acc13
11/11 Acc11/11
11/12 Acc11/12
11/111 Acc11/111
11/11/001 Acc11/11/001
11/11/002 Acc11/11/002
12/111 Acc12/111
12/112 Acc12/112
我想将其转换为MS-SQL Server 2008中的树查询，以将其用作我的获胜应用程序中的Treelist数据源. 我之前曾提出过这个问题，并且回答的方式对我的拥有5000多个记录的大表来说非常慢(
I want to convert it to tree query in MS-SQL Server 2008 to use it as a Treelist datasource in my win aaplication. I raised this question before and it's answered with a way that it was very very slow for my big table with more than 5000 records (Create Tree Query From Numeric Mapping Table in SQL). But I think counting "/" and separating AccountID field with "/" can solve my problem easier and very faster. Anyway, My expected result must be like below:
AccountID AccountName ID ParentID Level HasChild
--------- ----------- --- --------- ------ --------
11 Acc11 1 Null 1 1
12 Acc12 2 Null 1 1
13 Acc13 3 Null 1 0
11/11 Acc11/11 4 1 2 1
11/12 Acc11/12 5 1 2 0
11/111 Acc11/111 6 1 2 0
11/11/001 Acc11/11/001 7 4 3 0
11/11/002 Acc11/11/002 8 4 3 0
12/111 Acc12/111 9 2 2 0
12/112 Acc12/112 10 2 2 0
请帮助我.
推荐答案
我修改了第一个问题给出的答案...
I modified my answer given in the first question...
最好，如果您的表将关系数据直接保留在索引列中.在更改表的结构之前，您可以尝试以下方法:
It would be best, if your table would keep the relation data directly in indexed columns. Before you change your table's structure you might try this:
带有测试数据的表
DECLARE @tbl TABLE ( AccountID VARCHAR(100), AccountName VARCHAR(100));
INSERT INTO @tbl VALUES
('11','Acc11')
,('12','Acc12')
,('13','Acc13')
,('11/11','Acc11/11')
,('11/12','Acc11/12')
,('11/111','Acc11/111')
,('11/11/001','Acc11/11/001')
,('11/11/002','Acc11/11/002')
,('12/111','Acc12/111')
,('12/112','Acc12/112');
这会将所需的数据放入新创建的名为#tempHierarchy
This will get the needed data into a newly created temp table called #tempHierarchy
SELECT AccountID
,AccountName
,ROW_NUMBER() OVER(ORDER BY LEN(AccountID)-LEN(REPLACE(AccountID,'/','')),AccountID) AS ID
,Extended.HierarchyLevel
,STUFF(
(
SELECT '/' + A.B.value('.','varchar(10)')
FROM Extended.IDsXML.nodes('/x[position() <= sql:column("HierarchyLevel")]') AS A(B)
FOR XML PATH('')
),1,2,'') AS ParentPath
,Extended.IDsXML.value('/x[sql:column("HierarchyLevel")+1][1]','varchar(10)') AS ownID
,Extended.IDsXML.value('/x[sql:column("HierarchyLevel")][1]','varchar(10)') AS ancestorID
INTO #tempHierarchy
FROM @tbl
CROSS APPLY(SELECT LEN(AccountID)-LEN(REPLACE(AccountID,'/','')) + 1 AS HierarchyLevel
,CAST('<x></x><x>' + REPLACE(AccountID,'/','</x><x>') + '</x>' AS XML) AS IDsXML) AS Extended
;
中间结果
+-----------+--------------+----+----------------+------------+-------+------------+
| AccountID | AccountName | ID | HierarchyLevel | ParentPath | ownID | ancestorID |
+-----------+--------------+----+----------------+------------+-------+------------+
| 11 | Acc11 | 1 | 1 | | 11 | |
+-----------+--------------+----+----------------+------------+-------+------------+
| 12 | Acc12 | 2 | 1 | | 12 | |
+-----------+--------------+----+----------------+------------+-------+------------+
| 13 | Acc13 | 3 | 1 | | 13 | |
+-----------+--------------+----+----------------+------------+-------+------------+
| 11/11 | Acc11/11 | 4 | 2 | 11 | 11 | 11 |
+-----------+--------------+----+----------------+------------+-------+------------+
| 11/111 | Acc11/111 | 5 | 2 | 11 | 111 | 11 |
+-----------+--------------+----+----------------+------------+-------+------------+
| 11/12 | Acc11/12 | 6 | 2 | 11 | 12 | 11 |
+-----------+--------------+----+----------------+------------+-------+------------+
| 12/111 | Acc12/111 | 7 | 2 | 12 | 111 | 12 |
+-----------+--------------+----+----------------+------------+-------+------------+
| 12/112 | Acc12/112 | 8 | 2 | 12 | 112 | 12 |
+-----------+--------------+----+----------------+------------+-------+------------+
| 11/11/001 | Acc11/11/001 | 9 | 3 | 11/11 | 001 | 11 |
+-----------+--------------+----+----------------+------------+-------+------------+
| 11/11/002 | Acc11/11/002 | 10 | 3 | 11/11 | 002 | 11 |
+-----------+--------------+----+----------------+------------+-------+------------+
现在，发生了与我的第一个答案类似的递归方法.但是-由于它现在正在使用真实表，并且所有字符串拆分都已经发生-它应该更快...
And now a similar recursive approach takes place as in my first answer. But - as it is using a real table now and all the string splitting has taken place already - it should be faster...
WITH RecursiveCTE AS
(
SELECT th.*
,CAST(NULL AS BIGINT) AS ParentID
,CASE WHEN EXISTS(SELECT 1 FROM #tempHierarchy AS x WHERE x.ParentPath=th.AccountID) THEN 1 ELSE 0 END AS HasChild
FROM #tempHierarchy AS th WHERE th.HierarchyLevel=1
UNION ALL
SELECT sa.AccountID
,sa.AccountName
,sa.ID
,sa.HierarchyLevel
,sa.ParentPath
,sa.ownID
,sa.ancestorID
,(SELECT x.ID FROM #tempHierarchy AS x WHERE x.AccountID=sa.ParentPath)
,CASE WHEN EXISTS(SELECT 1 FROM #tempHierarchy AS x WHERE x.ParentPath=sa.AccountID) THEN 1 ELSE 0 END AS HasChild
FROM RecursiveCTE AS r
INNER JOIN #tempHierarchy AS sa ON sa.HierarchyLevel=r.HierarchyLevel+1
AND r.AccountID=sa.ParentPath
)
SELECT r.AccountID
,r.AccountName
,r.ID
,r.ParentID
,r.HierarchyLevel
,r.HasChild
FROM RecursiveCTE AS r
ORDER BY HierarchyLevel,ParentID;
最后我收拾了
DROP TABLE #tempHierarchy;
这是最终结果
+-----------+--------------+----+----------+----------------+----------+
| AccountID | AccountName | ID | ParentID | HierarchyLevel | HasChild |
+-----------+--------------+----+----------+----------------+----------+
| 11 | Acc11 | 1 | NULL | 1 | 1 |
+-----------+--------------+----+----------+----------------+----------+
| 12 | Acc12 | 2 | NULL | 1 | 1 |
+-----------+--------------+----+----------+----------------+----------+
| 13 | Acc13 | 3 | NULL | 1 | 0 |
+-----------+--------------+----+----------+----------------+----------+
| 11/11 | Acc11/11 | 4 | 1 | 2 | 1 |
+-----------+--------------+----+----------+----------------+----------+
| 11/111 | Acc11/111 | 5 | 1 | 2 | 0 |
+-----------+--------------+----+----------+----------------+----------+
| 11/12 | Acc11/12 | 6 | 1 | 2 | 0 |
+-----------+--------------+----+----------+----------------+----------+
| 12/111 | Acc12/111 | 7 | 2 | 2 | 0 |
+-----------+--------------+----+----------+----------------+----------+
| 12/112 | Acc12/112 | 8 | 2 | 2 | 0 |
+-----------+--------------+----+----------+----------------+----------+
| 11/11/001 | Acc11/11/001 | 9 | 4 | 3 | 0 |
+-----------+--------------+----+----------+----------------+----------+
| 11/11/002 | Acc11/11/002 | 10 | 4 | 3 | 0 |
+-----------+--------------+----+----------+----------------+----------+
这篇关于从SQL(特定格式)的数字映射表创建树查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
