mysql date_add 在程序中添加2次

Question

我想在一个过程中使用 Date_add 2 次，但它返回 0 行(虽然它应该返回行)

流程如下

 select av.*, ap.*,c.* from tbl_available av 
 left join tbl_appointment ap on av.avHours = ap.appointmenttime 
 and ap.calendarid = kalenderId 
 and ap.appointmentdate = DATE_ADD(dag, INTERVAL 6 DAY)
 left join tbl_client c on ap.clientid = c.clientid
 where av.avCalendarId = KalenderId 
 and av.avDays = DayOfweek(DATE_ADD(dag, INTERVAL 6 DAY)) 
 order by avHours;

无需 date_add 即可工作

提前致谢!

//编辑

我现在拥有的:

select av.*, ap.*,c.*, ab.absentid from tbl_available av 
left join tbl_appointment ap on av.avHours = ap.appointmenttime 
and ap.calendarid = kalenderId 
and ap.appointmentdate BETWEEN dag AND DATE_ADD(dag, INTERVAL 6 DAY)
and (av.avDays = DayOfweek(ap.appointmentdate) OR ap.appointmentdate IS NULL)
left join tbl_client c on ap.clientid = c.clientid
left join tbl_absent ab on av.avHours = ab.ababsent
and ab.abHoliday = dag
and ab.abCalendarID = kalenderId
where av.avCalendarId = kalenderId 
order by avDays,avHours;

但是 ab.absentid 没有被获取，这是为什么呢？ :(

Answer 1

我不确定到底你想要什么，但是整整一周，尝试如下:

select av.*, ap.*,c.* from tbl_available av 
left join tbl_appointment ap on av.avHours = ap.appointmenttime 
and ap.calendarid = kalenderId 
and ap.appointmentdate BETWEEN dag AND DATE_ADD(dag, INTERVAL 6 DAY)
and (av.avDays = DayOfweek(ap.appointmentdate) OR ap.appointmentdate IS NULL)
left join tbl_client c on ap.clientid = c.clientid
where av.avCalendarId = KalenderId 
order by avHours;

我使用 BETWEEN 指定 ap.appointmentdate 的日期范围。 av.avDays 更改为与 ap.appointmentdate 相关，或者还显示没有约会的行(假设此行为是因为您有 LEFT JOIN > 关于tbl_appointment)。我省略了 DayOfweek(dag) ... 因为您正在查看一整周，所以这是多余的。