我创建了一个游戏,用户可以在其中回答测验问题 - 他们可以根据需要多次回答。请参阅此处的示例问题(统计信息位于侧边栏右上角)。
目前,我只计算总统计数据,即所有用户的所有尝试。当然,这是在伪造每个问题的统计数据。
SELECT SUM(correct=1) AS correct, SUM(correct=0) AS incorrect, timestamp
FROM `gametable`
WHERE questionid = #
游戏桌的示例日期:
+---------------------+--------+------------+---------+
| 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 查询来做到这一点?
使用子查询获取每个用户的第一次尝试:-
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 的 null。这样做是为了忽略具有用户 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 = #