出现错误,继续请教

Question

用测试数据通过，但在数据库中执行时出现错误，日期为字符型，所以用视图先转换成日期型(to_date(日期,'yyyy-mm')SQL codeSELECT 姓名, Start_HM, End_HMFROM(SELECT b.姓名,MIN (b.日期) Start_HM,MAX (b.日期) End_HMFROM (SELECT a.*,a.日期, ADD_MONTHS(a.日期, ROWNUM* (-1)) ccFROM (SELECT*FROM tORDERBY 姓名, 日期 
                    ) a 
              ) bGROUPBY b.姓名, b.cc)WHERE MONTHS_BETWEEN(End_HM,Start_HM)>=5; 

Answer 1

是我写错了，应该是to_date(日期,'yyyymm')数据库中的日期是200901这样对吧

Answer 2

改成to_date(日期,'yyyymm')，同样的错误

Answer 3

这个字段有空值？还是最大值超过了先跑下面这个看看：select to_date(日期,'YYYYMM') from tablename where 日期 is not null;

Answer 4

如果这个还有问题，那么用
select to_date(trim(日期),'YYYYMM') from tablename where 日期 is not null;

Answer 5

select to_date(日期,'YYYYMM') from tablename where 日期 is null;
没有数据
select to_date(日期,'YYYYMM') from tablename where 日期 is not null;
显示正常，不过数据很多，几十万条

Answer 6

噢，懂了
因为数据量太大，所以若按全表排序的话，使用ADD_MONTHS计算时会超出范围
试试下面这个sql吧
SELECT 姓名, Start_HM, End_HM FROM(
         SELECT b.姓名, MIN (b.日期) Start_HM, MAX (b.日期) End_HM
         FROM (SELECT a.*, ADD_MONTHS(TO_DATE(a.日期,'YYYYMM'), RN * (-1)) cc
               FROM (SELECT t.*,ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 日期) RN
                     FROM t
                    ) a
              ) b
         GROUP BY b.姓名, b.cc) 
WHERE MONTHS_BETWEEN(TO_DATE(End_HM,'YYYYMM'),TO_DATE(Start_HM,'YYYYMM')) >= 4;

Answer 7

select max(rn) FROM (SELECT t.*,ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 日期) RN
                     FROM t
                    ) ;这个结果是多少？

Answer 8

那应该不会有问题啊，69也才6年不到，
SELECT ADD_MONTHS(TO_DATE(a.日期,'YYYYMM'), RN * (-1)) 
               FROM (SELECT t.*,ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 日期) RN
                     FROM t
                    );这句会出错吗？查查看

Answer 9

少写了表别名aSELECT ADD_MONTHS(TO_DATE(a.日期,'YYYYMM'), RN * (-1)) 
               FROM (SELECT t.*,ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 日期) RN
                     FROM t
                    ) a;

Answer 10

那看来内层没有问题了，只剩下最外一层：
SELECT b.姓名, MIN (b.日期) Start_HM, MAX (b.日期) End_HM   
FROM (SELECT a.*, ADD_MONTHS(TO_DATE(a.日期,'YYYYMM'), RN * (-1)) cc 
      FROM (SELECT t.*,ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 日期) RN
            FROM t
           ) a
     ) b 
GROUP BY b.姓名, b.cc;看看它有无问题，若再无问题就整个运行

Answer 11

是指数据库中有两条姓名，日期都相同的记录吗？如果有重复根据row_number()的做法；假设：
NAME DATE
TOM  200801
TOM  200801它们在排序的时候 row_number()的值是不一样的，分别是1，2，这种情况下200801 - 1和200801 - 2会是两个不同的结果，它后面的200802 - 3, 200803 - 4，这样row_number()为2的记录会和它后面的记录group在一起，而第一条200801记录则被丢弃。如果重复记录出现在中间的日期上，那这组日期会因此而中断，
如
200701，200702，200703，200703，200704，200705
因为200703有两个，则会导致该分组变成：
200701 200702 200703和200703 200704 200705两组可以在最底层select t.*时加distinct避免这类情况。

Answer 12

你的猜测没错
如果我没有理解错的话，distinct 应该加在
SELECT t.*,ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 日期) RN
            FROM t
中吧

Answer 13

你的猜测没错 
如果我没有理解错的话，distinct 应该加在 
SELECT t.*,ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 日期) RN 
            FROM t 
中吧 

Answer 14

嗯是的，可以把select t.*改成select distinct t.姓名,t.日期

Answer 15

错了，因为row_number的结果是不同的，
还需要改个地方SELECT t.*,ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 日期) RN改成SELECT distinct t.姓名,t.日期,DENSE_RANK() OVER(PARTITION BY 姓名 ORDER BY 日期) RN