我有一个表的数据是这样的:
id NO date
70000016_2 000090000000 2007-12-6 13:32:02
70000016_2 000090000000 2007-12-6 13:32:01
70000016_2 000090000000 2007-12-6 13:22:32
70000016_2 000070000999 2007-12-5 14:53:44
70000016_2 000070000999 2007-12-4 14:55:20
70000016_2 000070000008 2007-11-27 17:19:50
70000016_2 000070000008 2007-11-23 14:53:25
70000016_2 000070000008 2007-11-23 14:53:17
70000016_2 000070000006 2007-11-12 16:24:49
70000016_2 000070000006 2007-11-2 10:31:44
我现在想要的结果查询结果是这样的:
70000016_2 000090000000 2007-12-6 13:32:02
也就是从id相同的数据当中选择时间最大的一行数据。
请指教!!!!!!!!!!!
id NO date
70000016_2 000090000000 2007-12-6 13:32:02
70000016_2 000090000000 2007-12-6 13:32:01
70000016_2 000090000000 2007-12-6 13:22:32
70000016_2 000070000999 2007-12-5 14:53:44
70000016_2 000070000999 2007-12-4 14:55:20
70000016_2 000070000008 2007-11-27 17:19:50
70000016_2 000070000008 2007-11-23 14:53:25
70000016_2 000070000008 2007-11-23 14:53:17
70000016_2 000070000006 2007-11-12 16:24:49
70000016_2 000070000006 2007-11-2 10:31:44
我现在想要的结果查询结果是这样的:
70000016_2 000090000000 2007-12-6 13:32:02
也就是从id相同的数据当中选择时间最大的一行数据。
请指教!!!!!!!!!!!
select * from
(
select '70000016_2' tid,'000090000000' tno,'2007-12-06 13:32:02' tdate from dual union all
select '70000016_2','000090000000','2007-12-06 13:32:01' from dual union all
select '70000016_2','000090000000','2007-12-06 13:22:32' from dual union all
select '70000016_1','000070000999','2007-12-05 14:53:44' from dual union all
select '70000016_1','000070000999','2007-12-04 14:55:20' from dual union all
select '70000016_3','000070000008','2007-11-27 17:19:50' from dual union all
select '70000016_3','000070000008','2007-11-23 14:53:25' from dual union all
select '70000016_4','000070000008','2007-11-23 14:53:17' from dual union all
select '70000016_4','000070000006','2007-11-12 16:24:49' from dual union all
select '70000016_4','000070000006','2007-11-02 10:31:44' from dual
) t
where tdate = (select max(tdate) from (
select '70000016_2' tid,'000090000000' tno,'2007-12-06 13:32:02' tdate from dual union all
select '70000016_2','000090000000','2007-12-06 13:32:01' from dual union all
select '70000016_2','000090000000','2007-12-06 13:22:32' from dual union all
select '70000016_1','000070000999','2007-12-05 14:53:44' from dual union all
select '70000016_1','000070000999','2007-12-04 14:55:20' from dual union all
select '70000016_3','000070000008','2007-11-27 17:19:50' from dual union all
select '70000016_3','000070000008','2007-11-23 14:53:25' from dual union all
select '70000016_4','000070000008','2007-11-23 14:53:17' from dual union all
select '70000016_4','000070000006','2007-11-12 16:24:49' from dual union all
select '70000016_4','000070000006','2007-11-02 10:31:44' from dual
) t1 where tid = t.tid
)------------------------------------------------
TID TNO TDATE
1 70000016_2 000090000000 2007-12-06 13:32:02
2 70000016_1 000070000999 2007-12-05 14:53:44
3 70000016_3 000070000008 2007-11-27 17:19:50
4 70000016_4 000070000008 2007-11-23 14:53:17
我的意思是有个表A,数据如上,我想用一条SQL语句,选出每个id所对应的时间最大值,和所对应的NO的数据。
就是一条语句呀,那堆union all是我加的测试数据,我把ID分成了4块:取相同ID对应时间最大值的那条记录
用这个:
select * from yourtable t
where date = (select max(date) from yourtable where id = t.id)
SELECT ID, NO, DATE
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC) SEQ,
A.*
FROM A
)
WHERE SEQ = 1
[CODE]
SELECT ID, NO, DATE
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC) SEQ,
A.*
FROM A
)
WHERE SEQ = 1
[/CODE]
SELECT ID, NO, DATE
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC) SEQ,
A.*
FROM A
)
WHERE SEQ = 1
group by id,date) b,a
where a.id=b.r_id and
a.date=b.r_date;
FROM TAB A ,
(SELECT ID,MAX(DATE) DATE FROM TAB GRUOP BY ID) B
WHERE A.ID = B.ID AND A.DATE = B.DATE;
oracle官方定义的函数一般性能比较好
为啥大家都要整得那么复杂呢?
select id,no,max(date) over(partition by id) from 表名;
select a.id,a.no,c.date from t_TableName a
inner join
(
select b.id,max(b.date) as date from t_TableName b
group by b.id
)c on a.id = c.id