SQL查询-从级别和子级索引获取父级索引 [英] SQL query - get parent index from level and child index
问题描述
我的数据集有两列：索引和级别。
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.
规则是任何记录的父记录的级别均为=儿童级别-1。
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.
对于每个记录，我需要进行SQL查询，以获取记录的父索引。
For each record, I need to SQL query that will get the record's parent index.
SQL查询将是自连接，并获取自连接索引所在的最大索引值< child.index和自我连接级别= child.level
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
我需要帮助来弄清楚如何编写此SQL。
I need help to figure out how to write this SQL.
我可以使用MS Access或在VBA中使用SQL来执行此查询。
I can use MS Access or use SQL in VBA to perform this query.
这是数据集的直观表示。
This is a visual representation of the data set.
这是示例数据和预期结果..想要获取父级索引..父级是子级-1。
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
编辑添加：
我试图在Excel Power Query中执行此操作，并找到了答案，但要花很长时间才能运行因此需要找到SQL VBA / ADO解决方案。但是，这里有Power Query解决方案，可帮助您给出有关如何执行SQL的想法。
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"
此Power Query解决方案在每个行索引<所有索引和级别= -1级
This Power Query solution finds Max index where each row index < all index and level = level -1
推荐答案
--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
这篇关于SQL查询-从级别和子级索引获取父级索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
