digoal
2011-04-27
PostgreSQL , 递归查询 , group by , order by , cursor , 性能优化 , with recursive
一位同事有一个这样的需求，不知道怎么写SQL ，
两张表如下
table1
id1 int fk reference (table2.id)
id2 int fk reference (table2.id)
table2
id PK
name
需要查询如下结果 :
t1.id1, t1.id2, id1_name, id2_name where id1=? and id2=?
从结果上来看，table1和table2需要关联两次才能匹配到id1对应的name和id2对应的name
这里使用了WITH来实现要查询的结果 :
with
a1 as (select a.id1,a.id2,b.name id1_name from table1 a,table2 b where a.id1=b.id and a.id1=?),
a2 as (select a.id1,a.id2,b,name id2_name from table1 a,table2 b where a.id2=b.id and a.id2=?)
select a1.id1,a2.id2,a1.id1_name,a2.id2_name from a1,a2 where a1.id1=a2.id1 and a1.id2=a2.id2;
创建三个索引
table1.id1
table1.id2
table2.id
如下:
digoal=> create table table1 (id1 int,id2 int);
digoal=> create table table2 (id int,name name);
digoal=> alter table table2 add constraint uk_table2_id unique (id);
digoal=> alter table table1 add constraint fk_id1 foreign key (id1) references table2 (id);
digoal=> alter table table1 add constraint fk_id2 foreign key (id2) references table2 (id);
digoal=> create index idx_table1_id1 on table1(id1);
digoal=> create index idx_table1_id2 on table1(id2);
digoal=> insert into table2 select generate_series(1,100100),'digoal_'||generate_series(1,100100);
INSERT 0 100100
digoal=> insert into table1 select generate_series(1,100000),generate_series(101,100100);
INSERT 0 100000
digoal=> \d table2
Table "digoal.table2"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | name |
Indexes:
"uk_table2_id" UNIQUE, btree (id)
Referenced by:
TABLE "table1" CONSTRAINT "fk_id1" FOREIGN KEY (id1) REFERENCES table2(id)
TABLE "table1" CONSTRAINT "fk_id2" FOREIGN KEY (id2) REFERENCES table2(id)
digoal=> \d table1
Table "digoal.table1"
Column | Type | Modifiers
--------+---------+-----------
id1 | integer |
id2 | integer |
Indexes:
"idx_table1_id1" btree (id1)
"idx_table1_id2" btree (id2)
Foreign-key constraints:
"fk_id1" FOREIGN KEY (id1) REFERENCES table2(id)
"fk_id2" FOREIGN KEY (id2) REFERENCES table2(id)
digoal=> with a1 as (select a.id1,a.id2,b.name id1_name from table1 a,table2 b where a.id1=b.id and a.id1=1),
digoal-> a2 as (select a.id1,a.id2,b,name id2_name from table1 a,table2 b where a.id2=b.id and a.id2=101)
digoal-> select a1.id1,a2.id2,a1.id1_name,a2.id2_name from a1,a2 where a1.id1=a2.id1 and a1.id2=a2.id2;
id1 | id2 | id1_name | id2_name
-----+-----+----------+------------
1 | 101 | digoal_1 | digoal_101
(1 row)
Time: 0.678 ms
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
