SQL：限制一张表的结果

debugcn 发表于 Dev

底那

我有一个简单的查询：

SELECT 
    c.nom AS race_name, 
    r.nom_coureur AS player_name, 
    r.prenom_coureur AS player_name_first
FROM pourvelobsx.calendrier AS c    
INNER JOIN pourvelobsx.resultat_autre AS r 
    ON r.identifiant_epreuve = c.identifiant
WHERE nom_etape = "final"
ORDER BY c.id DESC
LIMIT 5

查询返回 5 行：

+------------------+-------------+-------------------+
| race_name        | player_name | player_name_first |
+------------------+-------------+-------------------+
| Watashi wa Alex  | BARBAS      | Yoann             |
| Watashi wa Alex  | AHLSTRAND   | Jonas             |
| Watashi wa Alex  | ACEVEDO     | Janier Alexis     |
| Tour de La Baule | CHAVANEL    | Sylvain           |
| Tour de La Baule | SANCHEZ     | Samuel            |
+------------------+-------------+-------------------+

我想知道，如何限制比赛的结果（“日历”表）而不是全局行结果？

像这样：

+------------------+-------------+-------------------+
| race_name        | player_name | player_name_first |
+------------------+-------------+-------------------+
| Watashi wa Alex  | BARBAS      | Yoann             |
| Watashi wa Alex  | AHLSTRAND   | Jonas             |
| Watashi wa Alex  | ACEVEDO     | Janier Alexis     |
| Tour de La Baule | CHAVANEL    | Sylvain           |
| Tour de La Baule | SANCHEZ     | Samuel            |
| Tour de La Groin | HELLO       | Calvin            |
| Tour de La Groin | ESTEBEZ     | Millo             |
| Tour de France   | SANCHEZ     | Samuel            |
| Tour de France   | SICKED      | Alex              |
| Tour d'Espagne   | VILLIOD     | Fred              |
| Tour d'Espagne   | CRACHER     | Jordan            |
| Tour d'Espagne   | ELBANTI     | Maxime            |
| Tour d'Espagne   | BOKEDRON    | Yohan             |
+------------------+-------------+-------------------+

所以我只有 5 场比赛，不限制排。我尝试过DISTINCT但没有成功。

我为 Bac 工作

我相信你想要这样的东西

SELECT 
  c.nom AS race_name, 
  r.nom_coureur AS player_name, 
  r.prenom_coureur AS player_name_first
FROM pourvelobsx.calendrier AS c    
INNER JOIN pourvelobsx.resultat_autre AS r ON r.identifiant_epreuve = c.identifiant
INNER JOIN
(
  SELECT c.nom
  FROM pourvelobsx.calendrier AS c   
  INNER JOIN pourvelobsx.resultat_autre AS r ON r.identifiant_epreuve = c.identifiant     
  WHERE nom_etape = "final"
  GROUP BY c.nom
  ORDER BY c.id DESC
  LIMIT 5
) t ON t.nom = c.nom
WHERE nom_etape = "final"

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。