SQL语句-递归查询、分组、统计
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
select sum(t.buildarea), count(t.personname), t.unitname
from hr_rup( t.unitname)
select sum(t.buildarea), t.unitname, t.personname
SQL语句——递归查询、分组、统计
1递归查询
1.1常规做法
select id, t.parentid, name from um_organization t where id=82;
select id, t.parentid, name from um_organization t where id=80;
order by t.unitname, t.personname
select grouping(t.unitname) as单位,grouping(t.personname) as个人,sum(t.buildarea), t.unitname, t.personname
from hr_returnsale t
select t.idno,count(idno) from hr_returnsale t group by idno having count(idno)>1
select t.personname, t.idno, count(idno)
from hr_returnsale t
group by idno
group by cube(t.unitname, t.personname)
order by t.unitname, t.personname
select grouping(t.unitname) as单位,decode(grouping(t.personname),1,'小计',t.personname) as个人,sum(t.buildarea), t.unitname, t.personname
start with id = 82
1.2.3查找子节点
select id,t.parentid,name from um_organization t connect by t.parentid= PRIOR id
start with id= 80
from hr_returnsale t
group by (t.unitname, t.personname)
select sum(t.buildarea), t.unitname, t.personname
from hr_returnsale t
group by cube(t.unitname, t.personname)
connect by id = PRIOR t.parentid
start with id = 82
1.2.2递归做法_层次
select id, t.parentid, name,LEVEL层次
from um_organization t
connect by id = PRIOR t.parentid
select * from (select t.idno,count(idno) ct from hr_returnsale t group by idno) a
where a.ct>1
select t.idno,count(idno) from hr_returnsale t group by idno where count(idno)>1
from hr_returnsale t
group by cube(t.unitname, t.personname)
order by t.unitname, t.personname
having count(idno) > 1
3统计
select sum(t.buildarea),t.unitname from hr_returnsale t group by t.unitname
select sum(t.buildarea),t.unitname from hr_returnsale t group by rollup( t.unitname)
select id, t.parentid, name from um_organization t where id=1;
1.2递归做法
1.2.1递归做法
select id, t.parentid, name
from um_organization t
1.2.4查找子节点_层次
select id,t.parentid,name,LEVEL层次from um_organization t connect by t.parentid= PRIOR id
start with id= 80
1.2.5生成树
select * from (select id, t.parentid, name, LEVEL层次
from um_organization t
connect by t.parentid = PRIOR id
start with id = 1
) where层次= 2
2分组
select t.idno,count(idno) from hr_returnsale t group by idno
from hr_rup( t.unitname)
select sum(t.buildarea), t.unitname, t.personname
SQL语句——递归查询、分组、统计
1递归查询
1.1常规做法
select id, t.parentid, name from um_organization t where id=82;
select id, t.parentid, name from um_organization t where id=80;
order by t.unitname, t.personname
select grouping(t.unitname) as单位,grouping(t.personname) as个人,sum(t.buildarea), t.unitname, t.personname
from hr_returnsale t
select t.idno,count(idno) from hr_returnsale t group by idno having count(idno)>1
select t.personname, t.idno, count(idno)
from hr_returnsale t
group by idno
group by cube(t.unitname, t.personname)
order by t.unitname, t.personname
select grouping(t.unitname) as单位,decode(grouping(t.personname),1,'小计',t.personname) as个人,sum(t.buildarea), t.unitname, t.personname
start with id = 82
1.2.3查找子节点
select id,t.parentid,name from um_organization t connect by t.parentid= PRIOR id
start with id= 80
from hr_returnsale t
group by (t.unitname, t.personname)
select sum(t.buildarea), t.unitname, t.personname
from hr_returnsale t
group by cube(t.unitname, t.personname)
connect by id = PRIOR t.parentid
start with id = 82
1.2.2递归做法_层次
select id, t.parentid, name,LEVEL层次
from um_organization t
connect by id = PRIOR t.parentid
select * from (select t.idno,count(idno) ct from hr_returnsale t group by idno) a
where a.ct>1
select t.idno,count(idno) from hr_returnsale t group by idno where count(idno)>1
from hr_returnsale t
group by cube(t.unitname, t.personname)
order by t.unitname, t.personname
having count(idno) > 1
3统计
select sum(t.buildarea),t.unitname from hr_returnsale t group by t.unitname
select sum(t.buildarea),t.unitname from hr_returnsale t group by rollup( t.unitname)
select id, t.parentid, name from um_organization t where id=1;
1.2递归做法
1.2.1递归做法
select id, t.parentid, name
from um_organization t
1.2.4查找子节点_层次
select id,t.parentid,name,LEVEL层次from um_organization t connect by t.parentid= PRIOR id
start with id= 80
1.2.5生成树
select * from (select id, t.parentid, name, LEVEL层次
from um_organization t
connect by t.parentid = PRIOR id
start with id = 1
) where层次= 2
2分组
select t.idno,count(idno) from hr_returnsale t group by idno