SELECT CITY_ID,COUNT(PSN_ID) FROM (SELECT TEMP_ORG.ORG_ID CITY_ID,TEMP_PSN.PSN_ID FROM TEMP_ORG,TEMP_PSN WHERE TEMP_PSN.ORG_ID = TEMP_ORG.ORG_ID AND TEMP_ORG.ORG_LEVEL = 20 UNION SELECT TEMP_ORG.PARENT_ORG_ID CITY_ID,TEMP_PSN.PSN_ID FROM TEMP_ORG,TEMP_PSN WHERE TEMP_PSN.ORG_ID = TEMP_ORG.ORG_ID AND TEMP_ORG.ORG_LEVEL = 30 ) CITY_COUNT GROUP BY CITY_COUNT.CITY_ID
属性统计select parent_org_id,org_id,sum(1) as counts from (select * from table_name start with org_id= '0' connect by org_id= prior parent_org_id ) a group by rollup(parent_org_id,org_id)
to: zw_yu(鱼猫) start with org_id= '0' 这里不就限制了父机构的id了吗? 比如这里的父机构是市级,我是要按各个市分组的 我的sql大概是这样的 select sum(a.charge),b.org_name from serv a,org b where a.org_id=b.org_id group by b.org_name 这样写出来的语句会把市和县分开群组,但我现在希望把各县归并到对应的市里. 怎么做好?而且语句要尽量简短.panppl(南易楼) 的办法确实可以实现,但如果级别再多些语句就会太长了.
create function get_num(p_org_id in varchar2) return number as num number; begin select count(1) into num from tabname start with org_id=p_org_id connect by org_id= prior parent_org_id; return num; end; / select org_id,get_num(org_id) from tabname where org_level='20' group by org_id;
FROM
(SELECT TEMP_ORG.ORG_ID CITY_ID,TEMP_PSN.PSN_ID FROM TEMP_ORG,TEMP_PSN
WHERE TEMP_PSN.ORG_ID = TEMP_ORG.ORG_ID
AND TEMP_ORG.ORG_LEVEL = 20
UNION
SELECT TEMP_ORG.PARENT_ORG_ID CITY_ID,TEMP_PSN.PSN_ID FROM TEMP_ORG,TEMP_PSN
WHERE TEMP_PSN.ORG_ID = TEMP_ORG.ORG_ID
AND TEMP_ORG.ORG_LEVEL = 30
) CITY_COUNT
GROUP BY CITY_COUNT.CITY_ID
from (select * from table_name
start with org_id= '0'
connect by org_id= prior parent_org_id
) a
group by rollup(parent_org_id,org_id)
start with org_id= '0' 这里不就限制了父机构的id了吗?
比如这里的父机构是市级,我是要按各个市分组的
我的sql大概是这样的
select sum(a.charge),b.org_name from serv a,org b
where a.org_id=b.org_id
group by b.org_name
这样写出来的语句会把市和县分开群组,但我现在希望把各县归并到对应的市里.
怎么做好?而且语句要尽量简短.panppl(南易楼) 的办法确实可以实现,但如果级别再多些语句就会太长了.
实在没有的话我明天上午结贴了.
return number
as
num number;
begin
select count(1) into num from tabname start with org_id=p_org_id connect by org_id= prior parent_org_id;
return num;
end;
/
select org_id,get_num(org_id) from tabname where org_level='20' group by org_id;