如何查询SQL以获得这样的结果

game
-----
id
start_time
player_p_id

player
-------
p_id
username

answer
------
id
correct
text

game_answer
-----------
game_id
answers_id

Hello everyone, How could I create a SQL query to select (game.id, player.name, [score]) ?

大家好,我怎样才能创建一个SQL查询来选择(game.id,player.name,[score])?

I am expecting to get a result game's id, player's name, and score. Score is the sum of correct answer.

我期待得到一个结果游戏的id,玩家的名字和分数。分数是正确答案的总和。

I tried this query but it doesn't work

我试过这个查询,但它不起作用

SELECT g.id, p.username, g.start_time, sum(a.correct)
from GAME g
INNER JOIN PLAYER p ON g.player_p_id = p.p_id
INNER JOIN GAME_ANSWER ga ON ga.GAME_ID = g.ID
INNER JOIN ANSWER a ON a.id = ga.ANSWERS_ID
ORDER BY g.id;

Note: answer.correct is either o or 1

注意:answer.correct是o或1

1 个解决方案

#1

There are two obvious syntax problems with your query.

查询有两个明显的语法问题。

It references various columns by different names than what you give in the table definitions. For instance, the query references a.correct, but you show that the column in answers is called 'isCorrect. You are probably getting error messages indicating the columns don't exist. Use the correct names for all the columns.

它使用与您在表定义中提供的名称不同的名称引用各种列。例如,查询引用了a.correct,但是您显示答案中的列被称为'isCorrect。您可能收到错误消息,指出列不存在。为所有列使用正确的名称。

(Seems like you have now updated the table definitions so that the column names match. So the above may not be an issue.)

(好像你现在已经更新了表定义,以便列名匹配。所以上面的内容可能不是问题。)

You have no GROUP BY clause in the query. SUM is a group function. If you have both ungrouped values (g.id, p.username, g.start_time) and grouped values in your select list, you need a GROUP BY clause. Usually you simply include every column from the select list that's not surrounded by a group function. In this case it looks like you want GROUP BY g.id, p.username, g.start_time. This should precede the ORDER BY clause.

查询中没有GROUP BY子句。 SUM是一个组函数。如果在选择列表中同时包含未分组的值(g.id,p.username,g.start_time)和分组值,则需要GROUP BY子句。通常,您只需在选择列表中包含未被组函数包围的每一列。在这种情况下,它看起来像你想要GROUP BY g.id,p.username,g.start_time。这应该在ORDER BY子句之前。

You have now clarified that isCorrect contains 0 or 1, so with the other corrections, the SUM function should give the result you want.

您现在已经澄清isCorrect包含0或1,因此对于其他更正,SUM函数应该给出您想要的结果。

#1