有下面数据(实际数据很多)
卡号 金额1 金额2 时间
001 15.00 10.00 2008-01-01 12:00:00
002 15.00 10.00 2008-01-02 12:00:00
001 -15.00 0.00 2008-02-15 12:00:00
求一sql或proc,筛选出所有卡最后发生时间的金额1\金额2
即筛选出:
002 15.00 10.00 2008-01-02 12:00:00
001 -15.00 0.00 2008-02-15 12:00:00
卡号 金额1 金额2 时间
001 15.00 10.00 2008-01-01 12:00:00
002 15.00 10.00 2008-01-02 12:00:00
001 -15.00 0.00 2008-02-15 12:00:00
求一sql或proc,筛选出所有卡最后发生时间的金额1\金额2
即筛选出:
002 15.00 10.00 2008-01-02 12:00:00
001 -15.00 0.00 2008-02-15 12:00:00
group by 卡号,金额1,金额2;
where exists (select 1 from (select 卡号,max(时间) sj from tablename group by 卡号)a where 卡号 = t.卡号 and sj = t.时间)
select acct_id,sum1,sum2,occr_date from (
select acct_id,sum1,sum2,occr_date,
row_number() over(partition by acct_id order by occr_date desc) rn from test)
where rn=1;
create table tt
(
t01 varchar2(3)
,t02 number(10,2)
,t03 number(10,2)
,t04 date
)insert into tt values('001',15,10,'2008-01-01 12:00:00')
insert into tt values('001',15,10,'2008-01-02 12:00:00')
insert into tt values('001',-15,10,'2008-02-01 12:00:00')select max(t01),t02,t03,max(t04) from
tt
group by t02,t03
(select 卡号 from (select 卡号,max(时间) from Your_Table group by 卡号));
SELECT *
FROM TABLENAME
WHERE 卡号, 时间 IN (SELECT 卡号, MAX(时间) FROM TABLENAME GROUP BY 卡号);
b.时间 = (select max(a.时间) from table_name a where a.卡号 = b.卡号 and a.时间 = b.时间));
(
t01 varchar2(3)
,t02 number(10,2)
,t03 number(10,2)
,t04 date
)
insert into tt values('001',15,10,'2008-01-01');
insert into tt values('002',15,10,'2008-01-02');
insert into tt values('001',-15,10,'2008-02-01');三种写法:
1、
select t01,t02,t03,t04
from (select t01,t02,t03,t04,
row_number() over (partition by t01 order by t04 desc) rn
from tt )
where rn=12、
select a.*
from tt a,(select t01,max(t04) t04 from tt group by t01) b
where a.t01=b.t01
and a.t04=b.t04
3、
select a.*
from tt a
where exists (select b.t01,max(b.t04) from tt b group by b.t01
having a.t01=b.t01
and a.t04=max(b.t04));
时间 in (select max(时间) from 表 group by 卡号)
(
t01 varchar2(3)
,t02 number(10,2)
,t03 number(10,2)
,t04 date
);insert into wiler values('001',15,10,to_date('2008-01-01 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into wiler values('002',15,10,to_date('2008-01-02 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into wiler values('001',-15,10,to_date('2008-02-15 12:00:00','yyyy-mm-dd hh24:mi:ss'));select b.*
from (
select t01,max(t04) max_date
from wiler
group by t01
) a,wiler b
where a.t01=b.t01 and a.max_date=b.t04
FROM TABLENAME a
WHERE 时间 = (SELECT MAX(时间) FROM TABLENAME where 卡号=a.卡号);