我有一张表，上面有房间的开始和结束日期。我写了这个SQL：
SELECT ic.invoice_id,
pccr.room_name,
DATEDIFF(end, start) AS 'Total Days',
pccr.start,
pccr.end,
FROM partners_campuses_courses_rooms AS pccr
JOIN invoices_courses AS ic ON pccr.course_id = ic.course_id
JOIN invoices i on ic.invoice_id = i.invoice_id
WHERE pccr.deleted_at IS NULL
AND pccr.end BETWEEN '2022-12-01' AND '2022-12-31'
这些房间在这段时间内是有效的。但是，当我这样过滤的时候，
SELECT ic.invoice_id,
pccr.room_name,
DATEDIFF(end, start) AS 'Total Days',
pccr.start,
pccr.end,
FROM partners_campuses_courses_rooms AS pccr
JOIN invoices_courses AS ic ON pccr.course_id = ic.course_id
JOIN invoices i on ic.invoice_id = i.invoice_id
WHERE pccr.deleted_at IS NULL
AND pccr.end BETWEEN '2022-12-01' AND '2022-12-30'
我没有结果。这些日期就像一个数组：
dates = ['2017-01-01', '2017-01-02', '2017-01-03', ... , '2022-12-30', '2022-12-31']
所以，你可以看到那个房间在2022-12-30是活动的，但我不能得到它。我怎么能那样做？
希望我能很好地表达自己。
如果需要在给定日期处于活动状态的行,请使用以下逻辑：
WHERE pccr.deleted_at IS NULL AND
pccr.end >= $date AND
pccr.start <= $date
如果您有一个日期范围,请使用：
WHERE pccr.deleted_at IS NULL AND
pccr.end >= $start_date AND
pccr.start <= $end_date
需要选择开始日期在期间结束之前、开始日期在期间开始之后的房间
SELECT ic.invoice_id,
pccr.room_name,
DATEDIFF(end, start) AS 'Total Days',
pccr.start,
pccr.end,
FROM partners_campuses_courses_rooms AS pccr
JOIN invoices_courses AS ic ON pccr.course_id = ic.course_id
JOIN invoices i on ic.invoice_id = i.invoice_id
WHERE pccr.deleted_at IS NULL
AND pccr.start < '2022-12-30' AND pccr.end > '2022-12-01'
