如何在sql中找到开始项的结束项？

pxyaymoc 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(329)

我遇到一个表，它的结构与下面的变量@t相似。
我想为每个开始条目找到结束条目，但这很困难，因为条目可能包含子条目。
你知道我怎么用它的位置为它的共同发起人startentrytext找到endentrytext吗？
条目的视觉表示：

-- Start 1
--  Start 2
--  Stop 2
--  Start 3
--      Start 4
--          Start 5
--          Stop 5
--      Stop 4
--  Stop 3
-- Stop 1

期望输出：

StartEntryText  EndEntryText
Start 1         Stop 1  
Start 2         Stop 2
Start 3         Stop 3
Start 4         Stop 4
Start 5         Stop 5

sql语句：

-- Simplified Table Structure
DECLARE @t TABLE (EntryText varchar(16) NULL
    , EntryType varchar(16)
    , EntryIndex int NULL
)

-- Test Data
INSERT INTO @t(EntryText, EntryType, EntryIndex) 
SELECT d.EntryText
    , d.EntryType
    , d.EntryIndex
FROM 
(
    SELECT 'Start 1 ' AS EntryText, 1 AS EntryIndex, 'Open' AS EntryType
    UNION ALL SELECT 'Start 2' AS EntryText, 2 AS EntryIndex, 'Open' AS EntryType
    UNION ALL SELECT 'Stop 2' AS EntryText, 3 AS EntryIndex, 'Close' AS EntryType
    UNION ALL SELECT 'Start 3' AS EntryText, 4 AS EntryIndex, 'Open' AS EntryType
    UNION ALL SELECT 'Start 4' AS EntryText, 5 AS EntryIndex, 'Open' AS EntryType
    UNION ALL SELECT 'Start 5' AS EntryText, 6 AS EntryIndex, 'Open' AS EntryType
    UNION ALL SELECT 'Stop 5' AS EntryText, 7 AS EntryIndex, 'Close' AS EntryType
    UNION ALL SELECT 'Stop 4' AS EntryText, 8 AS EntryIndex, 'Close' AS EntryType
    UNION ALL SELECT 'Stop 3' AS EntryText, 9 AS EntryIndex, 'Close' AS EntryType
    UNION ALL SELECT 'Stop 1' AS EntryText, 10 AS EntryIndex, 'Close' AS EntryType
) d

-- TODO: Find EndEntryText
SELECT t.EntryText AS StartEntryText
    , NULL AS EndEntryText
FROM @t t
WHERE t.EntryType = 'Open'

sql sql-server tsql

来源：https://stackoverflow.com/questions/62762122/how-can-i-find-the-end-entry-for-a-start-entry-in-sql

1条答案

按热度按时间

weylhg0b1#

不是一个完美的解决方案，但这应该做我需要的。
这基本上是通过将每个条目的祖先加在一起+1来计算每个条目的级别 OPEN 和-1表示 CLOSE . 然后得到第一个 CLOSE 每次之后 OPEN 在同一层面上。

-- Workout Indent level for each entry in the hierarchy
SELECT t.EntryText
    , t.EntryIndex
    , t.EntryType
    , r.Indent
INTO #t
FROM @t t
CROSS APPLY
(
    SELECT SUM(CASE WHEN s.EntryType = 'OPEN' THEN 1 ELSE -1 END)  
        + CASE WHEN t.EntryType = 'OPEN' THEN -1 ELSE 0 END AS Indent
    FROM @t s 
    WHERE s.EntryIndex <= t.EntryIndex
) r

-- Work out OPEN entry for CLOSE entry in the same level
SELECT tOpen.EntryText AS StartEntryText
    , t.EntryText AS EndEntryText
FROM #t t
OUTER APPLY   
(
    SELECT TOP(1) e.EntryText
        , e.EntryIndex
    FROM #t e
    WHERE e.EntryIndex < t.EntryIndex
        AND e.EntryType = 'OPEN'
        AND e.Indent = t.Indent
    ORDER BY e.EntryIndex DESC
) tOpen
WHERE t.EntryType = 'CLOSE'
ORDER BY tOpen.EntryIndex

DROP TABLE #t

赞(0）回复(0）举报 2021-07-24

我来回答

如何在sql中找到开始项的结束项？

1条答案

相关问题

热门标签

最新问答