我有一个员工表（ID）和更改其状态时的表（日期，状态，ID（FK））。 我需要显示根据指定日期的状态排序的员工列表
employees
ID, Name
1, Jack
2, Ralf
3, Jenny
changes
IDchange, Date, Status, ID
1, 2015-01-01, 2, 1 //Jack started in 2015-1-1 with status 2
2, 2015-03-01, 1, 2 //Ralf started in 2015-3-1 with status 1
3, 2015-04-01, 1, 3 //Jenny started in 2015-4-1 with status 1
4, 2015-08-01, 2, 2 //Ralf change status to 2 in 2015-8-1
5, 2015-10-01, 3, 2 //Ralf change status to 3 in 2015-10-1
6, 2016-04-01, 4, 1 //Jack change status to 4 in 2016-04-1
我尝试了任何日期$My_Date，但我不知道如何修复所有更改，它只检查最后日期更改
<?php
$sql2="SELECT *, Status
FROM employees s
JOIN (
SELECT MAX(Date) max_date, ID, Status
FROM changes sml
WHERE date <= '".$My_Date."'
GROUP BY ID
) sml ON (sml.ID = s.ID)
ORDER BY Status, Name ASC";
$result2 = MySQL_Query($sql2);
... ?>
您可以使用相关的子查询来获取每个员工的每ID status：
SELECT ID, Name, (SELECT Status
FROM changes AS c
WHERE e.ID = c.ID AND date <= ?
ORDER BY date DESC LIMIT 1) AS Status
FROM employees AS e
ORDER BY Status, Name ASC
子查询将返回更接近参数化日期值的当前员工的状态。
我们可以使用简单的连接查询来完成。由于我们将date作为参数传递，因此无需计算max(date)，示例如下：
select e.name, s.date, s.status
from employee e join status s on e.id = s.id
where s.date = ?
order by s.status desc