MySQL 查询 缺失记录,MySQL查询 - 使用group-by时获取丢失的记录

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;

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值