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

2016-02-25 03:43:31 7.3k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

如何在Java 8中获取一个月的第一天和最后一天如何在Java 8中获取一个月的第一天和最后一天、传入一个年月转为时间区间，该月的第一天和最后一天阅读详情

如何提取某个表每个月最后一天，比如我表格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

...全文

LAMMPS in文件详解：从零开始手把手教你配置金属分子动力学模拟本文详细解析了LAMMPS in文件的配置方法，手把手教你完成金属分子动力学模拟的全流程。从软件环境准备、势函数选择，到使用Materials Studio构建模型、编写in文件进行能量最小化与系统弛豫，再到实施单轴拉伸生产模拟并提取应力应变数据，最后通过Python和OVITO进行结果分析与可视化，提供了完整的实战指南和常见问题排查技巧。阅读详情

陈灬风

Oracle 高级技术 3,499 成员 18,710 内容

+ 关注 + 加入社区

切换为时间正序

zengjc 2016-03-02

打赏
举报

上次一打岔，把这事情给忘记了，这次补上代码，使用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中即可，具体代码等会再写

jiana839353733 2016-02-26

打赏
举报

1樓方法不錯，學習了！

z034736 2016-02-26

打赏
举报

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

z123zjf 2016-02-25

打赏
举报

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

zbdzjx 2016-02-25

打赏
举报

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')

zengjc 2016-02-25

打赏
举报

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

xu176032 2016-02-25

打赏
举报



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

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

相关推荐