Isn't there a better way?
От | Josh Berkus |
---|---|
Тема | Isn't there a better way? |
Дата | |
Msg-id | 200206131241.10909.josh@agliodbs.com обсуждение исходный текст |
Список | pgsql-sql |
Folks, Given the following tables: --DROP TABLE teams_desc; create table teams_desc (teams_id INT4 NOT NULL DEFAULT NEXTVAL('users_user_id_sq') PRIMARY KEY,teams_name VARCHAR(75) NOTNULL,teams_code VARCHAR(20) NOT NULL,notes TEXT NULL ); --drop table teams_tree; create table teams_tree (teams_id INT4 NOT NULL REFERENCES teams_desc(teams_id) ON DELETE CASCADE,treeno INT4 NOT NULL,constraintpk_teams_tree PRIMARY KEY (teams_id, treeno)); --drop table teams_users; create table teams_users (teams_id INT4 NOT NULL REFERENCES teams_desc(teams_id) ON DELETE CASCADE,user_id INT4 NOT NULLREFERENCES users(user_id) ON DELETE CASCADE,leader BOOLEAN NOT NULL DEFAULT FALSE,constraint teams_users_pk PRIMARY KEY( teams_id, user_id ) ); drop view teams; create view teams as select teams_id, teams_name, teams_code, notes,min(treeno) as lnode, max(treeno) as rnode from teams_desc JOIN teams_tree USING (teams_id) group by teams_id, teams_name, teams_code, notes; I need to construct a query that will delete all duplicate users within a tree barnch, leaving only the user references which are "lowest" on the tree. The best I've been able to come up with is: v_left := current branch left node v_right := current branch right node DELETE FROM teams_users WHERE EXISTS (SELECT teams.team_id FROM teams JOIN teams_users tu2 USING (team_id)WHERE EXISTS (SELECT MAX(tm.lnode), MIN(tm.lnode), user_id FROM teams_userstu JOIN teams tm USING (team_id) WHERE ((tm.lnode > v_left and tm.rnode < v_right) OR (tm.lnode < v_leftAND tm.rnode > v_right)) GROUP BY user_id HAVING MIN(tm.lnode) < MAX(tm.lnode) AND tu.user_id = tu2.user_id AND MAX(tm.lnode) > teams.lnode)AND teams_users.team_id = tu2.team_id and teams_users.user_id = tu2.user_id); But that's a nested WHERE EXISTS clause, with an aggregate referenceing the same aggregated view twice. It seems like there must be a more efficient way to build this query, but I can't think of one. Suggestions? -Josh Berkus P.S. This is based on Joe Celko's Linear Nested Model of tree construction.
В списке pgsql-sql по дате отправления: