sql高手们，是你们发挥的时候了

[guorui88_cool]

大家看看下面的列表，按日期进行升序排序时，正常情况下，n_num 下的值应该是逐渐变大才是正确，我如何通过一个sql就可以查询出了不符合要去的需要的数据。（过滤出不符合规律的数据），只有20分了，大家帮帮忙吧

c_id d_date n_num
01 2008-03-17 0
02 2008-03-18 1
03 2008-03-19 2
04 2008-03-20 3
05 2008-03-21 4
06 2008-03-22 3
07 2008-03-23 5
08 2008-03-24 4
09 2008-03-25 10
10 2008-03-26 8
11 2008-03-27 9
12 2008-03-28 10
13 2008-03-29 11
14 2008-03-30 6

[一颗向往数据的心丶]

楼主的所谓逐渐变大是啥意思？比如排序后第N条记录n_num =99,第N+1条记录n_num =89,第N+2条记录n_num =100 展示哪些数据？

[一颗向往数据的心丶]

这个是我理解的一种情况：

select c_id,d_date,n_num from (
select t.*,max(n_num) over(order by t.d_date) ld from table_name t order by t.d_date
) where n_num >= ld

[guorui88_cool]

我这个列表是数据库中的真实数据，按业务来的，聚个例子： 06 2008-03-22 3这条数据就是有问题，因为 3 这个值应该 大于或者等于2008-03-22 日期之前的数据才对，现在的目的就是把这些错误数据，能通过sql一下查出来；

[zbdzjx]

最好把你要的结果也贴出来，再说一下规则。

[guorui88_cool]

这些就是我要的结果： c_id d_date n_num 06 2008-03-22 3 08 2008-03-24 4 10 2008-03-26 8 11 2008-03-27 9 14 2008-03-30 6 如果通过写存储过程，用递归找到这些数据是不成问题，不过这样效率太低，我需要的是通过sql是否可以一步实现

[zbdzjx]

试试这句：

with table1 as (
select '01' c_id, '2008-03-17' d_date, 0 n_num from dual union all
select '02' c_id, '2008-03-18' d_date, 1 n_num from dual union all
select '03' c_id, '2008-03-19' d_date, 2 n_num from dual union all
select '04' c_id, '2008-03-20' d_date, 3 n_num from dual union all
select '05' c_id, '2008-03-21' d_date, 4 n_num from dual union all
select '06' c_id, '2008-03-22' d_date, 3 n_num from dual union all
select '07' c_id, '2008-03-23' d_date, 5 n_num from dual union all
select '08' c_id, '2008-03-24' d_date, 4 n_num from dual union all
select '09' c_id, '2008-03-25' d_date, 10 n_num from dual union all
select '10' c_id, '2008-03-26' d_date, 8 n_num from dual union all
select '11' c_id, '2008-03-27' d_date, 9 n_num from dual union all
select '12' c_id, '2008-03-28' d_date, 10 n_num from dual union all
select '13' c_id, '2008-03-29' d_date, 11 n_num from dual union all
select '14' c_id, '2008-03-30' d_date, 6 n_num from dual
)
select * from table1 a where n_num<(select max(n_num) n_num from table1 b where b.c_id<a.c_id)

[zbdzjx]

再补充一下，我的那句是判断的c_id，如果要判断d_date，那 b.c_id<a.c_id 改成 b.d_date<a.d_date 。