MySQL填写缺少的日期 [英] Mysql filling in missing dates

问题描述

我有以下查询

SELECT * 
FROM attend
RIGHT OUTER JOIN noattend ON attend.date = noattend.date2
WHERE attend.date
BETWEEN '2010-02-01'
AND '2010-04-01'
AND attend.customerid =1
ORDER BY date DESC 
LIMIT 0 , 30

参加者是带有customerid的表 noattend是每个日期(date2)都有一行的表，我按照其他问题的建议进行了正确的外部连接，以在其中没有出席记录但仍然没有填写空值的情况下创建值 非常感谢任何帮助

Attend is the table with customerid noattend is the table with a row for each date (date2) I followed the advice in other questions to right outer join it to create values where there is no record in attend but it still isn't filling in the empties any help much appreciated

推荐答案

如果您说noattend是每个日期都有一行的表，则应在WHERE子句中使用它:

If you say that noattend is a table with a row for each date, you should use it in WHERE clause:

WHERE noattend.date2 BETWEEN (.....

我认为使用LEFT JOIN更清楚:

And I think it's more clear to use LEFT JOIN :

SELECT * 
FROM noattend
LEFT OUTER JOIN attend ON (attend.date = noattend.date2 AND attend.customerid =1)
WHERE noattend.date2
BETWEEN '2010-02-01'
AND '2010-04-01'
ORDER BY date DESC 
LIMIT 0 , 30

这篇关于MySQL填写缺少的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！