mysql - 游戏mysql表: How to count only first attempt per user and provide total SUM of correct/incorrect?

Question

我创建了一款游戏，用户可以根据需要多次回答测验问题。请参阅example question here (统计信息位于右上角的侧边栏中)。

目前我只统计总统计数据，即。 e.所有用户的所有尝试。当然，这伪造了每个问题的统计数据。

SELECT SUM(correct=1) AS correct, SUM(correct=0) AS incorrect, timestamp 
    FROM `gametable` 
    WHERE questionid = #

游戏 table 日期示例:

+---------------------+--------+------------+---------+
| timestamp           | userid | questionid | correct |
+---------------------+--------+------------+---------+
| 2014-12-07 15:38:35 |      1 |         33 |       0 |
| 2014-12-07 15:39:40 |      1 |         33 |       1 |
| 2014-12-07 15:41:40 |      1 |         33 |       1 |
| 2014-12-07 16:00:17 |      2 |         33 |       1 |
| 2014-12-07 16:08:00 |      2 |         33 |       0 |
| 2014-12-07 16:09:00 |      2 |         33 |       0 |
| 2014-12-07 16:10:25 |      2 |         33 |       1 |
+---------------------+--------+------------+---------+

上面的表与给出的 mysql 查询的结果是:4 个正确，3 个不正确。

但是，“第一次尝试”结果应该是:1 个正确，1 个不正确。

有没有办法编写 mysql 查询来做到这一点？

Answer 1

使用子查询获取每个用户的第一次尝试:-

SELECT SUM(correct=1) AS correct, SUM(correct=0) AS incorrect
FROM `gametable` 
INNER JOIN
(
    SELECT userid, MIN(timestamp) AS mintimestamp
    FROM `gametable` 
    GROUP BY userid
) sub0
ON gametable.userid = sub0.userid
WHERE questionid = #
AND gametable.timestamp =  sub0.mintimestamp

注意，我已从外部选择中删除了时间戳列，因为它似乎不是必需的，也不会带回有意义的值。

请注意，您的示例数据不包含问题字段，但示例 SQL 包含。假设您确实有一个问题字段，您还需要选择特定问题的第一次尝试:-

SELECT SUM(correct=1) AS correct, SUM(correct=0) AS incorrect
FROM `gametable` 
INNER JOIN
(
    SELECT userid, questionid, MIN(timestamp) AS mintimestamp
    FROM `gametable` 
    GROUP BY userid, questionid
) sub0
ON gametable.userid = sub0.userid
AND gametable.questionid = sub0.questionid
AND gametable.timestamp =  sub0.mintimestamp
WHERE gametable.questionid = #

或

SELECT SUM(correct=1) AS correct, SUM(correct=0) AS incorrect
FROM `gametable` 
INNER JOIN
(
    SELECT userid, MIN(timestamp) AS mintimestamp
    FROM `gametable` 
    WHERE gametable.questionid = #
    GROUP BY userid
) sub0
ON gametable.userid = sub0.userid
AND gametable.timestamp =  sub0.mintimestamp

编辑 - 建议根据 cookie id 处理匿名用户。如果用户 id 为 null，则返回 cookie id，否则返回 cookie id。这样做可以使具有用户 ID 的记录忽略 Cookie ID。

SELECT SUM(correct=1) AS correct, SUM(correct=0) AS incorrect
FROM `gametable` 
INNER JOIN
(
    SELECT userid, if(userid IS NULL, cookie_id, NULL) AS cookie_id, questionid, MIN(timestamp) AS mintimestamp
    FROM `gametable` 
    GROUP BY userid, cookie_id, questionid
) sub0
ON ((gametable.userid IS NULL
AND gametable.cookie_id = sub0.cookie_id)
OR (gametable.userid IS NOT NULL
AND gametable.userid = sub0.userid))
AND gametable.questionid = sub0.questionid
AND gametable.timestamp =  sub0.mintimestamp
WHERE gametable.questionid = #