避免多次连接同一个表

Question

我有两张桌子：

CREATE TABLE one (
  id int4 primary key,
  p_id int4,
  k_id int4,
  c_id int4
);

CREATE TABLE two(
 id int4 primary key,
 p_id int4,
 k_id int4,
 t_id int4,
 pos int4
);

表one：是一个联接表，我在本报告中使用它只是因为c_id和限制行数。

表two：包含我的数据，一个p_id、k_id和t_id可以有很多pos，所以我对它们进行分组并使用min()来获得最小pos。我尝试了一些方法来获取k_id和p_id 这里有个方法的所有数据(使用这个解决方案，我无法通过pos订购)，所以我最终完成了这个(见小提琴)：

SELECT
  o.p_id,
  o.k_id,
  min(t.pos) as t_pos,
  min(t1.pos) as t1_pos
FROM one o
  LEFT JOIN two t ON t.p_id = o.p_id 
    AND t.k_id = o.k_id
    AND t.t_id = 1
  LEFT JOIN two t1 ON t1.p_id = o.p_id 
    AND t1.k_id = o.k_id
    AND t1.t_id = 2
WHERE o.p_id = 1 AND o.c_id = 1
GROUP BY 1, 2
LIMIT 1

这个解决方案比@Erwin Brandstetter的解决方案来自另一个帖子慢2-3倍，但是排序t_pos、t1_pos是可能的，并且还可以获取type_id = 1和pos = 1等所有数据。

是否有更好的方法来实现相同的输出，提高速度并能够对我的数据进行排序(t_pos和t1_pos列)？

Answer 1

试试这个对你有多好

对于两个t_id的表2，将左联接转换为一个内连接。在计算min时，使用case语句根据值t_id拆分成两列。我们在这里保存的是两次扫描表和一个内部连接，而不是左连接。

SELECT
    o.p_id,
    o.k_id,
    MIN(case when t.t_id = 1 then t.pos else null end) AS t_pos,
    MIN(case when t.t_id = 2 then t.pos else null end) AS t1_pos
FROM
    one o
INNER JOIN two T ON t.p_id = o.p_id
AND t.k_id = o.k_id
AND t.t_id =any ( 1,2)
WHERE
    o.p_id = 1
AND o.c_id = 1
GROUP BY
    1,
    2
LIMIT 1