sql - 基于 SQL Server 中多个先前行的值提取行

Question

我有一个包含示例数据的表，如下所示:

ID  Key   User
--  ----  -----
1   a     test
2   ab    test
3   abc   test
4   abcd  test
5   e     test1
6   ef    test1
7   efg   test1
8   efgh  test1
9   t     test1
10  ty    test1
11  tyu   test1
12  tyui  test1

数据由用户构建的值的连续“快照”组成。我想为用户的每个实例返回最后一行，构建一个独特的最终 Key值(value)。注意大多数行的 Key包含整个前一行的 Key再加上一封信？我只想要终止这样一个序列的行，并且是 Keys 的每个链中可能的最长值的行连续包含前一个 Key值(value)。

上述示例数据应返回以下内容:

ID  Key   User
--  ----  -----
4   abcd  test
8   efgh  test1
12  tyui  test1

我该怎么做呢？

Answer 1

在没有回答我的问题的情况下，我不得不做出以下假设:

ID列代表年表，并且总是加一，没有间隙。

SQL Server 2005 或更高版本

(更新:我做了一个小调整，使这项工作与来自不同用户的“交错”数据一起工作，并在我的 fiddle 中添加了一些交错和一些棘手的数据。)

所以这是我的解决方案。 See it in a SqlFiddle .值得注意的是它模拟了 LEAD来自 SQL Server 2012 的分析，没有 JOIN .

WITH Info AS (
  SELECT
     Grp = Row_Number() OVER (PARTITION BY UserName ORDER BY ID, Which) / 2,
     *
  FROM
     dbo.UserEntry U
     CROSS JOIN (
        SELECT 1 UNION ALL SELECT 2
     ) X (Which)
)
SELECT
   ID = Max(V.ID),
   DataKey = Max(V.DataKey),
   UserName = Max(V.UserName)
FROM
   Info I
   OUTER APPLY (SELECT I.* WHERE Which = 2) V
WHERE I.Grp > 0
GROUP BY
   I.UserName,
   I.Grp
HAVING
   Max(I.DataKey) NOT LIKE Min(I.DataKey) + '_';

输入:

INSERT dbo.UserEntry (ID, DataKey, UserName)
VALUES
(1, 'a', 'test'),
(2, 'ab', 'test'),
(3, 'e', 'test1'),
(4, 'ef', 'test1'),
(5, 'abc', 'test'),
(6, 'abcd', 'test'),
(7, 'efg', 'test1'),
(8, 'efgh', 'test1'),
(9, 't', 'test1'),
(10, 'ty', 'test1'),
(11, 'tyu', 'test1'),
(12, 'tyui', 'test1'),
(13, 't', 'test1'),
(14, 'a', 'test'),
(15, 'a', 'test'),
(16, 'ab', 'test'),
(17, 'abc', 'test'),
(18, 'abcd', 'test'),
(19, 'to', 'test1'), 
(20, 'abcde', 'test'),
(21, 'top', 'test1');

输出:

ID  DataKey  UserName
--  -------  --------
6   abcd     test
8   efgh     test1
12  tyui     test1
14  a        test
20  abcde    test
21  top      test1

注意:我使用了不同的列名，因为使用保留字作为列名不是最佳实践(它迫使您在所有名称周围加上方括号)。

我使用的技术将适用于单次扫描。它没有联接。使用适当索引的正确构造的基于连接的查询 5 月 在 CPU 和时间方面略胜一筹，但这个解决方案肯定会获得最少的读取。

更新

虽然我的查询可能很好，但这个问题中的特定数据结构本身提供了一个非常优雅的解决方案，我在第一次回答时没有考虑过。感谢 Andriy 的基本思想，这是一个炸药和超简单的查询(与上面的 fiddle 相同)。

WITH Info AS (
   SELECT
      Grp = Row_Number() OVER (PARTITION BY UserName ORDER BY ID) - Len(DataKey),
         *
   FROM
      dbo.UserEntry U
)
SELECT
   ID = Max(I.ID),
   DataKey = Max(I.DataKey),
   I.UserName
FROM
   Info I
GROUP BY
   I.UserName,
   I.Grp;