Groupwise最大多列

Question

我已经编写了一个查询，它允许我使用groupwise max方法获得最近的房间。但是，如何对多个列执行groupwise max？

我有下面的查询，子查询给我最近的房间，然后将它们与主表连接起来。现在我想挑选下一个将要发生的事件。

例如: MIN(DATEDIFF( DATEDIFF(日期，now()>0

SELECT name, date, t1.Room, descr, t1.D
FROM Events
JOIN
(
        SELECT Roomid, Room, Latitude, Longitude,
                ACOS(SIN((:lat))*SIN(RADIANS(Latitude)) + COS((:lat))*COS(RADIANS(Latitude))*COS(RADIANS(Longitude)-(:lon)))*(:R) AS D
        FROM Rooms
        WHERE Latitude>(:minLat) AND Latitude<(:maxLat)
                AND Longitude>(:minLon) AND Longitude<(:maxLon)
                AND ACOS(SIN((:lat))*SIN(RADIANS(Latitude)) + COS((:lat))*COS(RADIANS(Latitude))*COS(RADIANS(Longitude)-(:lon)))*(:R) < (:rad)
) AS t1 ON Events.roomid = t1.Roomid
GROUP BY Room
ORDER BY D

有人能给我一些关于如何做到这一点的建议吗？

谢谢!

附言:该事件应该是下一个发生的事件。我不使用ABS(MIN())，因为它会返回已经发生的事件。

Answer 1

假设D是您的日期，按日期差异排序，限制为1

SELECT name, date, t1.Room, descr, t1.D
FROM Events
JOIN
(
        SELECT Roomid, Room, Latitude, Longitude,
            ACOS(SIN((:lat))*SIN(RADIANS(Latitude)) + COS((:lat))*COS(RADIANS(Latitude))*COS(RADIANS(Longitude)-(:lon)))*(:R) AS D
    FROM Rooms
    WHERE Latitude>(:minLat) AND Latitude<(:maxLat)
            AND Longitude>(:minLon) AND Longitude<(:maxLon)
            AND ACOS(SIN((:lat))*SIN(RADIANS(Latitude)) + COS((:lat))*COS(RADIANS(Latitude))*COS(RADIANS(Longitude)-(:lon)))*(:R) < (:rad)
) AS t1 ON Events.roomid = t1.Roomid
ORDER BY DATEDIFF( D, now())
LIMIT 1