我有 2 个表客户和注释,在注释表中我记录了客户端的所有活动。我正在尝试做的是查询注释表,以查找哪些客户端在过去 30 天内没有任何活动。
在昨晚和今天早上喝了一瓶好药水后,我一直无法找到可行的解决方案。有什么建议吗?
SELECT `c`.`ClientID` , `c`.`CompanyName` , MAX( `NtWhen` )
FROM `clients` AS `c`
JOIN `client-notes` AS `n` ON ( `c`.`ClientID` = `n`.`NtClient` )
WHERE `NtText` = 'Viewed Client Details'
AND MAX(`NtWhen`) < '2012-04-10'
GROUP BY `NtClient`
ORDER BY MAX(`NtWhen`) DESC
基本原则是您需要按客户端对笔记表进行分组,然后仅过滤最近日期在 30 天前的组(即使用 HAVING
SELECT NtClient
FROM `client-notes`
GROUP BY NtClient
HAVING MAX(NtWhen) < DATESUB(NOW(), 30)
如果您需要公司名称等,您可以像以前一样加入clients
表。
SELECT `c`.`ClientID` , `c`.`CompanyName` , MAX( `NtWhen` )
FROM `clients` AS `c`
JOIN `client-notes` AS `n` ON ( `c`.`ClientID` = `n`.`NtClient` )
WHERE `NtText` = 'Viewed Client Details'
GROUP BY `NtClient`
HAVING MAX(`NtWhen`) < '2012-04-10'
ORDER BY MAX(`NtWhen`) DESC;