php - 如何从 SQL 表计算高分？

Question

好的，我有两个 MYSQL 表:

CREATE TABLE `sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userID` int(11) NOT NULL,
  `runTime` int(11) NOT NULL,
  `monstersKilled` int(11) NOT NULL,
  `profit` int(11) NOT NULL,
  `tasks` int(11) NOT NULL,
  `xpGain` int(11) NOT NULL,
  `lastupdate` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

还有，

CREATE TABLE  `users` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `user` text NOT NULL,
  `pass` text NOT NULL,
  `paypal` text NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  `strikes` int(11) NOT NULL DEFAULT '0',
  `session` text NOT NULL,
  `brand` text NOT NULL,
  `identifier` text NOT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=MyISAM AUTO_INCREMENT=651 DEFAULT CHARSET=latin1;

如您所见， session 具有指向用户 ID 的链接。现在想做一个总分高分的PHP文件，但是我对php的了解很少，对MYSQL的了解也很少，不知如何下手。总高分将按总运行时间排序。

例子:在用户中，我有用户 #1 (cody) 和用户 #2 (Joe)。现在，在 session 中我有 3 个 session :

id, userID, runTime, monstersKilled, profit, tasks, xpGain, lastupdate
12, 1, 27, 14, 6200, 0, 5050, 1282325410
19, 1, 18, 1, 277, 1, 168, 1278897756
1968, 2, 195, 433, 111345, 4, 73606, 1280993244

打印出来的应该是这样的:

Place, username, Total Run Time, Total Monsters Killed, Total profit, Total tasks, Total Exp Gain
1. Joe, 195, 433,11345,4,73606
2. Cody, 55, 15, 1, 5218

Answer 1

使用:

  SELECT u.user,
         SUM(s.runtime) AS total_run_time,
         SUM(s.monsterskilled) AS total_monsters_killed,
         SUM(s.profit) AS total_profit,
         SUM(s.tasks) AS total_tasks, 
         SUM(s.xpgain) AS total_xp_gained
    FROM USERS u
    JOIN SESSION s ON s.userid = u.userid
GROUP BY u.user
ORDER BY total_run_time DESC