;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)
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)
要不然不能正常排序
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;
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 --值不相等的
(1) 只比较相邻:
SELECT id, ddate
FROM (select id,ddate,
lag(ddate,1) over(order by id) as d1,
lead(ddate,1) over(order by id) as d2
from T0042)
WHERE d1 > ddate OR d2 < ddate
(2) 比较所有:
WITH T1 as (
select id,ddate,max(ddate)over(order by id) d1
from t0042),T2 as (
select id,min(ddate)over(order by id desc) d2
from t0042)
select T1.id,T1.ddate
from T2,T1
WHERE T1.id=T2.id and (t1.ddate<>T1.d1 or t1.ddate<>T2.d2)
SQL的写法较复杂难懂,其主要是因为集合无序,要用窗口函数拼出子查询造出次序。如果集合有序的话,上述运算就容易写了。可以试试润乾公司的免费产品集算器,上述运算可以很直观地写出来: 集算器提供JDBC接口,可以象数据库一样嵌入到应用程序中,用起来很简单。