如果关键字是name和date_time,可以这样:select name,balance from table
where name||to_char(date_time,'yyyy-mm-dd') in (
select name||to_char(max(date_time),'yyyy-mm-dd') from table
group by name)比较怪异的写法。
where name||to_char(date_time,'yyyy-mm-dd') in (
select name||to_char(max(date_time),'yyyy-mm-dd') from table
group by name)比较怪异的写法。
create table tn
(
name varchar2(10),
balance int,
date_time varchar2(50))输入数据
insert into tn values('a', 30,'2004-08-09');
insert into tn values('a',10,'2004-09-10');
insert into tn values('a',5,'2004-09-11') ;
insert into tn values('b',50,'2004-08-01');
insert into tn values('b',5,'2004-09-11' ) ;
insert into tn values('c', 5 ,'2004-09-11') ;测试:
select * from tn where date_time in (select max(date_time) from tn group by name)结果:
NAME BALANCE DATE_TIME
---------- ---------- --------------------------------------------------
a 5 2004-09-11
b 5 2004-09-11
c 5 2004-09-11
建表:
create table tn
(
name varchar2(10),
balance int,
date_time varchar2(50))输入数据
insert into tn values('a', 30,'2004-08-09');
insert into tn values('a',10,'2004-09-10');
insert into tn values('a',5,'2004-09-11') ;
insert into tn values('b',50,'2004-08-01');
insert into tn values('b',5,'2004-09-11' ) ;
insert into tn values('c', 5 ,'2004-09-11') ;
insert into tn values('c', 8 ,'2004-09-12') ;SQL> select * from tn where date_time in (select max(date_time) from tn group by name);NAME BALANCE DATE_TIME
---------- --------------------------------------- -----------------------
a 5 2004-09-11
b 5 2004-09-11
c 5 2004-09-11
c 8 2004-09-12这样c就出现了两次!
2 where tn.name = max_tn.name and tn.date_time = max_tn.date_time
3 ;NAME BALANCE DATE_TIME
---------- --------------------------------------- ------------------------
a 5 2004-09-11
b 5 2004-09-11
c 8 2004-09-12
yjdn(无尽天空) 是我第一次用的方法结果会出现重得的记录
SQL> select tn.* from tn ,(select name,max(date_time) date_time from tn group by name) max_tn
2 where tn.name = max_tn.name and tn.date_time = max_tn.date_time
-------------------------
不错