由student_id对任何课程的唯一标记进行SQL组行

I have a SQL table like this:

我有一个像这样的SQL表:

Student_id     mark         course
----------     --------     ---------
1              15           math
1              15           physics
2              15           math
2              16           physics

and want to produce this output:

并希望产生此输出:

Student_id     mark         count
----------     --------     ---------
1              15           2
2              15           1
2              16           1

count is number of single unique mark for each student (on any number of courses)

count是每个学生的单个唯一标记的数量(在任意数量的课程上)

1 个解决方案

#1

SELECT `student_id`, `mark`, count(1) AS `count`
FROM `the_table`
GROUP BY `student_id`, `mark`
;

If you want the results in a certain order, for now "GROUP BY" in MySql does it, but I've recently heard it's been deprecated in the latest versions so you might want to add an ORDER BY after the GROUP BY to future-proof a little.

如果您希望结果按特定顺序排列,那么现在MySql中的“GROUP BY”就可以了,但我最近听说它在最新版本中已被弃用,因此您可能希望在GROUP BY之后添加ORDER BY到未来 - 证明一点。