如何计算表中在另一个表中具有特定属性的表中的行 - how to count rows in table that has specific property in another table

how to count rows in table that has specific property in another table

table1
--------------
| sn | class |
--------------
table2
----------------
| id | student |
----------------

所有这些都int因为sn表1链接到表2中的学生 sn，id是自动增加的。将数据插入表2时，学生列与表1中的sn相同

现在我想选择表 2 中的student，但只选择表1 中class为"3"的那些因此，我的语法是;

$count = mysql_query(SELECT student from table2 whose class in table1 =3)

这样我就可以通过说来计算它们

$quantity = mysql_num_rows($count)

现在我的问题是SQL是否也有这个谁的关键字，或者我该怎么做。

$count = mysql_query(SELECT student from table2 whose class in table1 =3)

您需要联接表才能正确过滤结果。

(1(这将为您提供3级的学生人数。

$count = mysql_query(
  'SELECT COUNT(t2.student) 
   FROM table2 t2 
   INNER JOIN table1 t1
     ON t1.sn = t2.student
     AND t1.class = 3'
);

(2(这将为您提供所有课程和每个班级的学生人数。

$count = mysql_query(
  'SELECT t1.class, COUNT(t2.student) 
   FROM table2 t2 
   INNER JOIN table1 t1
     ON t1.sn = t2.student
   GROUP BY t1.class
   ORDER BY t1.class'
);

(3(这将为您提供所有课程和学生列表。

$list = mysql_query(
  'SELECT t1.class, GROUP_CONCAT(t2.student SEPARATOR ',') 
   FROM table2 t2 
   INNER JOIN table1 t1
     ON t1.sn = t2.student
   GROUP BY t1.class
   ORDER BY t1.class'
);

您应该连接这两个表，并将结果限制为具有 table1 的表.class = 3

SELECT
    student
FROM
    table2 a
    JOIN table1 b ON (a.student = b.sn)
WHERE b.class = 3

如果你想要一个计数，你也可以通过使用聚合函数通过SQL来完成

SELECT
    COUNT(student)
FROM
    table2 a
    JOIN table1 b ON (a.student = b.sn)
WHERE b.class = 3