mysql - 如何在SQL查询中添加固定位置列？

Question

我有队桌

CREATE TABLE teams(id int primary key auto_increment,tname char(32) unique);

我也有游戏桌

CREATE TABLE games(id int primary key auto_increment, date datetime,
               hteam int, ateam int, hscore tinyint,ascore tinyint);

这是我显示联盟排名的SQL查询

SELECT
tname AS Team, Sum(P) AS P,Sum(W) AS W,Sum(D) AS D,Sum(L) AS L,
SUM(F) as F,SUM(A) AS A,SUM(GD) AS GD,SUM(Pts) AS Pts
FROM(
SELECT
hteam Team,
1 P,
IF(hscore > ascore,1,0) W,
IF(hscore = ascore,1,0) D,
IF(hscore < ascore,1,0) L,
hscore F,
ascore A,
hscore-ascore GD,
CASE WHEN hscore > ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END PTS
FROM games
UNION ALL
SELECT
ateam,
1,
IF(hscore < ascore,1,0),
IF(hscore = ascore,1,0),
IF(hscore > ascore,1,0),
ascore,
hscore,
ascore-hscore GD,
CASE WHEN hscore < ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END
FROM games
) as tot
JOIN teams t ON tot.Team=t.id
GROUP BY Team
ORDER BY SUM(Pts) DESC, SUM(GD) DESC, SUM(F) DESC;

它可能直截了当，但我似乎无法弄清楚，我该如何在联赛表的最左侧添加一个以“ POS”表示的“排名”列，该列将显示表中的各个球队排名，例如自动递增？

谢谢！ :)

更新：

我设法在排名表的左侧获得了一个排名列，但是它显示了相应的ID，而不是他们在排名表中的排名。

SET @rownum := 0;
SELECT @rownum := @rownum + 1 AS rank, tname AS Team, Sum(P) AS P,Sum(W) AS 
W,Sum(D) AS D,Sum(L) AS L,
SUM(F) as F,SUM(A) AS A,SUM(GD) AS GD,SUM(Pts) AS Pts
FROM(
SELECT
hteam Team,
1 P,
IF(hscore > ascore,1,0) W,
IF(hscore = ascore,1,0) D,
IF(hscore < ascore,1,0) L,
hscore F,
ascore A,
hscore-ascore GD,
CASE WHEN hscore > ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END PTS
FROM games
UNION ALL
SELECT
ateam,
1,
IF(hscore < ascore,1,0),
IF(hscore = ascore,1,0),
IF(hscore > ascore,1,0),
ascore,
hscore,
ascore-hscore GD,
CASE WHEN hscore < ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END
FROM games
) as tot
JOIN teams t ON tot.Team=t.id
GROUP BY Team
ORDER BY SUM(Pts) DESC, SUM(GD) DESC, SUM(F) DESC;

Answer 1

这是您要找的东西吗？

SET @rownum := 0;
SELECT @rownum := @rownum + 1 AS rank, <other columns>
FROM <tables >
where ..