求一条SQL 想不出来了

[快乐大法师]

ID DATE
1 2013/5/7
2 2013/8/6
3 2013/6/12
4 2013/12/1

一个表当中 ID 1 2 3 4 这样 时间 也应该是从小到大 现在想把 时间不是从小到大的找出来 比如 2 2013/8/6
和3 2013/6/12 就不对 把这两条找出来

[bw555]

SELECT ID,DATE FROM(
select id,DATE,
lag(DATE,1,DATE)OVER (ORDER BY ID) as PREDATE
lead(DATE,1,DATE)OVER (ORDER BY ID) as NEXTDATE
from T
) WHERE PREDATE>DATE OR NEXTDATE<DATE

[hepe00]

;WITH CTE(ID,DATE) AS(
SELECT 1,'2013/05/07' UNION ALL
SELECT 2 ,'2013/08/06' UNION ALL
SELECT 3  ,   '2013/06/12' UNION ALL
SELECT 4   ,  '2013/12/01' UNION ALL
SELECT 5,'2014/01/22'
)
SELECT * FROM CTE AS A
WHERE EXISTS(SELECT 1 FROM CTE AS B WHERE A.DATE<B.DATE AND A.ID>B.ID)

[不写代码的钦]

确保你的date列是date类型或者格式标准的字符类型， 要不然不能正常排序 select t.d,t.date from (select d,date,rownum rn1, row_number() over(partition by d,date order by t.date) rn2 from tablename) t where rn1<>rn2;

[lyangsoft]

分别以ID、DATE排序，然后编号，以编号比较两次的结果，不同ID、DATE的则是你要的

SELECT T1.ID, T1.DATE
  FROM (SELECT ID, DATE, ROWNUM R FROM TABLE_NAME ORDER BY ID) T1,--以id排序
       (SELECT ID, DATE, ROWNUM R FROM TABLE_NAME ORDER BY DATE) T2--以date排序
 WHERE T1.R = T2.R--按照编号连接
   AND T1.ID <> T2.ID --值不相等的