Question

Ok,我有两个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;

如你所知,会议与用户识别仪有链接。现在,我想提供全部高分数的购买力平价文件,但我很少了解营地,很少了解MYSQL I的开端。总得分将按总运行时间分类。

Example: In users I have user #1 (cody) & user #2 (Joe). Now, In sessions I have 3 sessions:

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

Answer 2

该笔记本应为你制作一张记分。感谢OMG Ponies for the Qry.

<?php
$dbhost =  localhost ;  // Database Host
$dbuser =  user ;       // Database User
$dbpass =  password ;   // Database Password
$dbname =  database ;   // Database Name

$db = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname, $db);

$limit = 10; // The number of users to show in the top highscores.  Set to 0 to show all

$sql =  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 ;

if ($limit > 0) {
    $sql .=   LIMIT  .$limit;
}

$get_scores = mysql_query($sql);
$scores = array();

$rank = 0;
while($score = mysql_fetch_array($get_scores)) {
    ++$rank;
    $scores[] =  <td> .$rank. </td><td> .$score[ user ]. </td><td> .$score[ total_run_time ]. </td><td> .$score[ total_monsters_killed ]. </td><td> .$score[ total_profit ]. </td><td> .$score[ total_tasks ]. </td><td> .$score[ total_xp_gained ]. </td> ;
}

echo  <table><tbody> ;
echo  <tr><td>Place</td><td>Username</td><td>Total Run Time</td><td>Total Monsters Killed, Total profit</td><td>Total tasks</td><td>Total Exp Gain</td></tr><tr> ;
echo implode( </tr><tr> , $scores);
echo  </tr></tbody></table> ;
?>

友情链接