php - 如何从 MySQL/PHP 中获取每个科目的科目名称和错过的作业总数

Question

我在 MySQL 中有以下表格。(我正在使用 php/mysql)

CREATE TABLE `hw_subjects` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
)  ;

...
INSERT INTO `hw_subjects` VALUES(4, 'Design');
INSERT INTO `hw_subjects` VALUES(5, 'Drama');
...
INSERT INTO `hw_subjects` VALUES(11, 'Humanities');
...
INSERT INTO `hw_subjects` VALUES(13, 'Mathematics');
...

following shows students missed homework

CREATE TABLE `hw_homeworkmissed` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `studentid` int(10) NOT NULL,
  `subjectid` int(10) NOT NULL,
  `assignment_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `teacherid` int(10) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ;


INSERT INTO `hw_homeworkmissed` VALUES(1, 29, 5, '5E', 20, '2012-10-18 13:58:40');
INSERT INTO `hw_homeworkmissed` VALUES(2, 15, 5, '32B', 20, '2012-10-18 13:59:54');
INSERT INTO `hw_homeworkmissed` VALUES(3, 29, 4, 'Q2A', 20, '2012-10-18 17:53:46');
INSERT INTO `hw_homeworkmissed` VALUES(4, 29, 11, '6E', 20, '2012-10-02 20:06:39');
INSERT INTO `hw_homeworkmissed` VALUES(5, 29, 11, 'C15', 20, '2012-10-16 20:06:30');
INSERT INTO `hw_homeworkmissed` VALUES(6, 15, 11, '7A', 20, '2012-09-19 20:08:05');
INSERT INTO `hw_homeworkmissed` VALUES(7, 29, 5, '3B', 20, '2012-09-14 20:08:12');
INSERT INTO `hw_homeworkmissed` VALUES(8, 29, 13, '6E', 32, '2012-10-18 20:23:46');
...

我想显示一个学生(假设 studentid ==29)错过了作业。所以我需要科目和每个科目中错过的作业总数。 (我正在使用谷歌图表)

Mathematics,3
Science, 1
Humanities, 2 
etc.

我该怎么做？提前致谢。

更新:

我可以获得以下数组。但是我不确定我是否可以使用它。我加入了 studentid =29 的两个表

Array
(
    [0] => Array
        (
            [id] => 5
            [studentid] => 29
            [subjectid] => 5
            [assignment_name] => 5E
            [teacherid] => 20
            [date] => 2012-10-18 13:58:40
            [name] => Drama
        )

    [1] => Array
        (
            [id] => 4
            [studentid] => 29
            [subjectid] => 4
            [assignment_name] => Q2A
            [teacherid] => 20
            [date] => 2012-10-18 17:53:46
            [name] => Design
        )

    [2] => Array
        (
            [id] => 11
            [studentid] => 29
            [subjectid] => 11
            [assignment_name] => 6E
            [teacherid] => 20
            [date] => 2012-10-02 20:06:39
            [name] => Humanities
        )

    [3] => Array
        (
            [id] => 11
            [studentid] => 29
            [subjectid] => 11
            [assignment_name] => C15
            [teacherid] => 20
            [date] => 2012-10-16 20:06:30
            [name] => Humanities
        )

    [4] => Array
        (
            [id] => 5
            [studentid] => 29
            [subjectid] => 5
            [assignment_name] => 3B
            [teacherid] => 20
            [date] => 2012-09-14 20:08:12
            [name] => Drama
        )

    [5] => Array
        (
            [id] => 13
            [studentid] => 29
            [subjectid] => 13
            [assignment_name] => 6E
            [teacherid] => 32
            [date] => 2012-10-18 20:23:46
            [name] => Mathematics
        )

)
....
....

Answer 1

在您的 SELECT 和 GROUP BY subjectid 中使用 COUNT(id)

SELECT 
    hw_subjects.name, 
    COUNT(hw_homeworkmissed.id) 
FROM hw_subjects, hw_homeworkmissed 
WHERE hw_subjects.id= hw_homeworkmissed.subjectid 
GROUP BY subjectid 
ORDER BY hw_subjects.name