php - 子查询计数

试图在第一个表的基础上JOIN2个表来计算种族，
学生可能出现超过一次。表2让学生用(student_schedule)出现1(bday)。我使用time by ID是因为有时我可能在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列（种族，%作为人口，总学生人口）。

为了比较学生中课程student's ethnicity的比例，
我想再加一个专栏，让我知道整个学校按种族划分的种族。换言之，32%的白人学生接受，而学校的白人人口是30%。数据来自表2（bday），其中列出了每个学生的种族。应该是

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

我被LEFT JOIN所需的Like 'AS%'卡在了GROUP function中，以便将其分为不同的类别COUNT
理想情况下，我的结果是。。。

任何帮助都将不胜感激。

最佳答案

你可以试试这样的：

<?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;

关于php - 子查询计数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36975524/

BDAY

php - 子查询计数