php - 使用 AVG() 嵌套 SQL 语句

Question

我想从中提取数据的三个表。其中一个是类(class)表，其中存储提供的类(class)列表，包括 ID、时间戳、培训师 ID、开始时间、上午/下午以及类(class)举办日期。培训师 ID 是一个外键，将其绑定(bind)到培训师表，我在其中根据培训师的唯一 ID 提取培训师名称。

这很好，但我还需要显示每堂课的平均出勤率。这涉及到一个统计表，该表具有一个类 ID 外键，该外键将类 ID 与该 session 的参与者数量一起放入一行中。我想返回每个类(class)每次类(class)的平均参与者人数。

下面是我的选择语句:

SELECT 
                        class_id AS id, 
                        class_name, 
                        class_trainerid,
                        class_starttime AS start, 
                        class_ampm AS ampm,
                        class_days AS days,
                        trainer_id AS trainid,
                        trainer_name,
                        stat_class AS sclass,
                        AVG(stat_students) as stat_students_avg
                    FROM 
                        $class_table
                        LEFT JOIN $trainer_table ON (class_trainerid = trainer_id)
                        LEFT JOIN stats ON (id = stat_students_avg)
                    GROUP BY
                        id

上面的代码可能表明我实际上不太知道如何做到这一点。我看过有关通过联接求平均值或在选择语句中使用选择语句的帖子，但我似乎无法将这些转化为我的问题。

编辑:这是类表架构:

`class_id` tinyint(8) NOT NULL AUTO_INCREMENT,
`class_datereated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`class_name` varchar(256) NOT NULL,
`class_trainerid` tinyint(8) NOT NULL,
`class_starttime` time NOT NULL,
`class_ampm` text NOT NULL,
`class_days` text NOT NULL,
PRIMARY KEY (`class_id`)

这是训练器架构

`trainer_id` tinyint(8) NOT NULL AUTO_INCREMENT,
'trainer_datecreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`trainer_name` varchar(256) NOT NULL,
`trainer_password` varchar(25) NOT NULL,
`trainer_email` varchar(256) NOT NULL,
`trainer_active` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`trainer_id`),
UNIQUE KEY `email` (`trainer_email`)

这是统计表架构:

`stat_id` int(8) NOT NULL AUTO_INCREMENT,
`stat_datecreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`stat_class` int(8) NOT NULL,
`stat_students` int(8) NOT NULL,
`stat_trainer` tinyint(8) NOT NULL,
`stat_class_date` date NOT NULL,
PRIMARY KEY (`stat_id`)

输出将是 php while 语句的一部分，结果如下:

echo "<table class='list'>";
        echo "<tr>";
        echo "<th>Class Name</th><th>Trainer</th><th>Ave</th><th>Edit</th><th>Delete</th>";
        echo "</tr>";
        while($row = mysql_fetch_assoc($class_result))
        {
            echo "<tr id='class_".$row["id"]."'>"; 
            echo "<td>".$row["class_name"]."</td>";
            echo "<td class='trainer-name'>".$row["trainer_name"]."</td>";
            echo "<td class='trainer-name'>".$row["stat_students_avg"]."</td>";
            echo "<td class='icon'><a href='javascript:void(0);' id='class_edit_".$row["id"]."'><span class='glyphicon glyphicon-edit'></span></a></td>";
            echo "<td class='icon'><a href='javascript:void(0);' id='class_delete_".$row["id"]."'><span class='glyphicon glyphicon-remove'></span></a></td>";
            echo "</tr>";
        }
        echo "</table>";

Answer 1

您的ON (id = stat_students_avg) 不对应相同的值。

统计表连接到类(class)表的唯一方法是类(class)表的 class_trainerid 和统计表的 trainer_id

你可以尝试这样的事情

SELECT 
    class_id ,
    class_name,
    class_trainerid,
    class_starttime as start,
    class_ampm as ampm,
    class_days as days,
    trainer_id as trainid,
    trainer_name,
    stat_class as sclass,
    AVG(stat_students) as stat_students_avg
FROM
    $class_table

     LEFT JOIN $trainer_table ON (class_trainerid = trainer_id)
      LEFT JOIN stats ON (trainer_id  = class_trainerid)
GROUP BY
      class_id