PostgreSQL : WITH Queries use case

背景

一位同事有一个这样的需求，不知道怎么写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  

digoal’s 大量PostgreSQL文章入口