php - 子查询计数

Question

尝试JOIN 2 个表以根据第一个表(student_schedule) 计算种族，学生可能出现超过 1 次的地方。表 2 (bday) 仅让学生出现 1 次 time by ID 以及 student's ethnicity。我正在使用 LEFT JOIN，因为有时我可能会在 bday 表中缺少一个学生，他们不会被计算在内(因为他们的种族没有被宣布)。

 SELECT bday.Ethnicity, ROUND(COUNT(DISTINCT student_schedule.ID)/(SELECT  
 COUNT(DISTINCT student_schedule.ID) FROM student_schedule
 WHERE student_schedule.Course LIKE 'AS%')*100,2) AS "% of AS Population",  
 (SELECT COUNT(DISTINCT student_schedule.ID) FROM student_schedule AS 
 "Total Student Population") 
 FROM student_schedule LEFT JOIN bday ON student_schedule.ID=bday.ID WHERE    
 student_schedule.Course LIKE 'AS%' GROUP BY bday.Ethnicity
 ORDER BY COUNT(DISTINCT student_schedule.ID) DESC

结果是 3 列(种族、AS 人口百分比、学生总数)。

为了比较类(class) Like 'AS%' 的学生百分比，我想添加另一列，为我提供按种族分组的整个学校的种族。换句话说，32% 的白人学生参加了 AS，而学校的白人学生占 30%。数据来自表 2 (bday)，其中列出了每个学生的种族。应该是这样的

SELECT COUNT(bday.Ethnicity) 
FROM student_schedule
LEFT JOIN bday ON student_schedule.ID=bday.ID.

我坚持使用GROUP 函数 将COUNT 分成类别(白色、黑色、...)。

理想情况下，我的结果是...

如有任何帮助，我们将不胜感激。

Answer 1

你可以尝试这样的事情:

<?php
 //NOTE: I AM USING PDO FOR SIMPLICITY... BUT ANY OTHER DBAL WORKS AS WELL... 
//DATABASE CONNECTION CONFIGURATION:
defined("HOST")     or define("HOST",   "localhost");           //REPLACE WITH YOUR DB-HOST
defined("DBASE")    or define("DBASE",  "_TEST_");              //REPLACE WITH YOUR DB NAME
defined("USER")     or define("USER",   "root");                //REPLACE WITH YOUR DB-USER
defined("PASS")     or define("PASS",   "root");                //REPLACE WITH YOUR DB-PASS



try {
    $dbh            = new PDO('mysql:host='.HOST.';dbname='. DBASE,USER,PASS);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


    $sql            = 'SELECT COUNT(SS.ID) AS "Global_Student_Population" FROM student_schedule AS SS ';
    $stmt           = $dbh->prepare($sql);
    $stmt->execute();
    $result         = $stmt->fetchAll(PDO::FETCH_COLUMN);
    $intAllStudents = (int)$result[0];

    $sql            = " SELECT BD.Ethnicity, {$intAllStudents}  AS Global_Student_Population,
                        COUNT(DISTINCT SS.ID) AS Ethnic_Student_Population,
                        ROUND(COUNT(DISTINCT SS.ID)*100/{$intAllStudents}) AS '%_of_AS_Population'
                        FROM student_schedule AS SS
                        LEFT JOIN bday AS BD ON SS.ID=BD.ID
                        WHERE SS.Course LIKE 'AS%' GROUP BY BD.Ethnicity";

    $stmt           = $dbh->prepare($sql);
    $stmt->execute();
    $result         = $stmt->fetchAll();

      var_dump($result);
      var_dump($intAllStudents);

    //GARBAGE COLLECTION
    $dbh        = null;
}catch(PDOException $e){
    echo $e->getMessage();
}

我的 var_dump() 显示如下内容:

        array (size=3)
      0 => 
        array (size=8)
          'Ethnicity' => string 'Black' (length=5)
          0 => string 'Black' (length=5)
          'Global_Student_Population' => string '10' (length=2)
          1 => string '10' (length=2)
          'Ethnic_Student_Population' => string '3' (length=1)
          2 => string '3' (length=1)
          '%_of_AS_Population' => string '30' (length=2)
          3 => string '30' (length=2)
      1 => 
        array (size=8)
          'Ethnicity' => string 'Hispanic' (length=8)
          0 => string 'Hispanic' (length=8)
          'Global_Student_Population' => string '10' (length=2)
          1 => string '10' (length=2)
          'Ethnic_Student_Population' => string '1' (length=1)
          2 => string '1' (length=1)
          '%_of_AS_Population' => string '10' (length=2)
          3 => string '10' (length=2)
      2 => 
        array (size=8)
          'Ethnicity' => string 'White' (length=5)
          0 => string 'White' (length=5)
          'Global_Student_Population' => string '10' (length=2)
          1 => string '10' (length=2)
          'Ethnic_Student_Population' => string '2' (length=1)
          2 => string '2' (length=1)
          '%_of_AS_Population' => string '20' (length=2)
          3 => string '20' (length=2)

这是我的测试表定义: -- -- 表 bday 的表结构 --

    CREATE TABLE `bday` (
    `ID` int(11) NOT NULL,
      `Ethnicity` varchar(255) NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

    --
    -- Dumping data for table `bday`
    --

    INSERT INTO `bday` (`ID`, `Ethnicity`) VALUES
    (1, 'Black'),
    (2, 'Black'),
    (3, 'Black'),
    (4, 'White'),
    (5, 'Hispanic'),
    (6, 'White'),
    (7, 'Asian'),
    (8, 'Hispanic'),
    (9, 'White'),
    (10, 'Black');

    --
    -- Indexes for dumped tables
    --

    --
    -- Indexes for table `bday`
    --
    ALTER TABLE `bday`
      ADD PRIMARY KEY (`ID`);

    --
    -- AUTO_INCREMENT for dumped tables
    --

    --
    -- AUTO_INCREMENT for table `bday`
    --
    ALTER TABLE `bday`
      MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11;




    --
    -- Table structure for table `student_schedule`
    --

    CREATE TABLE `student_schedule` (
    `ID` int(11) unsigned NOT NULL,
      `Course` varchar(255) NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

    --
    -- Dumping data for table `student_schedule`
    --

    INSERT INTO `student_schedule` (`ID`, `Course`) VALUES
    (1, 'AS'),
    (2, 'AS'),
    (3, 'EN'),
    (4, 'EN'),
    (5, 'AS'),
    (6, 'AS'),
    (7, 'EN'),
    (8, 'EN'),
    (9, 'AS'),
    (10, 'AS');

    --
    -- Indexes for dumped tables
    --

    --
    -- Indexes for table `student_schedule`
    --
    ALTER TABLE `student_schedule`
      ADD PRIMARY KEY (`ID`);

    --
    -- AUTO_INCREMENT for dumped tables
    --

    --
    -- AUTO_INCREMENT for table `student_schedule`
    --
    ALTER TABLE `student_schedule`
      MODIFY `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11;