sql - How to get the accumulated sum of the children in an Hierarchical tree? -


table "salary"'s columns att_month, emp_id(points employee's emp_id), total.
table "organization" columns id, idparent, name.
table "employee"'s column emp_id, emp_name, dep_id (points organization's id).

the target calculate summary of every department, each month, according these 3 tables. wonder if can provide sql query or optimize mine?

my current query follows:

   select * (select yy.idparent, xx.id, name, sum(heji) total        (select connect_by_root(id) id, heji                   (select aa.id,                            aa.idparent,                            aa.name,                            nvl(sum(hj), 0) heji                           (select * organization) aa,                            (select att_month,                                    b.dep_id,                                    sum(total) hj                               salary a, employee b                              b.emp_id = a.emp_id                                , to_char(att_month, 'yyyy-mm') =                                    '2012-05'                              group att_month, b.dep_id) bb                      aa.id = bb.dep_id(+)                      group aa.id, aa.idparent, aa.name)                 connect prior id = idparent) xx,                organization yy          xx.id = yy.id          group yy.idparent, xx.id, name)     connect prior id = idparent      start id = '000';

but, it's long , need optimized. how?

    create table "employee"         (    "id" number(5,0),          "emp_id" varchar2(5) not null enable,          "password" varchar2(8) default 1,          "emp_name" varchar2(30),          "dep_id" varchar2(5),          "lev" char(1),          "sex" char(1),          "category" varchar2(20),          "edu" varchar2(8),          "birthday" date,          "gn" date,          "rj" date,          "pid" varchar2(18),          "lty" char(1),          "gwmc" varchar2(40),          "zj" varchar2(20),          "jszw" varchar2(20),          "jsjb" varchar2(20),          "gwgz" number(6,2),          "memo" varchar2(40),          "modilev" number(*,0) default 0,          "station" number(5,0),           constraint "pk_employee" primary key ("id")       using index pctfree 10 initrans 2 maxtrans 255 compute statistics        storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645       pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)       tablespace "jxkp_index"  enable,           constraint "uk_employee" unique ("emp_id")       using index pctfree 10 initrans 2 maxtrans 255 compute statistics        storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645       pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)       tablespace "jxkp_index"  enable        ) pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress logging       storage(initial 196608 next 1048576 minextents 1 maxextents 2147483645       pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)       tablespace "jxkp" ;        create unique index "pk_employee" on "employee" ("id")        pctfree 10 initrans 2 maxtrans 255 compute statistics        storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645       pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)       tablespace "jxkp_index" ;        create unique index "uk_employee" on "employee" ("emp_id")        pctfree 10 initrans 2 maxtrans 255 compute statistics        storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645       pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)       tablespace "jxkp_index" ;        alter table "employee" add constraint "pk_employee" primary key ("id")       using index pctfree 10 initrans 2 maxtrans 255 compute statistics        storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645       pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)       tablespace "jxkp_index"  enable;        alter table "employee" modify ("emp_id" not null enable);        alter table "employee" add constraint "uk_employee" unique ("emp_id")       using index pctfree 10 initrans 2 maxtrans 255 compute statistics        storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645       pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)       tablespace "jxkp_index"  enable;         create table "salary"         (    "emp_id" varchar2(5),          "att_month" date,          "jxgz" number(8,2),          "ycxjj" number(8,2),          "bzzjt" number(8,2),          "ybjt" number(8,2),          "wqjt" number(8,2),          "jbgz" number(8,2),          "qtj" number(8,2),          "bf" number(8,2),          "gskh" number(8,2),          "bmkh" number(8,2),          "total" number(8,2),          "memo" varchar2(80),          "checklock" varchar2(1) default '2',          "modilock" number(*,0) default 1,          "moditime" date,          "sgsff" varchar2(1) default 0,          "ct" number(8,2),          "rcjb" number(8,2),          "xjff" number(8,2),          "station" number(11,0),          "dep_id" varchar2(5),          "id" number(11,0),           constraint "pk_salary" primary key ("emp_id", "att_month")       using index pctfree 10 initrans 2 maxtrans 255 compute statistics        storage(initial 196608 next 1048576 minextents 1 maxextents 2147483645       pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)       tablespace "jxkp_index"  enable        ) pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress logging       storage(initial 327680 next 1048576 minextents 1 maxextents 2147483645       pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)       tablespace "jxkp" ;        create unique index "pk_salary" on "salary" ("emp_id", "att_month")        pctfree 10 initrans 2 maxtrans 255 compute statistics        storage(initial 196608 next 1048576 minextents 1 maxextents 2147483645       pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)       tablespace "jxkp_index" ;        alter table "salary" add constraint "pk_salary" primary key ("emp_id", "att_month")       using index pctfree 10 initrans 2 maxtrans 255 compute statistics        storage(initial 196608 next 1048576 minextents 1 maxextents 2147483645       pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)       tablespace "jxkp_index"  enable;         create table "organization"         (    "idparent" varchar2(5) not null enable,          "id" varchar2(5) not null enable,          "zjmc" varchar2(60),          "zjjs" varchar2(50),           constraint "pk_organization" primary key ("id")       using index pctfree 10 initrans 2 maxtrans 255 compute statistics        storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645       pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)       tablespace "jxkp_index"  enable        ) pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress logging       storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645       pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)       tablespace "jxkp" ;        create unique index "pk_organization" on "organization" ("id")        pctfree 10 initrans 2 maxtrans 255 compute statistics        storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645       pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)       tablespace "jxkp_index" ;        alter table "organization" add constraint "pk_organization" primary key ("id")       using index pctfree 10 initrans 2 maxtrans 255 compute statistics        storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645       pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)       tablespace "jxkp_index"  enable;        alter table "organization" modify ("idparent" not null enable);        alter table "organization" modify ("id" not null enable);

