将复杂布尔表达式作为列的postgreSQL查询

Question

我的PostgreSQL数据库中有以下表格：

• classes：包含class_id和其他字段
• 运动员：包含athlete_id和其他字段
• 阶级运动员：阶级和运动员之间是一种多对多的关系。它包含为每个class_id注册的athlets_id。在我的情况下，它看起来是这样的：

select * FROM classes_athletes;
class_id | athlete_id
-------------------+------------
2 |          1
2 |          2
2 |          3
2 |          4
2 |          5
1 |          1
1 |          2
1 |          3
1 |          4

现在我想做的是创建一个SQL查询，我可以用它来指示类是否包含某个运动员。以下是基于上述数据的一些示例：

For athlete_id=1 it should return
class_id | athleteIsEnrolled
----------+------------------
1 |          1
2 |          1
For athlete_id=99 it should return
class_id | athleteIsEnrolled
----------+------------------
1 |          0
2 |          0

我尝试了以下sql查询，但它为classes_thletes的每个条目返回一个值，而不是为每个唯一的class_id只返回一个：

SELECT c.class_id, CAST(CASE WHEN athlete_id = 1 THEN 1 ELSE 0 END AS bit) as athleteEnrolled
FROM classes_athletes  as c;

结果是：

class_id | athleteEnrolled
-------------------+-----------------
2 | 1
2 | 0
2 | 0
2 | 0
2 | 0
1 | 1
1 | 0
1 | 0
1 | 0

Answer 1

使用以下条件聚合：

SELECT c.class_id, Count(1) as athleteEnrolled
FROM classes c 
left join classes_athletes ca 
on c.class_id = ca.class_id
and ca.athlete_id = 1
Group by c.class_id;

Answer 2

使用左联接方法：

SELECT
c.class_id,
COUNT(ca.class_id) AS athleteEnrolled
FROM classes c
LEFT JOIN classes_athletes ca
ON ca.class_id = c.class_id AND
ca.athlete_id = 1
GROUP BY
c.class_id
ORDER BY
c.class_id;

Answer 3

使用子查询方法：

SELECT 
class_id,
(SELECT COUNT(1) FROM classes_athletes AS enrollment 
WHERE enrollment.class_id = classes.class_id AND athlete_id = 99
) AS athleteEnrolled
FROM classes
ORDER BY class_id

Answer 4

您可以使用EXISTS:执行此操作

SELECT c.class_id,
EXISTS (
SELECT 1 FROM classes_athletes ca 
WHERE ca.class_id = c.class_id AND athlete_id = ?
) athleteEnrolled
FROM classes c

将?替换为您搜索的athlete_id
此查询返回布尔结果true或false
如果希望结果为整数1或0，可以使用::int将其强制转换为整数

请参阅演示