SQL工作笔记

1.删除重复记录

DELETE FROM fbs_bank_account E
 WHERE E.ROWID > (SELECT MIN(X.ROWID)
                    FROM fbs_bank_account X
                   WHERE X.Emp_Num = E.Emp_Num);

2.查询同一订单时间大的状态
SELECT t.company,t.consumenotes,t.credit,t.status,t.consumetype, to_char(t.operattime, 'yyyy-mm-dd hh24:mi:ss') operattime
  FROM fbs_credit_statement_account t
 WHERE (t.orderid, t.operattime) IN
       (SELECT orderid, MAX(operattime)
          FROM fbs_credit_statement_account
         where orderid = 10000175940003
         GROUP BY orderid)

3.汇总总表积分数据
update fbs_credit_total ct
 set ct.credittotal = (select sum(tt.realcredit)
                           from fbs_credit_middlen tt
                          where tt.ispass = 1
                            and ct.account = tt.account
                          group by tt.account);
4.函数
to_char(sc.appjtcredit,'$9,999.99')格式化字符串
RTRIM(str)  去掉str尾部的空格
LTRIM(str) 去掉str前面部分的空格
trim(str)   去掉str前后的空格
mod(m,n)            求余
NULLIF(a,b)如果a=b 返回null，如果不等返回a
COALESEC(a,b,c,...)返回第一个不为null的值当都为null时返回null

5.层级数据结构查询

树形表结构
create table tm_trees(
Id  number;
Name varchar2;
parentId
)
--从最大级别按层级查询
select *
from tm_trees
start with parentId = 1
connect by prior id = parentId
--从最大级别查询链接父级名字
Select a.id,
'1' || SYS_CONNECT_BY_PATH(a.name, '->') || '.' path  --通过SYS_CONNECT_BY_PATH函数根据指定连接符号将指定字段连接
From tm_trees a
Start With a.parentId = 1
Connect By Prior a.id = a.parentId;