如何在MySQL中统计来自两个不同表的记录

Question

我有两个表:学生表和支付表。缴费表存储学生缴费记录。如何计算选定时段内未缴交学费的学生总人数？示例session: 2017/2018

我使用了下面的代码，我花了超过10秒的时间来给出结果。

SELECT   Count(students.registrationno)
FROM     students
WHERE    registrationno NOT IN
         (
          SELECT   DISTINCT payments.reg_no
          FROM payments
          WHERE payments.`session`='2017/2018'
         )

students
+----------------+------------+--------------+
| registrationno |   surname  |     firstname|
|----------------+------------+--------------|
| 100009         |   OGBODO   |     IPU      |
| 100014         |   IKE      |     JOSEPH   |
| 100033         |   JOHN     |     AJENE    |
+----------------+------------+--------------+

payments

+-------------+---------+-----------+ 
| payment_id  |  reg_no |   session |
|-------------+---------+-----------+
| 1           |  100009 | 2017/2018 |
| 2           |  180689 | 2017/2018 |
| 3           |  180680 | 2017/2018 |
+-------------+---------+-----------+

SELECT  COUNT(students.registrationno)
FROM    students
WHERE   registrationno NOT IN 
       (
        SELECT   DISTINCT payments.reg_no
        FROM     payments
        WHERE    payments.`session`='2017/2018'
       )

我希望输出不到10秒

Answer 1

使用连接

SELECT count(*) FROM students st
LEFT JOIN 
(SELECT * FROM payments WHERE session='2017/2018') pmt 
ON st.registrationno = pmt.reg_no WHERE pmt.reg_no IS NULL;

条件pmt.reg_no is null确保查询将返回相应会话中没有付款条目的学生记录。

还要创建适当的索引(例如，students.registrationno)实现了显著的性能提升。

Answer 2

正如评论中提到的，添加indexes可以帮助您提高性能。

下面是你可以尝试的另一种方法，但我相信它的效率会低于NOT IN

SELECT  count(s.registrationno)
FROM    students s
LEFT JOIN
        payments p
ON      p.reg_no  = s.registrationno
WHERE   p.reg_no IS NULL
AND P.session='2017/2018'

您也可以尝试使用NOT EXISTS