grafana mysql 时间段选择_mysql – 选择特定时间段内的所有可用项目

选择感兴趣的期间的关怀并分别限制此期间的开始/结束日期.这种限制将允许更容易计算“已预订”,即以后不是免费的日子.

SELECT ca.id,-- Limit start/end dates to period of interest,respectively

GREATEST (ca.startDate,'2017-05-16') AS `effectiveStartDate`,LEAST (ca.endDate,'2017-05-29') AS `effectiveEndDate`,ca.clientId

FROM carings ca

WHERE ca.startDate <= '2017-05-29' AND ca.endDate >= '2017-05-16';

接下来,计算预订天数：

DATEDIFF (DATE_ADD (LEAST (ca.endDate,'2017-05-29'),INTERVAL 1 DAY),GREATEST (ca.startDate,'2017-05-16'))

AS `effectiveDays`

最后,过滤掉整个期间预订的客户.这是通过比较完成的

>每个客户(GROUP BY)的预订天数总和

>整个期间的天数(具有sumDays< DATEDIFF(...)).

你也想要整个期间都没有预订的客户,我建议从客户表开始,“只是”LEFT JOIN(有效)关注：

SELECT cl.id,cl.name,IFNULL (SUM (eca.effectiveDays),0) AS `sumDays`

FROM clients cl

LEFT JOIN

(SELECT