Postgres 复杂连表查询

首先我们建立四张表，如下

CREATE TABLE blocks (
    file_id bigint NOT NULL,
    proj_id integer NOT NULL,
    block_id bigint NOT NULL,
);
CREATE TABLE files (
    proj_id integer NOT NULL,
    file_id bigint NOT NULL,
    file_path text,
    file_hash character varying(64),
);
CREATE TABLE projs (
    proj_id integer NOT NULL,
    proj_path text,
);
CREATE TABLE results (
    id bigint NOT NULL,
    proj_id0 integer NOT NULL,
    block_id0 bigint NOT NULL,
    proj_id1 integer NOT NULL,
    block_id1 bigint NOT NULL
);

results 是我们数据结果的表
查找表 results 如下：

select * from results limit 10;

现在需要把 proj_path 添加到输出结果上，分别 proj0_path， proj1_path

select 
    r.*, 
    proj0.proj_path as proj_path0, 
    proj1.proj_path as proj_path0,
from 
    resuts as r,
    projs as proj0, 
    projs as proj1 
where 
    proj0.proj_id=r.proj_id0 
and 
    proj1.proj_id=r.proj_id1
limit 1

把 file_id 添加到输出结果上， 分别 file_id0, file_id1。

select 
    r.*, 
    proj0.proj_path as proj_path0, 
    proj1.proj_path as proj_path0,
    block0.file_id as file_id0,
    block1.file_id as file_id1
from 
    resuts as r,
    projs as proj0, 
    projs as proj1,
    blocks as block0,
    blocks as block1
where 
    proj0.proj_id=r.proj_id0 
and 
    proj1.proj_id=r.proj_id1
and 
    block0.block_id=r.block_id0
and 
    block1.block_id=r.block_id1
limit 1

把 file_path 添加到输出结果上， 分别 file_path0, file_path1。

select 
    r.*, 
    proj0.proj_path as proj_path0, 
    proj1.proj_path as proj_path0,
    block0.file_id as file_id0,
    block1.file_id as file_id1,
    file0.file_path as file_path0,
    file1.file_path as file_path1
from 
    resuts as r,
    projs as proj0, 
    projs as proj1,
    blocks as block0,
    blocks as block1,
    files as file0,
    files as file1
where 
    proj0.proj_id=r.proj_id0 
and 
    proj1.proj_id=r.proj_id1
and 
    block0.block_id=r.block_id0
and 
    block1.block_id=r.block_id1
and
    file0.file_id=block0.file_id
and
    file1.file_id=block1.file_id
limit 1

到此就出我们想要的结果