亚洲欧美日韩熟女|做爱高潮视频网址|国产一区二区三级片|国产Av中文字幕www.性色av|亚洲婷婷永久免费|国产高清中文字幕|欧美变态网站久re视频精品|人妻AV鲁丝第一页|天堂AV一区二区在线观看|综合 91在线精品

oracle_sql遞歸查詢積累

2023-04-12


一、start with.....connect by遞歸查詢



建表語句： 


CREATE TABLE D_ZONECODE
(
	ID VARCHAR2(36) NOT NULL UNIQUE,
	ZONECODE VARCHAR2(6) NOT NULL,
	SUPERCODE VARCHAR2(6) NOT NULL,
	ZONELLEVEL VARCHAR2(2) NOT NULL,
	ZONENAME VARCHAR2(60) NOT NULL
);

插入數(shù)據(jù)語句：


INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('1', '370000', '000000', '01', '山東省');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('2', '370100', '370000', '02', '濟南市');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('3', '370102', '370100', '03', '歷下區(qū)');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('4', '370103', '370100', '03', '市中區(qū)');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('5', '370104', '370100', '03', '槐蔭區(qū)');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('6', '370105', '370100', '03', '天橋區(qū)');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('7', '370112', '370100', '03', '歷城區(qū)');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('8', '370113', '370100', '03', '長清區(qū)');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('9', '370114', '370100', '03', '高新區(qū)');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('10', '370124', '370100', '03', '平陰縣');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('11', '370125', '370100', '03', '濟陽縣');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('12', '370126', '370100', '03', '商河縣');
INSERT INTO D_ZONECODE (ID, ZONECODE, SUPERCODE, ZONELLEVEL, ZONENAME) VALUES ('13', '370181', '370100', '03', '章丘市');

root向樹末梢查詢：


select * from d_zonecode
 start with id=’370000’
 connect by prior zonecode = supercode;

樹末梢向ROOT查詢：


select * from d_zonecode
 start with zonecode = '370100'
 connect by prior supercode = zonecode;


附：


start with指明從哪里開始遍歷樹


connect by 就是指明父子關系,注重PRIOR位置


CONNECT_BY_ROOT: 提供獲取根節(jié)點記錄的字段信息。


二、今天客戶突然要一個表格，這里面包括兩個表的總分關系，比如我要顯示部門同時和該部門下的所有人員用一條記錄顯示，在網(wǎng)上搜到一個例子，記錄下方便使用。






建表語句：


/* Create Tables */
CREATE TABLE DEPT
(
	ID VARCHAR2(36) NOT NULL UNIQUE,
	DEPTID VARCHAR2(4) NOT NULL UNIQUE,
	DEPTNAME VARCHAR2(60) NOT NULL
);
CREATE TABLE D_USER
(
	ID VARCHAR2(36) NOT NULL UNIQUE,
	USERID VARCHAR2(6) NOT NULL UNIQUE,
	USERNAME VARCHAR2(60) NOT NULL,
	DEPTID VARCHAR2(4) NOT NULL
);

插入數(shù)據(jù)語句：


-- 部門表
INSERT INTO dept (id, deptid, deptname) VALUES ('1', '0001', '市場部');
INSERT INTO dept (id, deptid, deptname) VALUES ('2', '0002', '開發(fā)部');
INSERT INTO dept (id, deptid, deptname) VALUES ('3', '0003', '項目部');
-- 用戶表
INSERT INTO D_USER (id, userid, username, deptid) VALUES ('1', '100001', '張肅寧', '0001');
INSERT INTO D_USER (id, userid, username, deptid) VALUES ('2', '100002', '王濟南', '0002');
INSERT INTO D_USER (id, userid, username, deptid) VALUES ('3', '100003', '趙臨沂', '0001');
INSERT INTO D_USER (id, userid, username, deptid) VALUES ('4', '100004', '金淄博', '0003');
INSERT INTO D_USER (id, userid, username, deptid) VALUES ('5', '100005', '李德州', '0002');
INSERT INTO D_USER (id, userid, username, deptid) VALUES ('6', '100006', '周濟寧', '0001');
INSERT INTO D_USER (id, userid, username, deptid) VALUES ('7', '100007', '姜濰坊', '0003');
INSERT INTO D_USER (id, userid, username, deptid) VALUES ('8', '100008', '萬青島', '0001');

查詢：


select * from dept t;
select * from d_user t;





測試sql如下：


select username from (
select row_number() over(order by lv desc) id,username,deptid from (
select level lv, replace(sys_connect_by_path(username,','),',',',') username,deptid from(
select deptid,username,row_number() over(order by username) id from
(
select a.deptid,a.deptname,b.username
from dept a,d_user b
where a.deptid = b.deptid
)
) connect by prior id = id-1 )) where id = 1;



要是需要和部門連接查詢，我采用建立一個方法，然后查詢部門記錄時調(diào)用該方法，傳部門id這個參數(shù)進去。



--方法建立：
create or replace function getUsername(oc_deptid in varchar2
                                  ) return varchar2 is
  oc_result varchar(300);
  oc_username varchar2(300); -- 取值
begin
--查詢用戶名稱
  select username into oc_result from (
select row_number() over(order by lv desc) id,username,deptid from (
select level lv, replace(sys_connect_by_path(username,','),',',',') username,deptid from(
select deptid,username,row_number() over(order by username) id from
(
select a.deptid,a.deptname,b.username
from dept a,d_user b
where a.deptid = b.deptid and a.deptid = oc_deptid
)
) connect by prior id = id-1 )) where id = 1;
  oc_username:=oc_result;
  if oc_result is NULL then
     oc_username := '';
  else
     oc_username := SUBSTR(oc_result,INSTR(oc_result,',')+1,LENGTH(oc_result)-1);
  end if;
  return oc_username;
end;

查詢sql:


select t.deptid,t.deptname,getUsername(t.deptid) as username from dept t;



附（CONNECT_BY_ROOT使用）： 


select zonecode,zonename,CONNECT_BY_ROOT(zonecode) as root_code  from d_zonecode
start with zonecode = '370100'
connect by prior zonecode = supercode and zonecode != supercode;
select zonecode,CONNECT_BY_ROOT(zonecode) as root_code from d_zonecode
start with zonecode = '370114'
connect by prior supercode = zonecode and zonecode != supercode;
select CONNECT_BY_ROOT(t.zonecode) as ROOT from d_zonecode t
where t.zonecode = '370100'
start with t.zonecode = t.supercode
connect by prior t.zonecode = t.supercode and t.zonecode != t.supercode;





本文僅代表作者觀點，版權歸原創(chuàng)者所有，如需轉(zhuǎn)載請在文中注明來源及作者名字。

免責聲明：本文系轉(zhuǎn)載編輯文章，僅作分享之用。如分享內(nèi)容、圖片侵犯到您的版權或非授權發(fā)布，請及時與我們聯(lián)系進行審核處理或刪除，您可以發(fā)送材料至郵箱：service@tojoy.com