mysql - 查询使用空值或不等于来提取结果

Question

我有一个像这样的 mysql 查询

SELECT s.admission_number, s.student_name, c.class, m.month, 
       f.fee, fr23.amount_received
FROM students s
LEFT JOIN fee_receipts fr ON s.id = fr.admission_number
LEFT JOIN fee_receipts_23_repeat fr23 ON fr.id = fr23.parent_id
JOIN class c ON s.class = c.id
LEFT JOIN fee f ON f.id = fr23.fee
LEFT JOIN months m ON m.id = fr23.month

显示的结果是这样的？

ad_num  student_name class   month    fee   amount_received 
779     A Jain      Nur     January Tuition   Fee   575.00
808     A Gupta     Nur     January Tuition   Fee   0.00
821     A Mohanty   Nur     NULL    NULL            NULL
818     ATiwari     Nur     FebruaryTuition   Fee   575.00
826     D Mishra    Nur     NULL    NULL      NULL
813     G Kaur      Nur     NULL    NULL      NULL  NULL
809     P Palai     Nur     NULL    NULL      NULL
822     S Agrawal   KG1     December Tuition  Fee   575.00
773     S Garg      KG1     NULL    NULL            NULL

现在我试图通过排除已付费的学生来按类(class)获取未付费学生的列表，但没有得到正确的结果。我添加到上面查询的条件是这样的

where fr23.amount_received is NULL and c.class = 'Nur' and m.month != 'January'

我希望结果会列出一月份未付款的所有学生，但它给了我一个空的结果集。

实际上，我试图找到没有付款条目的学生，这意味着月份、费用和收到的金额列将为空值。

我不太确定我是否采取了正确的方法？

Answer 1

将您的条件移至 JOIN ON 条件:

SELECT s.admission_number, s.student_name, c.class, m.month, 
       f.fee, fr23.amount_received
FROM students s
LEFT JOIN fee_receipts fr ON s.id = fr.admission_number
LEFT JOIN fee_receipts_23_repeat fr23 ON fr.id = fr23.parent_id
    AND fr23.amount_received is NULL   -- Moved to here
JOIN class c ON s.class = c.id
LEFT JOIN fee f ON f.id = fr23.fee
LEFT JOIN months m ON m.id = fr23.month
    AND (m.month != 'January' OR m.month IS NULL) -- Added IS NULL check