-- Create table
create table CUS_SECTION
(
SECTION_ID NUMBER,
SECTION_NAME VARCHAR2(10),
WORK_TIME NUMBER,
UPDATE_USERID VARCHAR2(10),
UPDATE_DATE DATE default sysdate,
SECTION_DESC VARCHAR2(20),
SECTION_DEMO NUMBER,
SEQ NUMBER,
SHIFT_ID NUMBER,
SECTION_SEQ NUMBER
)
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss')now_date,
to_char(sysdate+(30/24/60),'yyyy-mm-dd hh:mi:ss') new_date from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss')now_date,
to_char(sysdate-(30/24/60),'yyyy-mm-dd hh:mi:ss') new_date from dual;
select add_months(sysdate,-4) from dual;
select months_between(sysdate,to_date('2008-01-01 02:00:00','yyyy-mm-dd hh:mi:ss')) result from dual;
select add_months(sysdate,2*12) two_years_later from dual;
select last_day(sysdate) last_day,last_day(sysdate)+1 fisrt_day from dual;
select next_day(sysdate,4) from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') now_date,
to_char(round(sysdate),'yyyy-mm-dd hh:mi:ss') round_date,
to_char(trunc(sysdate),'yyyy-mm-dd hh:mi:ss') trunc_date
from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') now_date,
to_char(round(sysdate,'hh24'),'yyyy-mm-dd hh:mi:ss') round_date,
to_char(trunc(sysdate,'hh24'),'yyyy-mm-dd hh:mi:ss') trunc_date
from dual;
select section_id,section_name,shift_id,
sum(work_time) section_times,
sum(sum(work_time)) over (partition by shift_id) shift_times
from cus_section
group by section_id,section_name,shift_id
select section_id,section_name,shift_id,
sum(work_time) section_times,
sum(sum(work_time)) over (partition by shift_id) shift_times,
sum(sum(work_time)) over () all_times
from cus_section
group by section_id,section_name,shift_id
select section_id,section_name,
sum(work_time) section_times,
rank() over(order by sum(work_time) desc) rank,
dense_rank() over(order by sum(work_time) desc) dense_rank,
row_number() over(order by sum(work_time) desc) row_number
from cus_section
group by section_id,section_name
select section_id,section_name,
sum(work_time) section_times,
rank() over(partition by shift_id order by sum(work_time) desc nulls last) rank,
dense_rank() over(partition by shift_id order by sum(work_time) desc) dense_rank,
row_number() over(partition by shift_id order by sum(work_time) desc) row_number
from cus_section
group by section_id,section_name,shift_id
select min(section_id) keep(dense_rank first order by sum(work_time) desc) first,
min(section_id) keep(dense_rank last order by sum(work_time) desc) last
from cus_section
group by section_id
select section_id,section_name,shift_id,
ntile(5) over (order by shift_id) shift_times
from cus_section
group by section_id,section_name,shift_id
select section_id,section_name,
sum(work_time) section_times,
sum(sum(work_time)) over (order by section_id rows between unbounded preceding and unbounded following) sum_shift_times
from cus_section
group by section_id,section_name,shift_id
select section_id,section_name,
sum(work_time) section_times,
sum(sum(work_time)) over (order by section_id rows between 1 preceding and unbounded following) sum_shift_times
from cus_section
group by section_id,section_name,shift_id
select section_id,section_name,
sum(work_time) section_times,
sum(sum(work_time)) over (order by section_id rows between unbounded preceding and current row) sum_shift_times,
avg(sum(work_time)) over (order by section_id rows between unbounded preceding and current row) avg_shift_times
from cus_section
group by section_id,section_name,shift_id
select trunc(update_date) day,
sum(work_time) section_times,
avg(sum(work_time)) over(order by trunc(update_date) range between interval '2' day preceding
and interval '2' day following) five_shift_times
from cus_section
group by update_date
select section_id,section_name,shift_id,
sum(work_time) section_times,
sum(sum(work_time)) over (partition by shift_id) shift_times ,
round(ratio_to_report(sum(work_time)) over (partition by shift_id),2) shift_present
from cus_section
group by section_id,section_name,shift_id
select * from sys_bom
where bom_id=1000000135;
select * from sys_bom
where item_part_id=1000000135
select distinct level,bom_id,item_part_id from sys_bom
start with bom_id=1000000135 connect by prior bom_id=item_part_id order by bom_id;
select distinct level,bom_id,item_part_id from sys_bom
start with bom_id=1000000135 connect by bom_id=prior item_part_id order by level
select count(distinct level) from sys_bom
start with bom_id=1000000135 connect by bom_id=prior item_part_id
select level,count(level) from sys_bom
start with bom_id=1000000135 connect by bom_id=prior item_part_id
group by level
select level,bom_id,item_part_id from sys_bom where level=2
start with bom_id=1000000135 connect by bom_id=prior item_part_id
select level,bom_id,item_part_id from sys_bom
start with bom_id=1000000135 connect by bom_id=prior item_part_id
代码
