MySQL查询以选择字段为NULL或字段日期值不大于另一个日期的日期 [英] MySQL query to select dates where field is NULL or field date value is not greater than another date

问题描述

我有这个 MySQL 查询:

SELECT DISTINCT
    *
FROM
    students s
        LEFT JOIN
    (SELECT 
        *
    FROM
        studentabsences a1
    WHERE
        (a1.absence_date = '2013-01-28')) a ON a.absence_student_id = s.student_id
        LEFT JOIN
    (SELECT 
        *
    FROM
        studentabsencenotes b1
    WHERE
        (b1.absence_note_date = '2013-01-28')
    GROUP BY absence_note_student_id) b ON b.absence_note_student_id = s.student_id
        INNER JOIN
    studentdates sd ON sd.student_id = s.student_id
        INNER JOIN
    coursecategory ctc ON s.student_course_category_id = ctc.category_id
WHERE
    '2013-01-28' BETWEEN sd.student_startdate AND sd.student_enddate
ORDER BY s.student_lastname , s.student_firstname

现在我想添加另一个WHERE clause 来选择没有更大sd.student_break_date 比 2013-01-28 还要选择空字段

Now I would like to add another WHERE clause to select rows that has not a greater sd.student_break_date than 2013-01-28 but also select empty fields

例如:

WHERE '2013-01-28' >= sd.student_break_date

这有效，但未列出在 sd.student_break_date 中具有 NULL 值的那些行.

This works but those rows that has NULL values in the sd.student_break_date are not listed.

很乱吧?我的问题是:如何选择日期不高于 2013-01-28 的空字段和字段

Messy, huh? My question is: How can I select fields that are empty AND fields where date is not higher than 2013-01-28

推荐答案

试试这个 ::

WHERE sd.student_break_date is null OR  '2013-01-28' >= sd.student_break_date

或者你可以试试 ::

WHERE sd.student_break_date is null OR  sd.student_break_date<'2013-01-28'

或 ::

WHERE IFNULL(sd.student_break_date,'2013-01-27') <'2013-01-28'

这篇关于MySQL查询以选择字段为NULL或字段日期值不大于另一个日期的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！