计算领奖台上的位置数目
计算领奖台上的位置数目
提问于 2019-02-28 00:39:37
谁能帮助我如何计算领奖台上的位置？此代码仅计算在讲台上每个地方至少一次的人。在第一张照片中，我发送的是“运动员”和“比赛”。在第二张图片中我得到了结果
SELECT ath.First_Name, ath.Last_Name, "First Places", "Second Places", "Third Places" from Athletes ath
join (SELECT first_place, count(*) as "First Places" from competition group by first_place) st on ath.id_athlete = st.first_place
join (SELECT second_place, count(*) as "Second Places" from competition group by second_place) nd on ath.id_athlete = nd.second_place
join (SELECT third_place, count(*) as "Third Places" from competition group by third_place) rd on ath.id_athlete = rd.third_place
ORDER BY 3 DESC;
第一张照片
第二张照片
回答 1
Stack Overflow用户
发布于 2019-02-28 10:32:06
使用左连接而不是内连接（没有进一步分类JOIN意味着内连接）。它将选择所有运动员的成绩。如果运动员从未到过某个地方，那么该地方的人数将是NULL。用于coalesce()将此转换为零。
SELECT ath.First_Name, ath.Last_Name, coalesce("First Places", 0) "First Places", coalesce("Second Places") "Second Places", coalesce("Third Places") "Third Places" from Athletes ath
LEFT join (SELECT first_place, count(*) as "First Places" from competition group by first_place) st on ath.id_athlete = st.first_place
LEFT join (SELECT second_place, count(*) as "Second Places" from competition group by second_place) nd on ath.id_athlete = nd.second_place
LEFT (SELECT third_place, count(*) as "Third Places" from competition group by third_place) rd on ath.id_athlete = rd.third_place
ORDER BY 3 DESC;
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/-100003127
复制相关文章
相似问题
腾讯云开发者