i tried simplify query, have @ please:

select      yy.idparent,      xx.root_id,      yy.name,      sum(xx.heji) total (     select          connect_by_root(t.id) root_id,         t.heji        (                 select                      dept.id,                     dept.idparent,                     dept.name,                     nvl(bb.hj, 0) heji                 organization dept left outer join                          (select                                  att_month,                                 b.dep_id,                                 sum(total) hj                                                            salary inner join employee b on b.emp_id = a.emp_id                                                            to_char(att_month, 'yyyy-mm') = '2012-05'                           group                                  att_month,                                  b.dep_id                          ) bb on dept.id = bb.dep_id             ) t      connect prior t.id = t.idparent     ) xx inner join organization yy on xx.root_id = yy.id group      yy.idparent,      xx.root_id,      yy.name;

Comments

Post a Comment

Popular posts from this blog

java - Play! framework 2.0: How to display multiple image? -

i need display gallery of photos. here template: @(photos: list[photo]) @title = { <bold>gallery</bold> } @main(title,"photo"){ <ul class="thumbnails"> @for(photo <- photos) { <li class="span3"> <a href="#" class="thumbnail"> <img src="@photo.path" alt=""> </a> </li> } </ul> } and here controller method: public static result getphotos() { return ok(views.html.photo.gallery.render(photo.get())); } and here photo bean : @entity public class photo extends model { @id public long id; @required public string label; public string path; public photo(string path, string label) { this.path = path; this.label = label; } private static finder<long, photo> find = new finder<long, photo>( long.class, photo.class); public static list<phot...
Read more

gmail - Is there any documentation for read-only access to the Google Contacts API? -

i want read-only access user's google contacts. there stackoverflow member wanted this, , received following answer : yes, there is, use https://www.googleapis.com/auth/contacts.readonly scope , "view contacts". this exchange reference read-only access google contacts can find using google. simply using access token obtained attempt read usual google contacts api (i.e. https://www.google.com/m8/feeds ) fails insufficient permissions. similarly, using google contacts api on new target fails 404 errors (for example https://www.googleapis.com/auth/contacts.readonly/default/full ). is there documentation anywhere on how use api, or permissible way access google contacts api full read-write access?
Read more

php - Controller/JToolBar not working in Joomla 2.5 -

relevant code: jtoolbarhelper::custom('savecategories', 'save', '', 'save', false, false); ... <input type="hidden" name="controller" value="easyblogcontroller"> according can dig docs , own previous questions, should call savecategories() function in easyblogcontroller . i've tried setting value easyblog , easyblog.php (file name) current easyblogcontroller (class name). clicking on save button refreshes page. not redirect, echo or var_dump test code put in savecategories() function. var_dump ing jrequest::getvar('controller') , 'task' return correct values. creating controller object , using $controller->execute('task'); works. the values going depend on location of controller trying call, , have couple options here. typically there controller in base folder of component (likely components/com_easyblog) in file called controller.php , class name inside ...
Read more