mysql - SQL:如何对别名计算列 ADD 添加计数进行排序？

Question

我是 SQL 新手，很难从数据库中获取信息，也许有人可以提供指导。我有

表格:学生

列:

name, notes, test_1_score, test_2_score, test_3_score, test_4_score, test_5_score,
test_6_score, test_7_score, test_8_score, test_9_score, test_10_score

我可以让下面的代码在没有GROUP BY中的points_achieved的情况下运行，但这就是我想要排序的。另外，如果不添加 IsNull，我就无法进行计算。

SELECT
name, 
notes, 
SUM (IsNull (test_1_score, 0) + IsNull (test_2_score, 0) + IsNull (test_3_score, 0) + IsNull (test_4_score, 0) + IsNull (test_5_score, 0) +IsNull ( test_6_score, 0) + IsNull (test_7_score, 0) + IsNull (test_8_score, 0) + IsNull (test_9_score, 0) + IsNull (test_10_score, 0)) AS points_acheived
FROM students
GROUP BY
points_achieved, name, notes;

最终，我想要一个简单的答案:名称points_achieved(添加已完成的#个测试的计数)…….notes

非常感谢任何帮助，谢谢。

Answer 1

试试这个:

SELECT
name, 
notes, 
SUM (IsNull (test_1_score, 0) + IsNull (test_2_score, 0) + IsNull (test_3_score, 0) + IsNull (test_4_score, 0) + IsNull (test_5_score, 0) +IsNull ( test_6_score, 0) + IsNull (test_7_score, 0) + IsNull (test_8_score, 0) + IsNull (test_9_score, 0) + IsNull (test_10_score, 0)) AS points_acheived
FROM students
GROUP BY
points_achieved, name, notes
order by 3;