gpt4 book ai didi

MySQL 查找六个月或更长时间没有预约的所有人员

转载 作者:行者123 更新时间:2023-11-29 09:09:28 25 4
gpt4 key购买 nike

大家好,我有一个约会表,其中除其他字段外还有一个记录约会日期的 DATE 字段。显示创建语句如下。

 | groomappointments | CREATE TABLE `groomappointments` (
`gapmtDate` date NOT NULL,
`gapmtClient` int(11) NOT NULL,
`gapmtUser` int(11) NOT NULL,
`gapmtStatus` int(11) NOT NULL DEFAULT '1',
`gapmtSTime` time NOT NULL,
`gapmtETime` time NOT NULL,
`gapmtPet` int(11) DEFAULT NULL,
`gapmtService` int(11) DEFAULT NULL,
`gapmtTracker` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`gapmtDate`,`gapmtClient`,`gapmtUser`,`gapmtStatus`,`gapmtSTime`),
KEY `gappPet` (`gapmtPet`),
KEY `gappClient` (`gapmtClient`),
KEY `gappSrve` (`gapmtService`),
KEY `gappStat` (`gapmtStatus`),
KEY `gappUsr` (`gapmtUser`),
KEY `gapmtTracker` (`gapmtTracker`),
CONSTRAINT `gappClient` FOREIGN KEY (`gapmtClient`) REFERENCES `clients` (`clientid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `gappPet` FOREIGN KEY (`gapmtPet`) REFERENCES `pets` (`petID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `gappSrve` FOREIGN KEY (`gapmtService`) REFERENCES `groomservices` (`groomServicesID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `gappStat` FOREIGN KEY (`gapmtStatus`) REFERENCES `aptstatus` (`aptStatusID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `gappUsr` FOREIGN KEY (`gapmtUser`) REFERENCES `users` (`userID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 |

我正在尝试查询数据库以查找最后一次约会记录在六个月或更长时间之前的所有客户,但我无法找出正确的查询。

我尝试了以下查询,它将为我提供过去记录 6Mts+ 的所有记录,但包括上周、上个月等进行过预约的客户。

 mysql> select groomappointments.gapmtDate, clients.firstname, clients.lastname
-> from groomappointments,clients
-> WHERE date_sub(CURDATE(), INTERVAL 6 MONTH)>gapmtDate
-> AND clients.clientid = groomappointments.gapmtClient;

非常感谢任何想法。

最佳答案

select clients.firstname, clients.lastname, groomappointments.gapmtDate
from clients join groomappointments on clients.clientid = groomappointments.gapmtClient
where clients.clientid in (
select gapmtClient from groomappointments
group by gapmtClient
where date_sub(CURDATE(), INTERVAL 6 MONTH) > gapmtDate
)

关于MySQL 查找六个月或更长时间没有预约的所有人员,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/6065538/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com