与多个结果连接两个表放在一个表

问题描述：

tbl_teams 
id, teamname, created 

tbl_teamstats 
id, rank, rating, wins, losses, tbl_teams_id, created

我想根据tbl_teamstats这两个表合并在一起“创建递减限制1” 例如查询

SELECT TS.*, T.* FROM tbl_teamstats as TS 
LEFT JOIN tbl_teams as T 
ON T.id = TS.tbl_teams_id

会回到这样的事情

TS.id TS.rank TS.rating TS.wins TS.losses T.id T.teamname    T.created 
14871 2  2522  168  26   2  teamname1    23/02/2017 17:55 
14688 2  2540  168  25   2  teamname1    23/02/2017 17:55 
2683 2  2535  167  25   2  teamname1    23/02/2017 17:55 
2612 2  2529  166  25   2  teamname1    23/02/2017 17:55 
2590 2  2523  165  25   2  teamname1    23/02/2017 17:55 
2448 2  2517  164  25   2  teamname1    23/02/2017 17:55 
2346 2  2511  163  25   2  teamname1    23/02/2017 17:55 
234  2  2505  162  25   2  teamname1    23/02/2017 17:55 
1  1  2570  171  19   1  teamname2    23/02/2017 17:55

WH在我希望它看起来像：

TS.id TS.rank TS.rating TS.wins TS.losses T.id T.teamname    T.created 
14871 2  2522  168  26   2  teamname1    23/02/2017 17:55 
1  1  2570  171  19   1  teamname1    23/02/2017 17:55

（只显示来自tbl_teamstat由TS.id订购一个结果）

可能有人请帮助我，或点我在正确的方向？将不胜感激！

答

您可以在子查询中找到来自tbl_teamstats的每个tbl_team_id的最大ID并将其与tbl_teamstats结合使用。然后，根据需要进行连接。

select * 
from (
    select t1.* 
    from tbl_teamstats t1 
    join (
     select tbl_teams_id, 
      max(id) id 
     from tbl_teamstats 
     group by tbl_teams_id 
     ) t2 on t1.tbl_teams_id = t2.tbl_teams_id 
     and t1.id = t2.id 
    ) ts 
left join tbl_teams as T on T.id = TS.tbl_teams_id

谢谢添加GROUP BY条款！这解决了它:) –

答

末

SELECT TS.*, T.* FROM tbl_teamstats as TS 
LEFT JOIN tbl_teams as T 
ON T.id = TS.tbl_teams_id 
GROUP BY TS.rank

与多个结果连接两个表放在一个表

相关推荐