sql - 左加入 Group By

Question

我正在使用 PostgreSQL 9.4。

我有一张锻炼的表格。用户可以为每个workout创建多个result，一个result有一个score。

给定一个 workout_id 列表和两个 user_id，我想返回每个用户每次锻炼的最佳分数。如果用户没有该锻炼的结果，我想返回一个填充/空结果。

SELECT "results".*, "workouts".* 
FROM "results" LEFT JOIN "workouts" ON "workouts"."id" = "results"."workout_id" 
WHERE (
  (user_id, workout_id, score) IN 
  (SELECT user_id, workout_id, MAX(score) 
    FROM results WHERE user_id IN (1, 2) AND workout_id IN (1, 2, 3) 
    GROUP BY user_id, workout_id)
) 

在此查询中，左连接充当内连接；如果用户没有得到锻炼结果，我不会得到任何填充。此查询应始终返回六行，无论存在多少结果。

示例数据:

results
user_id | workout_id | score 
-----------------------------
      1 |          1 |     10
      1 |          3 |     10
      1 |          3 |     15
      2 |          1 |      5

Desired result:

results.user_id | results.workout_id | max(results.score) | workouts.name
-------------------------------------------------------------------------
              1 |                  1 |                 10 | Squat
              1 |                  2 |               null | Bench
              1 |                  3 |                 15 | Deadlift
              2 |                  1 |                  5 | Squat
              2 |                  2 |               null | Bench
              2 |                  3 |               null | Deadlift

Answer 1

where 过滤掉您的 NULL 值，因此这就是结果不是您期望的原因。

加入 WHERE 子句结果而不是过滤 where 子句结果。

SELECT "results".*, "workouts".*,"max_score".*
FROM "results" 
LEFT JOIN "workouts" ON "workouts"."id" = "results"."workout_id"
LEFT JOIN (SELECT user_id, workout_id, MAX(score) 
    FROM results WHERE user_id IN (1, 2) AND workout_id IN (1, 2, 3) 
    GROUP BY user_id, workout_id) max_score ON workouts.workout_id=max_score.workout_id;

您需要更改 SELECT 以获得正确的列。