表1:table_res
id name ......//列名
one name1
two name2
three name3表2:table_valueid time value //列名
three 2013-09-16 10:55 33%
one 2013-09-16 10:56 22%
two 2013-09-16 09:55 35%
three 2013-09-16 11:53 43%
two 2013-09-16 10:38 33%
one 2013-09-16 12:55 17%
我想要的效果就是
id time value //列名
three 2013-09-16 11:53 43%
one 2013-09-16 12:55 17%
two 2013-09-16 10:38 33%
即id 不能重复,而且time为离现在时间最近的time,value 查出来。
求高手帮忙写个sql语句,谢谢了。
id name ......//列名
one name1
two name2
three name3表2:table_valueid time value //列名
three 2013-09-16 10:55 33%
one 2013-09-16 10:56 22%
two 2013-09-16 09:55 35%
three 2013-09-16 11:53 43%
two 2013-09-16 10:38 33%
one 2013-09-16 12:55 17%
我想要的效果就是
id time value //列名
three 2013-09-16 11:53 43%
one 2013-09-16 12:55 17%
two 2013-09-16 10:38 33%
即id 不能重复,而且time为离现在时间最近的time,value 查出来。
求高手帮忙写个sql语句,谢谢了。
解决方案 »
- Oracle语法问题
- oracle问题,急!~
- 关于存储过程算法效率的问题,高人请进。
- raise 使用问题
- 如何高效率的检索出:在40分钟内,住在同一旅馆并同一房间的数据(借用)
- 用DBLink时,出现错误ORA-12154: TNS: 无法解析指定的连接标识符
- 我总结的RedHat8.0 + Oracle9.0.2 + Proc(Pro*c)的环境设定以及Makefile的编写! 希望对大家有帮助!
- 求助!
- 用触发器中,我想得到当前被UpDate的行的rowid?应该如何实现???
- 查询优化
- 就一张表,两个字段,求一句SQL,在线等
- 【技术贴】把oracle从Solaris SPARC 迁移到 Linux x86-64
select 'three' id, '2013-09-16 10:55' time, '33%' value from dual union all
select 'one' id, '2013-09-16 10:56' time , '22%' value from dual union all
select 'two' id, '2013-09-16 09:55' time, '35%' value from dual union all
select 'three' id, '2013-09-16 11:53' time , '43%' value from dual union all
select 'two' id, '2013-09-16 10:38' time, '33%' value from dual union all
select 'one' id, '2013-09-16 12:55' time , '17%' value from dual
)
select id,max(time),max(value) from ax group by id不知道是不是你要的结果
(
select 'one' id ,'name1' name from dual union all
select 'two' id ,'name2' name from dual union all
select 'three' id ,'name3' name from dual
),table_value as
(
select 'three' id ,'2013-09-16 10:55' time,'33%' value from dual union all
select 'one' id ,'2013-09-16 10:56' time,'22%' value from dual union all
select 'two' id ,'2013-09-16 19:55' time,'35%' value from dual union all
select 'three' id ,'2013-09-16 11:53' time,'43%' value from dual union all
select 'two' id ,'2013-09-16 10:38' time,'33%' value from dual union all
select 'one' id ,'2013-09-16 12:55' time,'17%' value from dual
)select id,time,value
from
(
select a.id,b.time,b.value,
row_number() over(partition by a.id order by b.time desc) rn
from table_res a left join table_value b on a.id = b.id
)
where rn = 1
select id,max(time),max(value) from table_value group by id
那你这样吧:
with ax as(
select 'three' id, '2013-09-16 10:55' time, '33%' value from dual union all
select 'one' id, '2013-09-16 10:56' time , '22%' value from dual union all
select 'two' id, '2013-09-16 09:55' time, '35%' value from dual union all
select 'three' id, '2013-09-16 11:53' time , '43%' value from dual union all
select 'two' id, '2013-09-16 10:38' time, '33%' value from dual union all
select 'one' id, '2013-09-16 12:55' time , '17%' value from dual
)
select id,time,value from ax where id||time in(
select id||max(time) time from ax group by id
)
select id,time,value
from
(
select a.id,b.time,b.value,
row_number() over(partition by a.id order by b.time desc) rn
from table_res a
left join (select * from table_value where code='a') b on a.id = b.id
)
where rn = 1