Oracle Sql使用记录

Oracle当前时间 及转换

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') now,

    to_char(sysdate-interval '1' year, 'yyyy-mm-dd hh24') year1,   -- 1 year ago

    to_char(sysdate-10, 'yyyy-mm-dd hh24') day10,                       -- 10 day ago

    to_char(sysdate-interval '1' day, 'yyyy-mm-dd hh24') day1,

    to_char(sysdate-10 / 24, 'hh24:mi:ss') hour10,

    to_char(sysdate-interval '1' hour, 'hh24:mi:ss') hour1,

    to_char(sysdate-10 / (24*60), 'hh24:mi:ss') minute10,

    to_char(sysdate-interval '1' minute, 'hh24:mi:ss') minute1,

    to_char(sysdate-interval '1' second, 'hh24:mi:ss') second1

from dual;

 

---左连接

select t1.a_id, t1.a.name from tablea t1, tableb t2 where t1.a_id = t2.b_id(+);

 

--with as语句使用

with id2t as(

select distinct id, type from tableroot

),

id2n as(

select id, name from user

)

select a.id, id2n.name, id2t.type, money 

from tablemoney a, id2t, id2n

where a.id=id2t.id and a.id=id2n.id

;

 

--动态字段

with cols as

( select wm_concat('t.' || col_name) col

from ( select upper(column_name) col_name from all_tab_cols 

            where table_name = upper('p_a_users_v') and owner=upper('own_a') 

                        and column_name not in ('id', 'date')

) )

select 'select t.date, t.id, ' || cols.col || ' from p_a_users_v where date= to_date(''$flag'', ''mm-dd-yyyy'')  ' from cols;

--其中 || 符号是字符串连接符

--wm_concat函数可以将多个行连接为一个字符串并以逗号分隔

--字符串中出现单引号时，多一个单引号用于转义

 

-----------------------------------------------------------------

-- 字符串转行

with tmp as (    select 'name1, name2, name3, name4, id1, id2' colnamesstr from dual
),
cols as (    select regexp_substr(colnamesstr, '[^,]+', 1, rownum) colname from tmp 
    connect by rownum<length(colnamesstr) - length(regexp_replace( colnamesstr, ',', '' )) +2
)
select upper(substr(colname,1,1))  --首字母大写
    || lower(substr(colname, 2, length(colname)))  -- 除去首字母大写外其他小写
 from cols

 

--行转列===============================
Month    TID      AMOUNT
-----   ---  --------
1        1        1000
2        1        1500
3        1        2000
1        2        999
2        2        1200
3        2        2500
1        3        888
2        3        1800
3        3        2100
------------------------------
select * from (
    select month, tid, amount
    from all_sales
    where year = 2003
)
pivot(
    sum(amount) for month in (1 as JAN, 2 as FEB, 3 as MAR， 4 as APR)
)
order by tid;

-- 列转行

--------------------

with alldata as (
    select id, code1, code2, code3, name1, name2, name3,
        nvl( regexp_substr(codedesc, '[^,]+', 1, 1), 'Unknow') desc1,
        nvl( regexp_substr(codedesc, '[^,]+', 1, 2), 'Unknow') desc2,
        nvl( regexp_substr(codedesc, '[^,]+', 1, 3), 'Unknow') desc3
    from own.table1
)
select * from (
    select distinct id, code, name, codedesc
    from alldata unpivot( (code, codedesc, name) for dd in (
        (code1, desc1, name1), 
        (code2, desc2, name2), 
        (code3, desc3, name3), 
        )
    )
    where code is not null
)t order by code,name

-- pivot子句是Oracle database 11g

-----------------------------