MySQL:两次链接到同一个表但不同的行

Question

我有两个表 T1 和 T2。 T1和T2之间存在1:n的关系。

CREATE TABLE T1 (
  PK INT
  /* more columns here */
);

CREATE TABLE T2 (
  PK INT,
  T1_PK INT,
  Active INT,
  Rank INT
  /* more columns here */
);

INSERT INTO T1 VALUES (1);
INSERT INTO T1 VALUES (2);
INSERT INTO T1 VALUES (3);

INSERT INTO T2 VALUES (1, 1, 1, 20);
INSERT INTO T2 VALUES (2, 1, 0, 15);
INSERT INTO T2 VALUES (3, 1, 0, 10);
INSERT INTO T2 VALUES (4, 1, 0, 5);

INSERT INTO T2 VALUES (5, 2, 1, 16);
INSERT INTO T2 VALUES (6, 2, 0, 11);
INSERT INTO T2 VALUES (7, 2, 0, 6);

INSERT INTO T2 VALUES (8, 3, 1, 7);

因此 T1 中的每一行都有:
– T2 中 1 个对应行，Active = 1
– T2 中 Active = 0 的 0 到 n 对应行

T2 中的 Rank 列包含应用程序 UI 中最终用户排序的值。

这是我目前的简单查询:

   SELECT T1.PK, T2.Active, T2.Rank
     FROM T1
LEFT JOIN T2 ON T1.PK = T2.T1_PK
    WHERE T2.Active = 1

它返回这个结果集:

PK      Active  Rank
1       1       20
2       1       16
3       1       7

这样就可以正常工作了。

现在我想在同一个结果集中获取 Active = 0 和 MIN(Rank) 的行值:

PK      Active  Rank    Active  Rank
1       1       20      0       5
2       1       16      0       6
3       1       7       null    null

我尝试了第二个 JOIN、子查询、GROUP BY 和 HAVING，但无济于事。

注意上面的代码片段可以复制到http://sqlfiddle.com并且会起作用。

Answer 1

尝试使用另一个左连接，这次使用 t1_pk 的聚合值，其中 active = 0 的行具有最小等级。

select t1.pk,
    t2.active,
    t2.rank,
    0 as active_0,
    t.rank as min_rank_0
from t1
left join t2 on t1.pk = t2.t1_pk
    and t2.active = 1
left join (
    select t1_pk,
        min(rank) as rank
    from t2
    where active = 0
    group by t1_pk
    ) t on t1.pk = t.t1_pk;

Demo