PHP - 从 MySQL 数据构建排行榜

Question

我有两组来自 MySQL 的数据(用户及其测试数据)。我将根据这些数据构建一个排行榜，其中每个用户都应该有平均成绩 (%)、分数(所有测试结果的总和)和用户参加的测试总数。最后，当排行榜的所有数据形成时，应按降序排序，因此首先是最高的平均成绩和相关用户的分数等。我从 while 循环开始，但将数据链接到用户名中。

PHP 代码:

// contains set of data with fields user_id(INT) and name(VARCHAR)
$users = mysqli_fetch_assoc($result_users);

// contains set of data with fields user_id(INT), socres(DECIMAL), passed(BOOL)
$data = mysqli_fetch_assoc($result_data);

$pos = null; // collect positive tests
$neg = null; // collect negative tests
while ($data = mysqli_fetch_assoc($result_all)) {
    if ($data['quiz_passed'] == 1) {
        $pos += 1;
    } elseif ($data['quiz_passed'] == 0) {
        $neg += 1;
    }
}

预期排行榜结果:

Name     Average (Pos/Neg)     Scores (Sum of scores field)     Total tests (Pos+Neg)
-------------------------------------------------------------------------------------
John     80%                   143                              9

         // 4 Pos / 5 Neg                                       // 4 Pos + 5 Neg

如有任何帮助，我们将不胜感激。

更新:

User Table
----------
CREATE TABLE IF NOT EXISTS `user` (
  `user_id` int(11) NOT NULL,        // PRIMARY KEY
  `name` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `fullname` varchar(255) NOT NULL,
  `token` varchar(128) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(4, 'test1', 'password_here1', 'Tim Roth', 'token_here1'),
(5, 'test2', 'password_here2', 'Christoph Waltz', 'token_here2'),
(6, 'test3', 'password_here3', 'John Travolta', 'token_here3'),


Data Table
----------
CREATE TABLE IF NOT EXISTS `data` (
  `id` int(11) NOT NULL,            // PRIMARY KEY
  `user_id` int(11) NOT NULL,
  `scores` decimal(3,2) NOT NULL,
  `passed` tinyint(1) NOT NULL,
  `time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(1, 6, '0.60', 0, '2014-11-12 01:24:43'),
(2, 4, '0.75', 1, '2014-10-31 06:33:48'),
(3, 4, '0.90', 1, '2014-11-02 15:11:09'),
(4, 4, '0.50', 0, '2014-11-06 19:29:19'),
(5, 5, '0.75', 1, '2014-11-07 08:21:44'),
(6, 5, '0.60', 0, '2014-11-10 17:34:00'),
(7, 6, '0.60', 0, '2014-11-11 16:13:50'),
(8, 4, '0.85', 1, '2014-11-12 13:22:49')

Answer 1

根据数据，存在一个小问题。您对平均值的计算似乎是 pass = 1 的计数除以 pass = 0 的计数。因此，如果某人没有未通过考试，则会除以零。虽然很容易编写代码，但我需要知道在这种情况下你想做什么。

SELECT u.user_id, SUM(d.passed) / SUM(IF(d.passed = 0, 1, 0)) AS `Average`, SUM(d.scores) AS `Scores`, COUNT(d.id) AS `Total Tests`
FROM user u
INNER JOIN data d
ON u.user_id = d.user_id
GROUP BY u.user_id;

不确定按考试次数来计算通过次数是否更合适。

SELECT u.user_id, SUM(d.passed) / COUNT(d.passed) AS `Average`, SUM(d.scores) AS `Scores`, COUNT(d.id) AS `Total Tests`
FROM user u
INNER JOIN data d
ON u.user_id = d.user_id
GROUP BY u.user_id;

SQL fiddle 显示 2 个结果:-

http://www.sqlfiddle.com/#!2/ecf3d2/1