m*****e
发帖数: 126 | 1 问题简化如下：两个table，
player table: playerid int, ...... other fields do not matter
game table: gameid int, player1 int, player2 int ...... other fields do not
matter
game table 中player1, player2分别对应player table中的playerid
请问如何用SELECT Query得到交锋次数最多的几对player. | Q**g
发帖数: 183 | 2 嗯，刚才在mysql里调过了。假设你想知道player table里的name。如果还想要别
的，一并填进去就是了。如果啥都不要，只想知道(player1, player2, count(gameid)
的话，就根本不用join了
select g.player1, g.player2, p1.name, p2.name, count(g.gameid) as counts
from game g, player p1, player p2
where g.player1=p1.playerid and g.player2=p2.playerid
group by g.player1, g.player2, p1.name, p2.name
order by counts desc
【在 m*****e 的大作中提到】
: 问题简化如下：两个table，
: player table: playerid int, ...... other fields do not matter
: game table: gameid int, player1 int, player2 int ...... other fields do not
: matter
: game table 中player1, player2分别对应player table中的playerid
: 请问如何用SELECT Query得到交锋次数最多的几对player.
| a*******t
发帖数: 891 | 3 can add "top" for "最多的几对player"
【在 Q**g 的大作中提到】
: 嗯，刚才在mysql里调过了。假设你想知道player table里的name。如果还想要别
: 的，一并填进去就是了。如果啥都不要，只想知道(player1, player2, count(gameid)
: 的话，就根本不用join了
: select g.player1, g.player2, p1.name, p2.name, count(g.gameid) as counts
: from game g, player p1, player p2
: where g.player1=p1.playerid and g.player2=p2.playerid
: group by g.player1, g.player2, p1.name, p2.name
: order by counts desc
|
|
