mysql - 合并 2 个 SQL 查询/表

Question

我今天花了很多时间在谷歌上搜索，但我什至不知道该使用哪些关键字。所以……

该项目是对博彩游戏(足球)的评估。我有 2 个 SQL 查询:

SELECT players.username, players.userid, matchdays.userid, matchdays.points, SUM(points) AS gesamt
FROM players INNER JOIN matchdays ON players.userid = matchdays.userid AND matchdays.season_id=5
            GROUP BY players.username
            ORDER BY gesamt DESC

我的第二个查询:

SELECT max(matchday) as lastmd, points, players.username from players INNER JOIN matchdays ON players.userid = matchdays.userid WHERE matchdays.season_id=5 AND matchday=
 (select max(matchday) from matchdays)group by players.username ORDER BY points DESC

第一个将每个比赛日的得分相加并显示总和。第二个显示最后一个比赛日的得分。

我的目标是合并这两个查询/表，以便输出像这样的表

排名|用户名 |上个比赛日的积分 |总分|

我什至不知道从哪里开始或寻找什么。任何帮助将不胜感激；)

Answer 1

将两个查询与联接一起使用...如果每个用户 ID 在第二个查询中也有值，则使用内部联接。还在第二个查询中添加用户 ID 以进行联接

    SET @rank = 0;

    SELECT  @rank := rank + 1,
                t1.username,
                t2.points,
                t1.gesamt
    FROM        (
                    SELECT  players.username, players.userid puserid, matchdays.userid muserid, matchdays.points, SUM(points) AS gesamt
                    FROM        players INNER JOIN matchdays ON players.userid = matchdays.userid AND matchdays.season_id=5
                 GROUP BY players.username           
              )t1
              INNER JOIN 
              ( 
                SELECT players.userid, max(matchday) as lastmd, points, players.username 
                    from players INNER JOIN matchdays ON players.userid = matchdays.userid 
                    WHERE matchdays.season_id=5 AND matchday=
                    (select max(matchday) from matchdays)group by players.username
                )t2
                ON t1.puserid = t2.userid
    ORDER BY t1.gesamt