获取MySQL数据库中每个ID的最新(日期)记录 - Get the latest (date) record for every ID in the MySQL database

Get the latest (date) record for every ID in the MySQL database

我正在将住在房子里的人添加到数据库中。有人在8月1日搬进1号房子，有人在8月5日搬进4号房子，有人在8月10日再次搬进1号房子。我想在页面上显示的是住在房子里的人，例如8月9日和8月11日。

数据库示例:

employee----house-date
1-----------2-----01-01-2012
5-----------7-----01-01-2012
1-----------1-----01-08-2012
3-----------4-----01-01-2012
1-----------8-----01-09-2012

第一次尝试

SELECT employee FROM habitants WHERE house='$house' AND date <= '$today' ORDER BY date DESC, employee ASC

但这显示了一个人住在多个房子里(例如，当雇员1有时换房子时)

Then I try

SELECT employee FROM habitants WHERE house='$house' AND date <= '$today' ORDER BY date DESC, employee ASC LIMIT 1

但是每栋房子只有一个人

然后我在MAX上找到了一些东西，所以我尝试了

SELECT employee, MAX(date), house FROM habitants WHERE house='$house' AND date <= '$today' ORDER BY date DESC, employee ASC GROUP BY house

这导致了以下错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY huis' at line 1

谁能告诉我怎么做这个正确的方法?谢谢!

一种方法是使用子查询和join:

SELECT h.*
FROM habitants h JOIN
     (SELECT house, MAX(date) as maxd
      FROM habitants
      WHERE date <= '$today'
      GROUP BY house
     ) hh
     ON hh.house = h.house and hh.maxd = h.date
WHERE house = '$house';

您也可以将其写为相关子查询:

SELECT h.*
FROM habitants h
WHERE h.house = $house AND
      h.date = (SELECT MAX(date)
                FROM habitants h2
                WHERE h2.house = h.house and h2.date <= CURDATE()
               );

注意，在大多数情况下，您不需要传入当前日期。CURDATE()提供当前日期(NOW()为当前时间)。

编辑:

如果您正在查看员工在哪里，那么您需要查找每个员工的最大日期，而不是house:

SELECT h.*
FROM habitants h
WHERE h.house = $house AND
      h.date = (SELECT MAX(date)
                FROM habitants h2
                WHERE h2.employee = h.employee and h2.date <= CURDATE()
               );

,或者另外:

SELECT h.*
FROM habitants h JOIN
     (SELECT employee, MAX(date) as maxd
      FROM habitants
      WHERE date <= '$today'
      GROUP BY employee
     ) hh
     ON hh.employee = h.employee and hh.maxd = h.date
WHERE h.house = '$house';