TSQL -验证连续性的查询

Question

我有张桌子看起来像这样：

  Id  |  PersonId  |    Date    |  Number  |  NumberOld  
------+------------+------------+----------+-----------
   1  |     1      |  2014 1 1  |    1     |      0      
   2  |     1      |  2014 1 2  |    2     |      1      
   3  |     1      |  2014 1 3  |    3     |      2      
   4  |     2      |  2014 1 1  |    1     |      0      
   5  |     2      |  2014 1 2  |    3     |      2      
   6  |     2      |  2014 1 3  |    4     |      3      

我想要的是一个查询，它让我得到一个没有给出数字和数字连续性的人。

所以对于PersonId = 1，一切都很好，所以查询不应该返回person1。

1 - 0
2 - 1
3 - 2

但对于PersonId = 2，则没有给出这些数字的连续性。

1 - 0
3 - 2
4 - 3

第一记录的数字1与第二记录的数字2不相对应。

我怎样才能实现这样的目标？

Answer 1

您可以使用延迟来查看前一条记录。因此，您会发现不匹配，并且在找到的PersonID中选择人员的历史记录：

select *
from mytable
where personid in
(
  select personid
  from
  (
    select personid, numberold, lag(number) over (partition by personid order by logdate, number) as numberbefore
    from mytable
  ) lookup
  where numberold <> numberbefore
)
order by personid, logdate, number;

下面是一个SQL花招：http://sqlfiddle.com/#!6/991e7/2。

Answer 2

我认为您可以使用left join来查找不匹配的内容：

select t.person
from table t left join
     table tnext
     on t.person = tnext.person and t.number = tnext.numberold
where tnext.person is null
group by t.person
having count(*) > 1;

join查找数字不匹配的所有行。然而，对于一个给定的人，总会有至少一个(最后一个)。这是使用having子句过滤掉的。

Answer 3

我倾向于使用NOT EXISTS vs LEFT JOIN/IS NULL，因为作为一般规则，它在Server中性能更好

SELECT  *
FROM    T AS t1
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    T AS t2
            WHERE   t1.PersonID = t1.PersonID
            AND     t2.NumberOld = t1.Number
        );

这在您的情况下可能没有任何不同，但性能提高的潜在原因是Server无法将LEFT JOIN/IS NULL方法优化为一个反半连接，也就是说，它将在筛选出空值之前返回所有结果，而NOT EXISTS将在找到匹配时立即停止搜索。上面链接的文章更详细，但是为了演示您的查询，我创建了下表：

CREATE TABLE #T 
(
    ID INT IDENTITY(1, 1), 
    PersonId INT, 
    Date DATE,
    Number INT, 
    NumberOld INT
);
INSERT #T (PersonID, Date, Number, NumberOld) 
VALUES 
    (1, '2014-01-01', 1, 0), (1, '2014-01-02', 2, 1), (1, '2014-01-03', 3, 2),
    (2, '2014-01-01', 1, 0), (2, '2014-01-02', 3, 2), (2, '2014-01-03', 4, 3);

然后运行以下两个查询：

SELECT  *
FROM    #T AS t1
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    #T AS t2
            WHERE   t1.PersonID = t1.PersonID
            AND     t2.NumberOld = t1.Number
        );

SELECT  t1.*
FROM    #T AS t1
        LEFT JOIN #T AS t2
            ON  t1.PersonID = t1.PersonID
            AND t2.NumberOld = t1.Number
WHERE   t2.PersonID IS NULL;

比较这两个方案，您可以在顶部计划中看到反半连接，但是在下面的计划中会出现一个普通的连接，然后是一个过滤器：

​

​