如何提取某个表每个月最后一天

[陈灬风]

如何提取某个表每个月最后一天，比如我表格A里面有很多数据，要取数据为每个月的最后一天，各位有什么好的建议？
表格A
date data
2016/1/31 11
2016/2/29 qe
2016/3/31 qe
2016/2/22 a
2016/1/1 a
要得到的数据为
date data
2016/1/31 11
2016/2/29 qe
2016/3/31 qe

[xu176032]

with tb1 as
 (select date '2016-01-31' as dat, '11' as data
    from dual
  union all
  select date '2016-02-29' as dat, 'qe' as data
    from dual
  union all
  select date '2016-03-31' as dat, 'qe' as data
    from dual
  union all
  select date '2016-02-22' as dat, 'a' as data
    from dual
  union all
  select date '2016-01-01' as dat, 'a' as data from dual)

select dat, data
  from (select dat,
               data,
               row_number() over(partition by to_char(dat, 'yyyymm') order by dat desc) as fn
          from tb1)
 where fn = 1

[zengjc]

使用LAST_DAY函数，date字段的值在LAST_DAY中即可，具体代码等会再写

[zbdzjx]

with table1 as
(select date '2016-01-31' as dat, '11' as data from dual
 union all
 select date '2016-02-29' as dat, 'qe' as data from dual
 union all
 select date '2016-03-31' as dat, 'qe' as data from dual
 union all
 select date '2016-02-22' as dat, 'a' as data from dual
 union all
 select date '2016-01-01' as dat, 'a' as data from dual
)
select * from table1 where to_char(dat,'mm') <> to_char(dat + 1,'mm')

[z123zjf]

select max(dat) from table1 group by to_char(dat,'yyyymm')

[z034736]

with table1 as (select date '2016-01-31' as dat, '11' as data from dual union all select date '2016-02-29' as dat, 'qe' as data from dual union all select date '2016-03-31' as dat, 'qe' as data from dual union all select date '2016-02-22' as dat, 'a' as data from dual union all select date '2016-01-01' as dat, 'a' as data from dual ) select a.dat,a.data from table1 a,( select max(dat) dat from table1 group by to_char(dat,'yyyy-MM') order by to_char(dat,'yyyy-MM') desc ) b where a.dat = b.dat

[jiana839353733]

1樓方法不錯，學習了！

[zengjc]

上次一打岔，把这事情给忘记了，这次补上代码，使用last_day无需排序

with tb1 as
 (select date '2016-01-31' as dat, '11' as data
    from dual
  union all
  select date '2016-02-29' as dat, 'qe' as data
    from dual
  union all
  select date '2016-03-31' as dat, 'qe' as data
    from dual
  union all
  select date '2016-02-22' as dat, 'a' as data
    from dual
  union all
  select date '2016-01-01' as dat, 'a' as data from dual) 
select * from tb1 where dat=last_day(dat);

引用 2 楼 zengjc 的回复:

使用LAST_DAY函数，date字段的值在LAST_DAY中即可，具体代码等会再写