SQL query - get parent index from level and child

问题:

I have dataset with two columns: index and level.

Level is number indicating level in hierarchy of nested parent child records.

The records are in order of hierarchy and index is just the line number of record.

The rule is that any record's parent record has level = child level - 1.

My challenge is to identify the parent's index based on this rule.

For each record, I need to SQL query that will get the record's parent index.

The SQL query will be a self join, and get the max index value where the self join index < child.index and the self join level = child.level

I need help to figure out how to write this SQL.

I can use MS Access or use SQL in VBA to perform this query.

This is a visual representation of the data set.

This is sample data and expected result .. want to get parent index .. parent level is child level - 1.

Index,Level Number,Parent Level,Parent Index
1,1,1,1
2,2,1,1
4,4,3,3
9,9,8,8
3,3,2,2
5,5,4,4
8,8,7,7
6,6,5,5
7,7,6,6
10,10,9,9
11,11,10,10
12,12,11,11
13,13,12,12
14,14,13,13
15,14,13,13
16,14,13,13
17,14,13,13
18,14,13,13
19,14,13,13
20,14,13,13
21,13,12,12
22,13,12,12
23,13,12,12
24,14,13,23
25,14,13,23
26,14,13,23
27,11,10,10
28,9,8,8
29,9,8,8
30,9,8,8
31,9,8,8
32,9,8,8
33,9,8,8
34,9,8,8
35,8,7,7
36,9,8,35
37,10,9,36
38,11,10,37
39,11,10,37
40,12,11,39
41,12,11,39
42,13,12,41
43,13,12,41
44,13,12,41
45,11,10,37
46,12,11,45
47,13,12,46
48,14,13,47
49,14,13,47
50,14,13,47
51,14,13,47
52,14,13,47
53,14,13,47
54,14,13,47
55,13,12,46
56,13,12,46
57,13,12,46
58,9,8,35
59,9,8,35
60,9,8,35
61,9,8,35
62,8,7,7
63,8,7,7
64,8,7,7
65,8,7,7
66,8,7,7
67,8,7,7
68,8,7,7

Edited to add: I tried to do this in Excel Power Query, and found an answer, but it takes forever to run so need to find SQL VBA/ADO solution. But here is Power Query solution to help give ideas about how to do it SQL.

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle3"]}[Content],
    ParentIndex = Table.AddColumn(Source, "ParentIndex", each let Index=[Index], LN=[Level Number] in List.Max(Table.SelectRows(Source, each _[Index] < Index and _[Level Number]=LN-1)[Index])),
    #"Merged Queries" = Table.NestedJoin(ParentIndex,{"ParentIndex"},ParentIndex,{"Index"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Level Number"}, {"Level Number.1"})
in
    #"Expanded NewColumn" 

This Power Query solution finds Max index where each row index < all index and level = level -1

回答1:

DECLARE @t TABLE (val INT)
INSERT INTO @t
VALUES 
    (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),
    (14),(14),(14),(14),(14),(14),(14),(13),(13),(13),(14),(14),(14),(11)

SELECT REPLICATE(' ', val) + CAST(val AS VARCHAR(10))
FROM @t

Output

-----------------------------
 1
  2
   3
    4
     5
      6
       7
        8
         9
          10
           11
            12
             13
              14
              14
              14
              14
              14
              14
              14
             13
             13

回答2:

--http://stackoverflow.com/questions/36639349/sql-query-get-parent-index-from-level-and-child-index
declare @table table
(idx int, level int)

insert into @table
(idx,level)
values
(1,1),
(2,2),
(3,3),
(4,4),
(5,5),
(6,6),
(7,7),
(8,8),
(9,9),
(10,10),
(11,11),
(12,12),
(13,13),
(14,14),
(15,14),
(16,14),
(17,14),
(18,14),
(19,14),
(20,14),
(21,14),
(22,13),
(23,13),
(24,13),
(25,14),
(26,14),
(27,14),
(28,11),
(29,9),
(30,8)

select  v.idx,v.level,v.parentlevel,u.idx parentidx
from
(
select s.* from                             --Find the first idx,level 
(
select  t.*,  t.level - 1 as parentlevel,
        row_number() over (partition by level order by idx,level) rownum
from    @table t
) s
where rownum = 1
) u
join                                        --join to every occurance of 
(select t2.*, t2.level - 1 parentlevel,
        1 as  rownum
from    @table t2
) v
on (v.parentlevel = u.level and v.rownum = u.rownum) 
union                                       --and put 1 back
select  w.idx,w.level,w.level,w.idx
from    @table w
where   w.idx = 1
order by v.idx