mysql - SQL选择不在未来的最大记录

Question

我的表结构是这样的

tblclients

clientid
clientname

tblfacilities

facilityid
facilityname

tblclientfacilities

clientid
facilityid
moveindate

我允许我的用户输入 future 的日期作为入住日期，我想做的是选择我的客户所在的当前设施。我已经尝试过

SELECT 
 clientid,
 facilityid,
 moveindate

FROM 
 tblclientfacilities
WHERE 
 MAX(tblclientfacilities.moveindate) <= now()
GROUP BY
  tblclientfacilities.clientid

显然那是行不通的。有人能让我走上正轨吗？谢谢!

Answer 1

SELECT  clientId,
        (
        SELECT  facilityId
        FROM    tblclientfacilities cf
        WHERE   cf.clientId = c.clientId
                AND cf.moveDate <= NOW()
        ORDER BY
                cf.clientId DESC, moveDate DESC
        LIMIT 1
        ) AS currentFacility
FROM    tblclients c

在 tblclientfacilities (clientId, moveDate, facilityId) 上创建一个唯一索引以使其快速运行。