代码之家 › 专栏 › 技术社区 › Doonie Darkoo

如何按级别对列进行分组

sql-server

Doonie Darkoo · 技术社区 · 7 年前

我有一个表结构和一些示例数据,如下所示。

CREATE TABLE COA
(
COACode NVARCHAR(50),
COATitle VARCHAR(50),
)

INSERT INTO COA VALUES (01, 'Expense');
INSERT INTO COA VALUES (01-01, 'Expense');
INSERT INTO COA VALUES (01-01-01, 'Expense');
INSERT INTO COA VALUES (01-01-02, 'Expense');
INSERT INTO COA VALUES (01-02, 'Expense');
INSERT INTO COA VALUES (01-02-01, 'Expense');
INSERT INTO COA VALUES (01-02-02, 'Expense');

我期望的输出是这样的。

COACode     COACode     COACode     COATitle
01                                  Expense
            01-01                   Expense
                        01-01-01    Expense
                        01-01-02    Expense
            01-02                   Expense
                        01-02-01    Expense
                        01-02-02    Expense

查询需要是动态的,就像这是3级COACode一样,但也可以或多或少地是动态的。所以,如果用户插入的COACode级别更高,则应该将其插入下一列,但如果它具有相同的COACode级别,则可以。

1 回复 | 直到 7 年前

Esat ARSLAN 7 年前

也许你可以这样做

CREATE TABLE COA
(
COACode NVARCHAR(50),
ParentCode NVARCHAR(50),
COATitle VARCHAR(50),
)

INSERT INTO COA VALUES ('01', NULL,'Expense');
INSERT INTO COA VALUES ('01-01', '01','Expense');
INSERT INTO COA VALUES ('01-01-01','01-01' ,'Expense');
INSERT INTO COA VALUES ('01-01-02','01-01', 'Expense');
INSERT INTO COA VALUES ('01-02', '01','Expense');
INSERT INTO COA VALUES ('01-02-01','01-02' ,'Expense');
INSERT INTO COA VALUES ('01-02-02','01-02' ,'Expense');

;WITH x AS
(
    -- anchor:
    SELECT COACode, COATitle, ParentCode, [level] = 0
    FROM COA WHERE ParentCode IS NULL
    UNION ALL
    -- recursive:
    SELECT t.COACode, t.COATitle, t.ParentCode, [level] = x.[level] + 1
    FROM x INNER JOIN COA AS t
    ON t.ParentCode = x.COACode
)
SELECT COACode, COATitle, ParentCode, [level] FROM x
ORDER BY [level]
OPTION (MAXRECURSION 32);

推荐文章