从一个列中提取分层数据

Question

我有以下示例数据：

它基本上显示了以下层次结构：

我应该从中获取所有（S）开始（E）结束对，如下所示（顺序不重要）：

我的解决方案是将所有值推入“源”表变量中：

1.选择所有连续的（S）开始（E）结束对 2.将它们插入“目标”表变量中 3.从“源”表变量中删除它们 4.如果“源”表变量不为空，则执行步骤1

我的问题是，有人能想出另一种解决方案吗？在我的实际情况中，我有更多的行，并且我担心在表变量中进行删除和插入。

下面的脚本生成示例数据：

DECLARE @DataSource TABLE
(
    [ID] TINYINT
   ,[Type] CHAR(1)
)

INSERT INTO @DataSource ([ID], [Type])
VALUES (3,'S')
      ,(6,'E')
      ,(7,'S')
      ,(10,'S')
      ,(13,'E')
      ,(14,'E')
      ,(15,'S')
      ,(16,'S')
      ,(17,'S')
      ,(19,'S')
      ,(20,'S')
      ,(26,'E')
      ,(27,'E')
      ,(28,'E')
      ,(29,'E')
      ,(30,'E')
      ,(31,'S')
      ,(32,'S')
      ,(35,'E')
      ,(36,'S')
      ,(38,'E')
      ,(39,'S')
      ,(40,'S')
      ,(44,'E')
      ,(45,'E')
      ,(46,'E')

- gotqn

---

嘿 @gotqn，我做了一些调整。 - t-clausen.dk

非常感谢@t-clausen.dk。我真的很喜欢您如何使用WINDOW函数。 - gotqn

Answer 1

这有点复杂，但可以尝试这个：

。

;with a as
(
  select [ID], [Type], row_number() over (order by ID) rn from @DataSource
), b as
(
  select [ID], [Type], rn, cast(1 as int) lvl from a where rn = 1
  union all
  select a.[ID], a.[Type],a.rn, case when a.[Type] = 'S' then 1 when a.[Type] = 'E' then -1 else 0 end + lvl 
  from a
  join b on a.rn = b.rn + 1
), 
c as
(
  select [ID], [Type], row_number() over (partition by [type] order by lvl,rn) grp
  from b
)
select c1.id S, c2.id E from c c1
join c c2
on c1.grp = c2.grp
where c1.[type] = 'S' and c2.[type] = 'E'
order by c1.id
option( maxrecursion 0) 

结果：

S   E
3   6
7   14
10  13
15  30
16  29
17  28
19  27
20  26
31  46
32  35
36  38
39  45
40  44

编辑：由于您正在使用sqlserver 2012，所以脚本可以简化，我还添加了一个提高性能的改进。希望这在您的情况下有效。脚本现在假定's'始终在'e'之前。

;with a as
(
  select [ID], [Type], 
  sum(case when [type]='S' then 1 when [type]='E' then -1 end)over(order by id) lvl
  from @DataSource
), b as
(
  select [ID], [Type], 
  row_number() over (partition by [type] order by lvl,id) grp
  from a
)
select min(id) S, max(id) E 
from b
group by grp
order by s

- t-clausen.dk

---

这个解决方案虽然正确，但在我的实际情况中应用起来太复杂了。不过毕竟它是一种可行的替代方案。 - gotqn