一个小问题～ 送分

Question

数据库A表里有个这个字段：‘changetime’ 里面记录的数据为：‘20080901121121’这样日期年月日＋时间的数据。
现在想：查某个月（200809）之前包括9月changetime里最大的数  怎么写？

Answer 1

select max(字段) from A where to_date(substr(changetime,1,8),'yyyy-mm-dd')<=to_date('20080930','yyyy-mm-dd')

Answer 2

select max(changetime) from (select changetime from A where changetime>'20080900000000') t;

Answer 3

select max(changetime) from  A where changetime>'20080900000000'

Answer 4

select max(changetime) from A where substr(changetime,1,8)<=20080930

Answer 5

SQL> select * from t;CHANGETIME                                                                                       NUM
------------------------------------------------------------ ---------------------------------------
20080901121121                                                                                    12
20080801121121                                                                                    24
20080701121121                                                                                    30
20080601121121                                                                                    60
20081001121121                                                                                    90SQL> 
SQL> select changetime,max(num) num from t
  2  where to_date(substr(changetime,1,6),'yyyymm')<=to_date('200809','yyyymm')
  3  group by changetime order by num desc;CHANGETIME                                                          NUM
------------------------------------------------------------ ----------
20080601121121                                                       60
20080701121121                                                       30
20080801121121                                                       24
20080901121121                                                       12SQL> 
SQL> select * from (
  2    select changetime,max(num) num from t
  3    where to_date(substr(changetime,1,6),'yyyymm')<=to_date('200809','yyyymm')
  4    group by changetime
  5  ) A  where rownum=1 order by num;CHANGETIME                                                          NUM
------------------------------------------------------------ ----------
20080601121121                                                       60SQL> 

Answer 6

select max(字段) from A where to_date(substr(changetime,1,8),'yyyy-mm-dd') <=to_date('20080930','yyyy-mm-dd')