我的表如下所示:
tbl_teams
id, teamname, created
tbl_teamstats
id, rank, rating, wins, losses, tbl_teams_id, created
我想根据 tbl_teamstats“created desc limit 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
我希望它看起来像什么:
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_team_id
from 的最大 idtbl_teamstats
并将其与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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句