MySQL查询 - 使用group-by时获取丢失的记录 [英] MySQL Query - getting missing records when using group-by

问题描述

我有一个查询：

  select score，count（1）as'NumStudents' from testresults where testid ='mytestid'
 group by score order by score 
  

其中testresults表包含学生在考试中的表现。如果测试的最大分数为10，则示例结果如下所示。
$ b blockquote>

分数，NumStudents

0 10
1 20
2 12
3 5 5 5 34
... 10 23

正如你所看到的，这个查询不返回没有学生评分的记录。例如。在测试中没有人得分为4/10，查询输出中没有记分= 4。

我想更改查询，以便我可以将0作为NumStudents字段值的缺失记录。这样我的最终输出就会有最多+1条记录，每个可能的分数都有一条记录。

有什么想法？

编辑：

数据库包含多个测试，测试的最大分数是测试定义的一部分。所以有一个新的表格来存储所有可能的分数是不可行的。从某种意义上说，每当我用新的最大分数创建一个新的测试时，我需要确保新的表格也应该改变以包含这些分数。

解决方案

SQL擅长处理数据库中的数据值集合，但不擅长数据值集合 不在数据库中。

最好的解决方法是为您需要的值保留一个小表格：

  CREATE TABLE ScoreValues（score int）; 
 INSERT INTO ScoreValues（score）
 VALUES（0），（1），（2），（3），（4），（5），（6），（7），（8） ，（9），（10）; 
  

考虑到您在另一个表中定义了测试的最大标记的评论，您可以加入到表，只要 ScoreValues 的值至少与最大测试的最大分数一样高或更高：

  SELECT v.score，COUNT（tr.score）AS'NumStudents'
 FROM ScoreValues v 
 JOIN测试t ON（v.score< ; = t.maxmarks）
 LEFT OUTER JOIN TestResults tr ON（v.score = tr.score AND t.testid = tr.testid）
 WHERE t.testid ='mytestid'
 GROUP BY v.score; 
  

I have a query :

select score, count(1) as 'NumStudents' from testresults where testid = 'mytestid'
group by score order by score

where testresults table contains the performances of students in a test. A sample result looks like the following, assuming maximum marks of the test is 10.

score, NumStudents

0 10
1 20
2 12
3 5
5 34
..
10 23

As you can see, this query does not return any records for scores which no student have scored. For eg. nobody scored 4/10 in the test and there are no records for score = 4 in the query output.

I would like to change the query so that I can get these missing records with 0 as the value for the NumStudents field. So that my end output would have max + 1 records, one for each possible score.

Any ideas ?

EDIT:

The database contains several tests and the maximum marks for the test is part of the test definition. So having a new table for storing all possible scores is not feasible. In the sense that whenever I create a new test with a new max marks, I need to ensure that the new table should be changed to contain these scores as well.

解决方案

SQL is good at working with sets of data values in the database, but not so good at sets of data values that are not in the database.

The best workaround is to keep one small table for the values you need to range over:

CREATE TABLE ScoreValues (score int);
INSERT INTO ScoreValues (score) 
  VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

Given your comment that you define the max marks of a test in another table, you can join to that table in the following way, as long as ScoreValues is sure to have values at least as high or higher than the greatest test's max marks:

SELECT v.score, COUNT(tr.score) AS 'NumStudents'
FROM ScoreValues v 
  JOIN Tests t ON (v.score <= t.maxmarks)
  LEFT OUTER JOIN TestResults tr ON (v.score = tr.score AND t.testid = tr.testid)
WHERE t.testid = 'mytestid'
GROUP BY v.score;

这篇关于MySQL查询 - 使用group-by时获取丢失的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！