根據第一個ID查詢獲取遞回記錄-有解無憂

我正在嘗試獲得給定地段的第一個位置，以及有多少其他位置被翻新到該第一個位置。我的桌子是：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Spots](
[SpotID] [int] NOT NULL,
[LotID] [int] NULL,
[RefurbedSpotID] [int] NULL,
[datestamp] [datetime] NULL,
 CONSTRAINT [PK_Spots] PRIMARY KEY CLUSTERED 
(
    [SpotID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (178, 5, 
NULL, CAST(N'2022-08-30T12:41:50.833' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (183, 5, 
178, CAST(N'2022-08-30T12:42:01.263' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (193, 5, 
183, CAST(N'2022-08-30T12:43:03.527' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (212, 5, 
NULL, CAST(N'2022-08-30T12:43:12.373' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (213, 5, 
193, CAST(N'2022-08-30T12:43:19.023' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (380, 70, 
NULL, CAST(N'2022-08-30T12:43:36.837' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (381, 70, 
380, CAST(N'2022-08-30T12:43:43.637' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (382, 70, 
NULL, CAST(N'2022-08-30T12:43:54.780' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (383, 70, 
381, CAST(N'2022-08-30T12:44:06.390' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (384, 70, 
383, CAST(N'2022-08-30T12:44:13.800' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (385, 71, 
NULL, CAST(N'2022-08-30T12:46:17.660' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (389, 70, 
NULL, CAST(N'2022-08-30T12:44:17.070' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (398, 71, 
NULL, CAST(N'2022-08-30T12:46:26.670' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (465, 
101, NULL, CAST(N'2022-08-30T12:46:44.450' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (491, 
118, NULL, CAST(N'2022-08-30T12:48:32.380' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (1496, 
 118, 491, CAST(N'2022-08-30T12:48:38.497' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (1497, 
118, NULL, CAST(N'2022-08-30T12:48:42.617' AS DateTime))
GO
INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (1498, 
118, 1496, CAST(N'2022-08-30T12:48:49.290' AS DateTime))
GO
 INSERT [dbo].[Spots] ([SpotID], [LotID], [RefurbedSpotID], [datestamp]) VALUES (1633, 
70, 382, CAST(N'2022-08-30T12:44:24.183' AS DateTime))
GO
ALTER TABLE [dbo].[Spots] ADD  CONSTRAINT [DF_Spots_datestamp]  DEFAULT (getdate()) FOR 
[datestamp]
gO

這是預期的結果。第一個 SpotID 在括號中。

LotID : 5

213, 193, 183,  [ 178 ]

LotID : 70 x

384, 383, 381, [ 380 ]

LotID : 71 x

[ 385 ]

LotID : 101 x

[ 465 ]

對于 LotID : 118

1498, 1496,  [ 491 ]

我嘗試的是：

select *
from ( 
    select s2.spotID, s2.lotID , s2.RefurbedSpotID  
    from spots s2
    where s2.lotID = @lotID
    and s2.RefurbedSpotID is not null 

    union 

    select s1.spotID, s1.lotID, s1.RefurbedSpotID  
    from spots s1
    inner join ( select s2.spotID as s2_SpotID, s2.lotID as s2_lotID, s2.RefurbedSpotID 
as s2_RefurbedSpotID
                 from spots s2
                 where s2.lotID = @lotID ) s3 on s2_RefurbedSpotID = s1.spotID 
    where s1.lotID = @lotID 
    ) q
 order by spotID

但是，我沒有得到 LotID 70、71 和 101 的正確結果。有什么建議嗎？

uj5u.com熱心網友回復：

然后，您需要一個遞回 CTE。從每個的第一行開始，LotId并計算一Level列以及遞回。

STRING_AGG然后，您只需使用withWITHIN GROUP (ORDER BY子句進行聚合

WITH cte AS (
    SELECT
      s.SpotID,
      s.LotID,
      SpotIdString = CAST(CONCAT('[ ', s.SpotId, ' ]') AS varchar(30)),
      Level = 1
    FROM (
        SELECT *,
          rn = ROW_NUMBER() OVER (PARTITION BY s.LotID ORDER BY s.datestamp)
        FROM Spots s
    ) s
    WHERE s.rn = 1
    
    UNION ALL
    
    SELECT
      s.SpotID,
      s.LotID,
      SpotIdString = CAST(s.SpotId AS varchar(30)),
      Level = cte.Level   1
    FROM cte
    JOIN Spots s ON s.RefurbedSpotID = cte.SpotID AND s.LotID = cte.LotID
)
SELECT
  cte.LotID,
  STRING_AGG(SpotIdString, ', ') WITHIN GROUP (ORDER BY cte.Level DESC)
FROM cte
GROUP BY
  cte.LotID;

另一種選擇是在遞回期間計算字串，然后使用過濾到遞回的最后一個ROW_NUMBER

WITH cte AS (
    SELECT
      s.SpotID,
      s.LotID,
      SpotIdString = CAST(CONCAT('[ ', s.SpotId, ' ]') AS varchar(4000)),
      Level = 1
    FROM (
        SELECT *,
          rn = ROW_NUMBER() OVER (PARTITION BY s.LotID ORDER BY s.datestamp)
        FROM Spots s
    ) s
    WHERE s.rn = 1
    
    UNION ALL
    
    SELECT
      s.SpotID,
      s.LotID,
      SpotIdString = CAST(CONCAT(s.SpotID, ', ', cte.SpotIdString) AS varchar(4000)),
      Level = cte.Level   1
    FROM cte
    JOIN Spots s ON s.RefurbedSpotID = cte.SpotID AND s.LotID = cte.LotID
)
SELECT
  cte.LotID,
  cte.SpotIdString
FROM (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY cte.LotID ORDER BY cte.Level DESC)
    FROM cte
) cte
WHERE cte.rn = 1;

db<>小提琴

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/507858.html

標籤：sql sql服务器递归

上一篇：遞回函式內的兩個呼叫

下一篇：遞回（一種子集總和，但不完全）-如果可以以確切的價格購買筆，則為真，否則為假