时间戳

首页 > 解决方案 > 获取数据SQL Server中最后一条记录的id

sql - 获取数据SQL Server中最后一条记录的id

问题描述

我试图从 ID 之间的至少 4 个子父关系中获取最后一个 ID，并将所有相关 ID 的数量相加。我在下面尝试过 -

declare @test table (ID int not null, P_ID int null, Qty int not null)
insert into @test(ID, P_ID, Qty) values
(1 , 11  , 1),
(2 , null, 3),
(11, 21  , 2),
(21, 31  , 1),
(31, null, 3),
(12, null, 4)

select
    COALESCE(T2.ID,T1.ID) as ID,
    MAX(CASE WHEN T1.P_ID is not null then T1.ID END) as OldID,     
    SUM(Qty) as Qty
from   
    @test T1
        left join
        (select ID from @test 
            GROUP By ID) T2
            on  T2.ID = T1.P_ID

group by
    COALESCE(T2.ID, T1.ID)

我得到输出 -

ID  OldID   Qty
2   NULL    3
11  1       1
12  NULL    4
21  11      1
31  21      2

但我希望我的输出将是这样的，其中第一行中所有没有父 ID 的 ID 然后所有以前的 ID 将显示以及SUM所有相关 ID 的数量 -

ID  OldID3  OldID2  OldID1  Qty
2                            3  
12                           4
31  21      11      1        7

有人可以帮我实现这一目标。

提前致谢

标签： sqlsql-server

解决方案

希望这对您有所帮助。我没有彻底测试它，所以对任何错误表示歉意。

我使用公用表表达式来获取层次结构信息，然后使用动态 SQL 提取所需数量的先前 ID。

DECLARE @test TABLE (ID INT NOT NULL, P_ID INT NULL, Qty INT NOT NULL);
INSERT INTO @test(ID, P_ID, Qty) VALUES
(1 , 11  , 1),
(2 , null, 3),
(11, 21  , 2),
(21, 31  , 1),
(31, null, 3),
(12, null, 4);


IF (OBJECT_ID('tempdb..#hierarchy') IS NOT NULL)
    DROP TABLE #hierarchy;
CREATE TABLE #hierarchy (
    RootID INT NOT NULL, ID INT NOT NULL, [Qty] INT NOT NULL, SeqIndex INT NOT NULL
);

;WITH hierarchy AS (
    SELECT ID, P_ID, Qty, ID [RootID], 0 [SeqIndex]
    FROM @test
    WHERE P_ID IS NULL

    UNION ALL

    SELECT child.ID, child.P_ID, child.Qty, parent.RootID, parent.SeqIndex + 1 [SeqIndex]
    FROM @test child
    JOIN hierarchy parent ON parent.ID=child.P_ID
)
INSERT #hierarchy
SELECT RootID, ID, Qty, SeqIndex 
FROM hierarchy;


DECLARE 
    @DEPTH INT = 3,
    @maxSeqIndex INT = (SELECT MAX(SeqIndex) FROM #hierarchy);

IF (@DEPTH = 0) 
    SELECT RootID, SUM(Qty) [Qty] 
    FROM #hierarchy
    GROUP BY RootID;
ELSE IF (@DEPTH > @maxSeqIndex)
    SELECT NULL
ELSE BEGIN
    DECLARE @SQL NVARCHAR(MAX) = N'
    SELECT 
        RootID, 
';
    DECLARE @idx INT = 1;
    WHILE @idx <= @DEPTH BEGIN
        SET @SQL += N'
        (SELECT ID FROM #hierarchy i WHERE i.RootID=o.RootID AND SeqIndex='+CAST(@idx as nvarchar(10))+N') [OldID'+CAST(@maxSeqIndex-@idx+1 as nvarchar(10))+N'],';
        SET @idx += 1;
    END

    SET @SQL += N'
        SUM(Qty) [Qty] 
    FROM #hierarchy o
    GROUP BY RootID;';

    EXEC sp_executesql @SQL
END

当然，如果您觉得可以的话，可以用硬编码的 SQL 替换动态脚本。

注：性能未考虑

推荐阅读