分层查询
来源:互联网 发布:网络共享文件 编辑:程序博客网 时间:2024/04/27 19:58
1.表示父子关系
select a.ename || ' works for ' || b.ename as emps_and_mgrs from emp a,emp b
where a.mgr = b.empno
2.表示子-父-祖关系
select ltrim(sys_connect_by_path(ename,'--->'),'--->') leaf_branch_root
from emp where level = 3 start with ename = 'MILLER'
connect by prior mgr = empno
3.创建表的分层视图
select ltrim(sys_connect_by_path(ename,'--->'),'--->') emp_tree from emp
start with mgr is null
connect by prior empno = mgr
order by 1
4.为给定父行找到所有字行
select ename from emp start with ename = 'JONES'
connect by prior empno = mgr
5.确认哪些行是叶节点,分支节点和根节点(?)
select ename,connect_by_isleaf is_leaf,
(select count(*) from emp e where e.mgr = emp.empno
and e.mgr is not null
and rownum = 1) is_branch,
decode(ename,connect_by_root(ename),1,0) is_root
from emp
start with mgr is null
connect by prior empno = mgr
order by 4 desc,3 desc
6.
WITH a AS(
SELECT '2010-1-1' rq,'XXX' yz,'YYY' mbz FROM dual
UNION ALL
SELECT '2010-5-1' ,'YYY' ,'ZZZ' FROM dual
UNION ALL
SELECT '2010-1-1' ,'AAA' ,'BBB' FROM dual
)
SELECT CONNECT_BY_ROOT(yz),mbz,yz FROM a
WHERE CONNECT_by_isleaf=1 ---判断是否是叶子节点
START with NOT EXISTS(select 1 from a b where b.mbz=a.yz) ---判断?
CONNECT BY PRIOR mbz=yz
7.
有两个表,一个是类型表,简称a ,一个是数据表,简称b,
a表的表结构如下:
id name pid
1 产品 0
2 饮料 1
3 食品 1
4 可乐 2
5 矿泉水 2
6 橙汁 2
7 牛肉 3
8 饼干 3
9 挂面 3
b表的数据如下:
id num(销售量)
4 10
4 5
5 2
6 7
7 10
现在想通过sql得到这种结果
id name num
1 产品 34
2 饮料 24
4 可乐 15
5 矿泉水 2
6 橙汁 7
3 食品 10
7 牛肉 10
即,分类别进行逐级汇总,该怎么写呢?
with a as(
select 1 id,'产品' name,0 pid from dual
union all
select 2 id,'饮料' name,1 pid from dual
union all
select 3 id,'食品' name,1 pid from dual
union all
select 4 id,'可乐' name,2 pid from dual
union all
select 5 id,'矿泉水' name,2 pid from dual
union all
select 6 id,'橙汁' name,2 pid from dual
union all
select 7 id,'牛肉' name,3 pid from dual
union all
select 8 id,'饼干' name,3 pid from dual
union all
select 9 id,'挂面' name,3 pid from dual
)
,b as(
select 4 id,10 num from dual
union all
select 4 id,5 num from dual
union all
select 5 id,2 num from dual
union all
select 6 id,7 num from dual
union all
select 7 id,10 num from dual
)
,c as (
select level lv from dual connect by level < 6
)
select id,name,sumbytype from(
select id,name,kk,sum(tol) over(partition by kk order by kk) sumbytype,row_number() over(partition by kk order by id) rn from(
select distinct id,name,tol,tier,substr(tier,0,lv) kk from(
select id,name,pid,nvl(tol,0) tol,replace(ltrim(sys_connect_by_path(id,'-->'),'-->'),'-->',null) tier from (
select a.id,name,pid,sum(num) tol from a,b where a.id = b.id(+) group by a.id,name,pid order by a.id
) t start with pid=0 connect by prior t.id=t.pid
),c
) order by id
) where rn = 1 and sumbytype != 0
--group by substr(tier,0,lv)
1 1 产品 34
2 2 饮料 24
3 3 食品 10
4 4 可乐 15
5 5 矿泉水 2
6 6 橙汁 7
7 7 牛肉 10
8.
with temp as(
select '01' code,'江苏省' name,'0' superitemid,1 levelno from dual
union all
select '0101' code,'常州市' name,'01' superitemid,2 levelno from dual
union all
select '0102' code,'南京市' name,'01' superitemid,2 levelno from dual
union all
select '010101' code,'武进区' name,'0101' superitemid,3 levelno from dual
union all
select '02' code,'山东省' name,'0' superitemid,1 levelno from dual
union all
select '0201' code,'济南市' name,'02' superitemid,2 levelno from dual
union all
select '0202' code,'青岛市' name,'02' superitemid,2 levelno from dual
union all
select '0203' code,'淄博市' name,'02' superitemid,2 levelno from dual
union all
select '020101' code,'历城区' name,'0201' superitemid,3 levelno from dual
)
SELECT code itemid,connect_by_root(code) code1,
connect_by_root(NAME) name1,
Decode(levelno,2,code,3,superitemid,NULL) code2,
Decode(levelno,1,NULL,3,(SELECT NAME FROM temp WHERE code=a.superitemid),name) name2,
Decode(levelno,3,code,NULL) code3,
Decode(levelno,3,NAME,NULL) name3
FROM temp a
START WITH superitemid='0'
CONNECT BY PRIOR code = superitemid
- 分层查询
- oracle分层查询
- 分层开发 单元测试查询
- 分层查询(Hierarchical Queries)
- 分层查询(Hierarachical Queries)(翻译)
- 分层
- 分层
- 分层
- 分层
- 分层
- 分层
- 分层
- 分层
- 分层
- 分层架构的模糊查询问题笨方法
- 分层查询中的start with和connect by怎么用
- oracle分层查询中的start with和connect by(树结构查询)
- 基于EF(Entity Framework)的分层系统中如何传递查询的结果集
- ava中yield(),sleep()以及wait()的区别
- flex中navigateToURL函数的应用
- ORA-26663
- VC编程规范[转载]
- 《BREW进阶与精通——3G移动增值业务的运营、定制与开发》连载之56---BREW SDK 个版本的区别(下)
- 分层查询
- Java Thread.interrupt 中断JAVA线程
- java中hashcode()和equals()的详解
- 面试中你必须要知道的语言陷阱(很好,怕再找不到了)
- 性能测试之细节决定成败
- 正则表达式基础精简篇之二 (匹配多个字符)
- 前序,中序,后续非递归遍历树的标准算法
- Reflections on the mainstream culture (3)
- XP系统 安装office 2007弹出找不到office.zh-cn文件夹中的文件 的问题