mysql特殊查询

Question

我有两张桌子：teams和formations

表teams

team_id | team_name
   1    | Barcelona
   2    | Real Madrid
   3    | PSG

表formations

formation_id | team_id | module
    1        |    2    | 5-3-2
    2        |    1    | 4-4-2
    3        |    3    | 4-4-2
    4        |    2    | 4-4-3

实际上，我需要通过team_id在两个表组之间“联接”，但使用最后一个"formation_id“。

我的结果是：

 team_id | team_name  | formation_id  | module
    1    | Barcelona  |     2         |  4-4-2
    2    | Real Madrid|     4         |  4-4-3
    3    | PSG        |     3         |  4-4-2 

实际上，我的疑问是：

SELECT *
  FROM formations f
 INNER JOIN teams t 
    ON (f.team_id = t.team_id)
 GROUP BY t.team_id

在我的查询中，我为每个团队选择了第一个insert格式，而不是为每个团队选择最后的阵形。

Answer 1

你可以写：

SELECT t.team_id,
       t.team_name,
       f.formation_id,
       f.module
  FROM teams t
  JOIN formations f
    ON f.team_id = t.team_id
       -- require f.formation_id to be MAX(formation_id) for some team:
  JOIN ( SELECT MAX(formation_id) AS id
           FROM formations
          GROUP
             BY team_id
       ) max_formation_ids
    ON max_formation_ids.id = f.formation_id
;

或者：

SELECT t.team_id,
       t.team_name,
       f.formation_id,
       f.module
  FROM teams t
  JOIN formations f
    ON f.team_id = t.team_id
       -- require f.formation_id to be MAX(formation_id) for this team:
 WHERE f.formation_id =
        ( SELECT MAX(formation_id)
            FROM formations
           WHERE team_id = t.team_id
        )
;

或者：

SELECT t.team_id,
       t.team_name,
       f.formation_id,
       f.module
  FROM teams t
  JOIN formations f
    ON f.team_id = t.team_id
       -- forbid f.formation_id to be less than another for the same team:
  LEFT
 OUTER
  JOIN formations f2
    ON f2.team_id = t.team_id
   AND f2.formation_id > f.formation_id
 WHERE f2.formation_id IS NULL
;

Answer 2

检查这个 SQLFIDDLE

SELECT A.team_id,A.team_name,B.formation_id,B.module
FROM teams A,formations B
WHERE A.team_id=B.team_id
AND B.formation_id =
(
  SELECT max(formation_id)
  FROM formations C
  WHERE C.team_id =B.team_id
 )
ORDER BY A.team_id;


create table teams
(  
  team_id int
 ,team_name varchar(40)
);
create table formations 
(
  formation_id int
 ,team_id  int
 ,module int
);
insert into teams
values
(1,'Barcelona'),(2,'Real Madrid'),(3,'PSG');
insert into formations
values
(1,2,532),(2,1,442),(3,3,442),(4,2,443);

Answer 3

您可以使用一个子查询找到它们的最大形成ID，然后将其与原始表连接起来。试试这个，

SELECT  a.*, c.formation_ID, c.`module`
FROM    teams a
        INNER JOIN
        (
            SELECT team_id, MAX(formation_ID) maxID
            FROM formations
            GROUP BY team_ID
        ) b ON  a.team_id = b.team_id
        INNER JOIN formations c
            ON  c.team_id = b.team_id AND
                c.formation_ID = b.maxID
ORDER BY a.Team_id

SQLFiddle演示