orcale学习中的几个sql

------查询年月的sql

select to_number(to_char(sysdate, 'YYYY') ||to_char(sysdate, 'MM')||to_char(sysdate,'DD')) from dual;

----一条功能为：去除重复行，重复行聚合，表连接的sql 

select * from
(
  select distinct fillcard.fillcard_seq,--不去掉重复列
  sum(fillcard.fillcard_balance)-----重复行的累加总和
  from fillcard group by fillcard.fillcard_seq
),
(select * from fillcard_batch)
 where fillcard_seq=filecard_batch_no

-----------------------树形结构-------------------

Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。

创建示例表：

CREATE TABLE TBL_TEST
(
  ID    NUMBER,
  NAME  VARCHAR2(100 BYTE),
  PID   NUMBER                                  DEFAULT 0
);

 

插入测试数据：

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

从Root往树末梢递归

select * from TBL_TEST
 start with id=1
 connect by prior id = pid

 

从末梢往树ROOT递归

select * from TBL_TEST
 start with id=5
 connect by prior pid = id