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

时间：2020-12-20 13:52:45


  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，可以重复，求出这些值不同的个数：

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

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


标签：

相关文章