SQL Server，查找任意值序列

只许你无理取闹

假设我们有一张表Maintenance
Customer LastLogin ActionType
1       12/1/2007 2
1       12/2/2007 2
etc.
我们想要一个在给定年份中的任何时间点上具有一个或多个不间断序列（长14天）且操作类型为2的登录的所有客户的列表。
我当然可以轻松地用代码来做到这一点，甚至可以在小型代码集上相当快地做到这一点。在SQL中有非游标方法吗？

解决方案:


            这将选择具有至少两个连续的相同类型操作的所有客户。
WITH rows AS
      (
      SELECT  customer, action,
            ROW_NUMBER() OVER (PARTITION BY customer ORDER BY lastlogin) AS rn
      FROM mytable
      )
SELECT  DISTINCT customer
FROM rows rp
WHERE EXISTS
      (
      SELECT  NULL
      FROM rows rl
      WHERE rl.customer = rp.customer
            AND rl.rn = rp.rn + 1
            AND rl.action = rp.action
      )
这是正义行动的更有效查询2：
WITH rows AS
      (
      SELECT  customer, ROW_NUMBER() OVER (PARTITION BY customer ORDER BY lastlogin) AS rn
      FROM mytable
      WHERE action = 2
      )
SELECT  DISTINCT customer
FROM rows rp
WHERE EXISTS
      (
      SELECT  NULL
      FROM rows rl
      WHERE rl.customer = rp.customer
            AND rl.rn = rp.rn + 1
      )
更新2：
要选择不间断范围：
WITH rows AS
      (
      SELECT  customer, action, lastlogin
            ROW_NUMBER() OVER (PARTITION BY customer ORDER BY lastlogin) AS rn
            ROW_NUMBER() OVER (PARTITION BY customer, action ORDER BY lastlogin) AS series
      FROM mytable
      )
SELECT  DISTINCT customer
FROM (
      SELECT  customer
      FROM rows rp
      WHERE action
      GROUP BY
            customer, actioncode, series - rn
      HAVING
            DETEDIFF(day, MIN(lastlogin), MAX(lastlogin)) >= 14
      ) q
此查询计算两个序列：一个返回连续的序列ORDER BY lastlogin，第二个通过action另外划分：
action  logindate rn  series diff = rn - series
1    Jan 01 1 1    0
1    Jan 02 2 2    0
2    Jan 03 3 1    2
2    Jan 04 4 2    2
1    Jan 05 5 3    2
1    Jan 06 6 4    2
只要两种方案之间的差异相同，该系列就不会中断。每次打断都会打断系列。
这意味着（action, diff）的组合定义了不间断的组。
我们可以按分组action, diff，在分组中找到MAX和MIN，然后对其进行过滤。
如果您需要选择14行而不是14连续几天，只是过滤的COUNT(*)，而不是DATEDIFF。

SQL Server，查找任意值序列

只许你无理取闹 LV1