oracle数据库中创建具有自增主键的表



1. 创建工单发起统计表：

   createtable cvms_start_cates_statistics

   (idnumber(15)primarykeynotnull,

    cate_id1varchar2(50),

   cate_id2varchar2(50),

    cate_id3varchar2(50),

   STATISTICS_DATEdate,

    order_totalnumber(15)

   )

    创建工单发起序列号

   create sequence cvms_start_cates_statistics_se

   minvalue1

   maxvalue999999999999

   startwith1

   incrementby1

   cache20;

   创建工单发起触发器

   createorreplacetrigger cvms_start_cates_statistics_tr

   beforeinserton cvms_start_cates_statistics

   foreachrow

   begin

    select cvms_start_cates_statistics_se.nextval into:new.idfrom dual;

    end;

2. 查询并统计每天的占比

   
   

   selectid,identify_num,order_num,round((identify_num/(casewhen order_num=0then1else order_numend))*100,2)||'%'from cvms_end_order_identify 

3. 从一个表中查询出来的数据复制到另一个表中：

    insertinto cvms_end_order_identify

       (identify_date, order_num, identify_num)

       select to_date(to_char(start_time,'yyyy-mm-dd'),'yyyy-mm-dd'),count(*),nvl(sum(casewhen result_count>0then 1else0end),5) ccfrom cvms_end_work_order

    groupby to_char(start_time,'yyyy-mm-dd')orderby to_char(start_time,'yyyy-mm-dd')desc

4. oracle中含有日期格式的插入：insertinto tb_start_ordervalues(null,'100171','100282','100289',to_date('2014-06-23 08:02:06','yyyy-mm-dd HH24:MI:SS'),'214','0.01%');

5. 带分页的查询语句：select *
     from (select d.*, rownum as rn
             from (select b.start_time, b.emp_code, b.order_text
                     from cvms_start_work_order_cates a, cvms_start_work_order b
                    where a.order_uuid = b.order_uuid
                      and 1 = 1
                      and cate_id3 = '100173'
                      and start_time >= to_date('2014-06-08 ', 'YYYY/MM/DD')
                      and start_time <= to_date('2014-06-20 ', 'YYYY/MM/DD')
                      and b.dept_code in ('N2221')
                    order by start_time desc
                  
                   ) d
            where rownum <= 10)
    where rn >= 1

6. oracle中查询两个相邻日期的数据:

        select * from (

       select version_name ,to_char(to_date(version_time,'yyyy-mm-dd HH24:MI:SS'),'yyyy-mm-dd HH24:MI:SS') aa,

      to_char(to_date(version_time,'yyyy-mm-dd HH24:MI:SS')+1,'yyyy-mm-dd HH24:MI:SS') bb

     from version  order by aa desc

     ) where rownum<2

    7.查询总的工单识别率：

     select sum(tt.cc)  ,sum(tt.bb) ,round((sum(tt.cc)/sum(tt.bb))*100,2)||'%' from(

select aa, '发起 ', bb, cc, cc/bb from (
select to_char(end_time,'YYYY-MM-DD') aa,sum(case when result_count>0 then 1 else 0 end) cc , count(*) bb  from CVMS_start_WORK_ORDER
where end_time>=to_date('2014-05-18','YYYY-MM-DD') and end_time<=to_date('2014-05-26','YYYY-MM-DD') and result_count>0
group by to_char(end_time,'YYYY-MM-DD')
)  ) tt

      8.分类统计：

     select cate_id1,cate_id2,cate_id3, cc,round((cc/sm)*100,2)||'%'   from(

  select cate_id1,cate_id2,cate_id3,count(*) as cc ,    ( select count(*)  from cvms_start_work_order  x , cvms_start_work_order_cates y
    where x.order_uuid=y.order_uuid  and x.start_time>= to_date('2014-05-15','YYYY-MM-DD') and x.end_time<= to_date('2014-05-22','YYYY-MM-DD') ) sm
  from cvms_start_work_order  a ,cvms_start_work_order_cates  b
  where a.order_uuid=b.order_uuid  and a.start_time>= to_date('2014-05-15','YYYY-MM-DD') and a.end_time<= to_date('2014-05-22','YYYY-MM-DD') 
  group by  cate_id1,cate_id2,cate_id3
  ）

  9.一个表中一个字段xulie,表名为tb_a,xulie字段的值就是0到9，可以重复，求出这些值不同的个数：