*****************************************************
查询多个结果一起使用
SELECT X.a,F.b from (select (3-2) a from dual) X,(select (4-2) b FROM dual) F;
****************************************************************************
查询结果的表在与其他表使用
select * from (
with pro as (
SELECT distinct first_sequence
FROM parametric_measure
WHERE
measure_name = 'DATE' ),
bbb as (select b.measure_name 测试参数,b.description 测试内容
from pro p inner join parametric_measure b on (b.first_sequence = p.first_sequence and b.measure_name = 'HUMIDITY')
inner join parametric a on (a.handle = b.parametric_bo)
INNER JOIN RESRCE C ON(A.SITE = C.SITE AND SPLIT(A.PARA_CONTEXT_GBO,2) = C.RESRCE AND A.SITE = '1000') )
select * from bbb)
***********************************************
SQL中时间计算得到值
SELECT ROUND(TO_NUMBER( TO_DATE('2013-07-04 05:10:28','YYYY-MM-DD hh24:mi:ss') - TO_DATE('2013-07-04 02:58:28','YYYY-MM-DD hh24:mi:ss') ) * 24 ) SJ
from dual 得到小时
有两个日期数据START_DATE,END_DATE,欲得到这两个日期的时间差(以天,小时,分钟,秒,毫秒):
天:
ROUND(TO_NUMBER(END_DATE - START_DATE))
小时:
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)
分钟:
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)
秒:
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)
毫秒:
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 60)
<!--StartFragment -->
stmt varchar2(200);
begin
for tb in (select table_name from user_tables) loop
stmt:='alter table '||tb.table_name||' allocate extent';
execute immediate stmt;
end loop;
end;
/