oracle9.0_sql_函数

最新推荐文章于 2021-04-12 02:26:42 发布

原创最新推荐文章于 2021-04-12 02:26:42 发布 · 492 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #sql #table #date #tree #数据库

oracle 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了Oracle SQL中一些实用的技巧，包括时间格式化、空值处理、日期格式转换、循环查询实现等，帮助读者更好地理解和操作Oracle数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1:取系统时间以及时间格式化
        select KT_ID, KT_NAME,KT_DATE from place.DIM_KQ_TYPE
                where KT_ID NOT IN(SELECT KT_ID FROM PLACE.MANA_KQ_INFO
                WHERE SEQ_TS_WK_NO=#SEQ_TS_WK_NO# AND Trunc(KI_DT,'dd') =Trunc(SYSDATE,'dd'))
                order by KT_DATE

1:
空值替换显示
    select nvl(VAR_VALUE, '无') AS VARVALUE ,
      VAR_ID AS VARID
      from dim_sys_var
2:
Date 列显示格式化
select to_char(kq_date,'yyyy-MM-dd')
    from mana_kq_info
    where to_char(kq_date,'yyyy-MM-dd') ='2008-11-01'

    sysdate 数据库时间(用于存取数据库存时间即处理服务器时间)

3:查询条件父子节点以下的子节点也都要
select node from basedba.dept_tree start with node=999999 connect by prior node=f_node
minus
select node from basedba.dept_tree start with node in(83,1091) connect by prior node=f_node

4. 删除重复记录
最高效的删除重复记录方法 ( 因为使用了ROWID)
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);

5 分组函数 max()
select MAX(STATE),table_name,col_name from dw.state

group by table_name,col_name;

eg: 错误使用
1:select MAX(STATE),table_name,col_name from dw.state

        报错：ORA-00937:not a single-group proup function

     2:select STATE,table_name,col_name from dw.state
       group by table_name,col_name;

报错：ORA-00979:not a GROUP BY expression

6 字段长度
select length('ND.QDJMK/ZG14(6001-6600,7001-7600,8001-8600,9001-9200)') from dual;

7 update table1 set ddh=substr(ddh,1,10);
alter table table1 modify ddh varchar2(10);

8 查找是否有子串
select * from dw.cab_plot_standard
where instr('123','1234')>0;

9.循环
SELECT DISTINCT (TO_CHAR(DT, 'yyyyMM')) MONTHNAME
             FROM (SELECT ADD_MONTHS(TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, -12), 'yyyymm'), 'yyyyMM'), ROWNUM - 1) DT
             FROM DUAL
          CONNECT BY ROWNUM <= 12)