sql - SELECT 语句涉及三个不同的表

Question

注意：我不确定如何命名，如果有人有更好的主意，请随时更改它。

---

我的数据库目前有以下表格（一些列和其他详细信息已从所示命令的输出中删除，但它们不应该相关）：

webserver=> \d+ team

   Column    |          Type          |                     Modifiers                     | Storage  
-------------+------------------------+---------------------------------------------------+----------
 id          | integer                | not null default nextval('team_id_seq'::regclass) | plain     
 name        | character varying(255) | not null                                          | extended  
Indexes:
    "team_pkey" PRIMARY KEY, btree (id)
    "team_name" UNIQUE, btree (name)
Referenced by:
    TABLE "challengesolve" CONSTRAINT "challengesolve_team_id_fkey" FOREIGN KEY (team_id) REFERENCES team(id)

webserver=> \d+ challenge

       Column       |          Type          |                       Modifiers                        | Storage  
--------------------+------------------------+--------------------------------------------------------+----------
 id                 | integer                | not null default nextval('challenge_id_seq'::regclass) | plain     
 name               | character varying(255) | not null                                               | extended  
 points             | integer                | not null                                               | plain     
Indexes:
    "challenge_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "challengesolve" CONSTRAINT "challengesolve_challenge_id_fkey" FOREIGN KEY (challenge_id) REFERENCES challenge(id)

webserver=> \d+ challengesolve

    Column    |            Type             | Modifiers | Storage  
--------------+-----------------------------+-----------+---------
 team_id      | integer                     | not null  | plain   
 challenge_id | integer                     | not null  | plain   
Indexes:
    "challengesolve_pkey" PRIMARY KEY, btree (team_id, challenge_id)
    "challengesolve_challenge_id" btree (challenge_id)
    "challengesolve_team_id" btree (team_id)
Foreign-key constraints:
    "challengesolve_challenge_id_fkey" FOREIGN KEY (challenge_id) REFERENCES challenge(id)
    "challengesolve_team_id_fkey" FOREIGN KEY (team_id) REFERENCES team(id)

和表应该是相当不言自明的team。challenge该challengesolve表是一个交叉表，指示哪些团队已经解决了哪些挑战。

简而言之，我想找到得分最多的三支球队。我已经知道我可以通过以下语句找到特定团队的积分数

SELECT SUM(challenge.points) AS points 
FROM challengesolve 
INNER JOIN challenge
ON challenge.id = challengesolve.challenge_id 
WHERE challengesolve.team_id = 1;

如何修改此查询以查找前三名团队？

标签： sqlpostgresqlselectgreatest-n-per-group

Answer 1

简而言之，我想找到得分最多的三支球队。

你可以使用：

SELECT challengesolve.team_id,SUM(challenge.points) AS points 
FROM challengesolve 
INNER JOIN challenge
ON challenge.id = challengesolve.challenge_id 
GROUP BY challengesolve.team_id
ORDER BY points DESC
LIMIT 3;