oracle视图、列转行、前几个月、相差秒数、自增主键

1、创建视图

CREATE OR REPLACE VIEW test
(
)
--只读模式
--with read only;
--selec * from mid_business_detail

列转行

--中台业务量
select * from 
(
select
 v.MIDDLEGROUND_BY AS midder,
    ty.business_byname AS processName,
    COUNT (1) as numbs
from 表
WHERE 1=1
GROUP BY
    v.MIDDLEGROUND_BY,
    ty.business_byname

) pivot (sum(numbs) for midder in (

'01147063', '01127374', 'admin', '01438487'
))
where processName = '退休-测试（请勿删）'
;

前几个月

--选择后六个月的日期
select to_char(sysdate+interval'6'month,'yyyy-mm-dd') from dual;
--去年1月1日
select trunc(add_months(sysdate,-12),'year') from dual

select to_char(trunc(add_months(sysdate,-12),'year'),'yyyyMM') from dual
--去年年份
select TO_CHAR (SYSDATE - INTERVAL '1' year,'yyyy') from dual;

select to_char(SYSDATE,'yyyyMM') as we from dual

--今年一月
select to_char(trunc(sysdate,'yyyy'),'yyyyMM') from dual;

--往前推6个月的年月
select 
TO_CHAR (SYSDATE - (INTERVAL '7' MONTH),'yyyyMM') from dual
--往前推12个月的年月
select to_char( add_months(sysdate,-13),'YYYYMM')  from dual;

相差秒数

select  s.startTime, s.ENDTIME, ceil((to_date(s.ENDTIME,'yyyy-mm-dd hh24:mi:ss') - to_date(s.startTime,'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60)
 from (select min(e.ACTION_DATE) as startTime, max(e.ACTION_DATE) as endTime
    from  T_SSC_M_ORDER_EXECUTION e
where e.ACTION_PLAN = '2'
     and e.ORDER_ITEM_ID = '201702150000000254') s;

自增主键

        SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') || 'A' || LPAD(SSC_B_BUSINESS_ACTION_SEQ.NEXTVAL, 9, 0) FROM DUAL