sql - 如何比较组中的行？

Question

我有一个表格如下:

 INS_SEQ |  NAME |     DATE
-----------------------------
       1 | willy | 15/11/10
       2 |   zoe | 15/11/20
       3 | willy | 15/11/20
       4 | willy | 15/11/10
       5 | willy | 15/11/21
       6 |   zoe | 15/11/17
       7 | willy | 15/11/12
       8 |   zoe | 15/11/20
       9 |   zoe | 15/11/30
... and other values ...

按 NAME 列分组返回:

 INS_SEQ |  NAME |     DATE
----------------------------
       1 | willy | 15/11/10
       3 | willy | 15/11/20
       4 | willy | 15/11/10
       5 | willy | 15/11/21
       7 | willy | 15/11/12
       2 |   zoe | 15/11/20
       6 |   zoe | 15/11/17
       8 |   zoe | 15/11/20
       9 |   zoe | 15/11/30

我想得到一个结果，对于相同的值NAME，前一个日期大于下一个日期(换句话说，我想看到满足前一个插入日期的那些行小于注册同名)。
如何实现？

我的预期输出是:

 NAME |     DATE
-----------------
willy | 15/11/20
willy | 15/11/21
  zoe | 15/11/20
  zoe | 15/11/17
  zoe | 15/11/30

Answer 1

首先，您必须编写一个查询来查找不按顺序排列的日期，然后确保不包含同名的最后一条记录。

-- Finding dates which are not in order
(SELECT * FROM CompareRows  C1 WHERE EXISTS 
   (SELECT * FROM CompareRows  C2 
     WHERE C2.ID < C1.ID
       AND C2.OrderDate < C1.OrderDate
       AND C2.Name = C1.Name))

 EXCEPT
 -- Not including last record with same name
(SELECT C1.*
FROM CompareRows C1 LEFT JOIN CompareRows C2
 ON (C1.name = C2.name AND C1.ID < C2.ID)
WHERE C2.ID IS NULL)

这是现场演示 http://www.sqlfiddle.com/#!6/51865/1/0