MySQL查询以选择字段为NULL或字段日期值不大于另一个日期的日期

I have this MySQL query:

我有这个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

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的行但是还要选择空字段

For example:

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

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

这有效,但未列出sd.student_break_date中具有NULL值的行。

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

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

2 个解决方案

#1

Try this ::

试试这个 ::

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

OR you can try ::

或者你可以试试::

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

OR ::

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

#2

BETWEEN is an odd one for this bit of code

对于这段代码来说,BETWEEN是一个奇怪的代码

'2013-01-28' BETWEEN sd.student_startdate AND sd.student_enddate

You're actually doing sd.student_startdate>'2013-01-28' AND sd.student_enddate<'2013-01-28'. Notice there's no = in there so its actually excluding the 28th in both cases. If you want to actually include the 28th in your clause then a BETWEEN is not really any use.

你实际上是在做sd.student_startdate>'2013-01-28'AND sd.student_enddate <'2013-01-28'。请注意,那里没有=所以它实际上在两种情况下排除了第28位。如果你想在你的条款中实际包含第28个,那么BETWEEN实际上并没有任何用处。

As for you're question assuming the badly worded question means you want an sd.studen_break_date which is either null or less than the 28th then it'd be

至于你的问题,假设措辞严重的问题意味着你想要一个无效或低于28的sd.studen_break_date那么它就是

WHERE sd.student_startdate>'2013-01-28' AND sd.student_enddate<'2013-01-28' AND (sd.student_break_date is null OR sd.student_break_date<='2013-01-28')

#1