表结构:sid (主键) date(日期) money(金额)
1 2011-07-01 300
2 2011-07-11 400
3 2011-07-21 500
4 2011-08-01 400
5 2011-08-14 600
6 2011-08-22 700现需要,每个月最后一次消费金额。结果:date money2011-07-21 500
2011-08-22 700
小弟愚笨,求指教!
1 2011-07-01 300
2 2011-07-11 400
3 2011-07-21 500
4 2011-08-01 400
5 2011-08-14 600
6 2011-08-22 700现需要,每个月最后一次消费金额。结果:date money2011-07-21 500
2011-08-22 700
小弟愚笨,求指教!
select to_char(createdate,'YYYY-MM-DD'),money from test1 t11 where createdate in (
select max(createdate) from test1 t12 group by to_char(createdate,'MM')
)
表结构:sid (主键) date(日期) money(金额)
1 2011-07-01 300
2 2011-07-11 400
3 2011-08-21 200
4 2011-08-22 400
5 2011-09-14 600
6 2011-09-22 700
7 2011-10-04 300
8 2011-10-12 500
现需要,8到9月,每个月最后一次消费金额。结果:date money2011-08-22 400
2011-09-22 700
SELECT 2 ,'2011-07-11' ,400 FROM DUAL UNION ALL
SELECT 3 ,'2011-07-21' ,500 FROM DUAL UNION ALL
SELECT 4 ,'2011-08-01' ,400 FROM DUAL UNION ALL
SELECT 5 ,'2011-08-14' ,600 FROM DUAL UNION ALL
SELECT 6 ,'2011-08-22' ,700 FROM DUAL)
select s_date,s_money from (select s_id,s_date,s_money,ROW_NUMBER() OVER(PARTITION BY substr(s_date,1,7) ORDER BY to_number(a.s_id) desc) rn
from a )t where t.rn=1
select max(createdate) from test1 t12 group by to_char(createdate,'MM')
having to_char(createdate,'MM') ='08' or to_char(createdate,'MM') ='09' )
/
create table tb([sid] int,[date] varchar(10),[money] int)
go
insert into tb select 1,'2011-07-01',300
insert into tb select 2,'2011-07-11',400
insert into tb select 3,'2011-07-21',500
insert into tb select 4,'2011-08-01',400
insert into tb select 5,'2011-08-14',600
insert into tb select 6,'2011-08-22',700;with t as(
select row_number() over(partition by month([date]) order by [date] desc) rn,* from tb
)
select [date],[money] from t
where rn=1-----------------------------------date money
2011-07-21 500
2011-08-22 700
select 1 sid,date '2011-07-01' ddate,300 money from dual union all
select 2,date '2011-07-11',400 from dual union all
select 3,date '2011-08-21',200 from dual union all
select 4,date '2011-08-22',400 from dual union all
select 5,date '2011-09-14',600 from dual union all
select 6,date '2011-09-22',700 from dual union all
select 7,date '2011-10-04',300 from dual union all
select 8,date '2011-10-12',500 from dual
) select f.sid,f.ddate,f.money from (select t.*, max(t.sid) keep( dense_rank last order by trunc(t.ddate,'mm')) over( partition by trunc(t.ddate,'mm')) a from t ) f
where (f.sid=f.a and to_char(f.ddate,'mm')='08')
or (f.sid=f.a and to_char(f.ddate,'mm')='09')
select * from table where date in (select max(date) from table where minDate< date < maxDate group by substr(date, 0,6))
select max(createdate) from test1 t12 group by to_char(createdate,'MM')
having to_char(createdate,'MM') ='08' or to_char(createdate,'MM') ='09' )参照这个写出来。3Q
select row_number() over(partition by trunc(createdate,'mm') order by trunc(createdate,'MM') desc) rn,* from tb
)
where rn=1
select row_number() over(partition by to_char(createdate,'mm') order by to_char(createdate,'MM') desc) rn,* from tb
)
where rn=1