解决方案 »
- 数据库建表出错
- 新人求教11g监听程序问题
- 从sql server2000数据库导入到oracle服务器~~出错误 ?
- 问一个查询的问题
- oracle 外部表问题
- 谁有ORACLE9I(OS:WIN2000 SERVER)的initSID.ora文件,我用create pfile from spfile生成了一个,好像不是很好,问题解决即结账!
- 关于PHP来编译函数/过程的问题...
- Oracle9建临时表不在Temp表空间中,而在CWMLITE中?
- 急:如何让sqlload使用命令行方式,把数据库中的指定几个表导出?(在线)
- sqlldr error 605错误。--在线等待
- 菜鸟请教一个plsql写法
- to_date 转换的时候出错
select uuid ,
c_id ,
c_time,
,a , b , c ,
row_number() over(partition by c_id,to_char(c_time,'yyyy-mm-dd hh24') order by c_time desc) rn
from c_data_01
where c_id = '0001'
) t where t.rn = 1
改一下就是随机
select *
from (select uuid,
c_id,
c_time,,
a,
b,
c,
row_number() over(partition by c_id, to_char(c_time, 'yyyy-mm-dd hh24') order by dbms_random.value desc) rn
from c_data_01
where c_id = '0001') t
where t.rn = 1
目前测出order by c_time 比 order by dbms_random.value 效率高很多感谢两位。
insert into p_data
select c_id, c_time, a, b, c
from (select c_id,
c_time,
a,
b,
c,
row_number() over(partition by c_id,trunc(c_time,'hh24') order by c_time) as rn
from c_data_01
where c_time >=
to_date('2013-3-6 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and c_time <
to_date('2013-3-7 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
where rn = 1;/* 效率次之*/
insert into p_data
select a1.c_id,a1.c_time,a1.a,a1.b,a1.c
from c_data_01 a1,
(select c_id, max(c_time) c_time
from c_data_01
where c_time >=
to_date('2013-3-6 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and c_time <
to_date('2013-3-7 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
group by c_id) a2
where a1.c_id = a2.c_id
and a1.c_time = a2.c_time;/*不知为什么,这个巨慢*/
insert into p_data
select c_id, c_time, a, b, c
from (select c_id,
c_time,
a,
b,
c,
row_number() over(partition by c_id order by dbms_random.value) as rn
from c_data_01
where c_time >=
to_date('2013-3-6 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and c_time <
to_date('2013-3-7 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
where rn = 1;