从SQL中的数字映射表创建树查询 [英] Create Tree Query From Numeric Mapping Table in 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数据源.

then I want to convert it to tree query in MS-Sql Server 2008 to use as a Treelist datasource in my win aaplication.

预期的SQL查询结果:

Expected SQL query Result:

 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

请您帮我创建SQL查询吗? 谢谢.

Would you please help me for creating SQL query? Thanks.

推荐答案

毫无疑问，无论您编写什么查询或执行任何DBA操作，由于表结构错误，您的查询都会很慢.

No doubts,whatever query you write or whatever DBA DO,your query will be slow because of faulty table structure.

我相信您的上述查询对于其他示例数据也能正常工作.

I believe your above query is working fine for other sample data also.

在这种情况下，我会像(c#等)那样在前端获取记录并在其中进行操作以获取期望的输出，在这种情况下通常会很快.

In such situation,I fetch my record in front end like (c# etc) and do manipulation there in order to get desire output,it is often fast in such cases.

我尝试用自己的方式编写查询，希望它能快速记录6000条记录.还请测试我的查询是否有其他示例数据.

I have tried to write query in my own way,hope it is fast for 6000 records.Also please test my query for other sample data also.

我找到LASTINdexOf("/")，然后加入

I find LASTINdexOf("/") then join

DECLARE @FilePath VARCHAR(50) = '11/11/001'
DECLARE @FindChar1 VARCHAR(1) = '/'

SELECT substring (@FilePath,0, LEN(@FilePath) - CHARINDEX(@FindChar1,REVERSE(@FilePath))+1 )AS LastOccuredAt

最终查询

DECLARE @tbl TABLE(id int identity(1,1), 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');


DECLARE @FindChar VARCHAR(1) = '/'


    SELECT a.id
        ,a.accountid
        ,a.AccountName
        ,(
            SELECT min(c.id)
            FROM @tbl c
            WHERE c.id < a.id
                AND substring(a.accountid, 0, LEN(a.accountid) - CHARINDEX(@FindChar, REVERSE(a.accountid)) + 1) = c.accountid
            ) ParentID
        ,LEN(a.AccountID) - LEN(REPLACE(a.AccountID, '/', '')) + 1 AS [level]
        ,(
            SELECT CASE 
                    WHEN min(c.id) IS NOT NULL
                        THEN 1
                    ELSE 0
                    END
            FROM @tbl c
            WHERE c.id > a.id
                AND substring(c.accountid, 0, LEN(c.accountid) - CHARINDEX(@FindChar, REVERSE(c.accountid)) + 1) = a.accountid
            ) Haschild
    FROM @tbl a
    INNER JOIN @tbl b ON a.id = b.id + 1

这篇关于从SQL中的数字映射表创建树查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！