php mysql根据多个条件获取最新记录并对其进行计数

Question

我有一张桌子

id | staff Name | adress
-------------------------
1 | Mr.A        | Any Address
2 | Mr. B       | Any Address

第二表- employment_history

eid | staff_id | school_id | type       | grade | date_of_appointmet
--------------------------------------------------------------------
1   | 1         | 1        |Promotion   | 17    | 2012-12-12
2   | 1         | 2        |promotion   | 18    | 2013-2-2
3   | 2         | 2        |appointment | 17    | 2013-3-3
 and so on tables moves

现在的问题是

• 我想从员工表中找到那个人的最新工作。
• 我如何计算在school_id 1工作的17级员工中有多少人(记得staff_id 1 (mr.a)现在已经晋升到18，现在在school_id 2工作)。

Answer 1

select staff_id, max(date_of_appointment) as date_of_appointment
from employment_history
group by staff_id

此查询将返回每个staff_id的最新人员记录。将其转换为子查询并连接到雇用历史记录表中。

Select grade, count(1) 
from
(select staff_id, max(date_of_appointment) as date_of_appointment
from employment_history
group by staff_id) a
inner join employment_history e on e.staff_id = a.staff_id and a.date_of_appointment = e.date_of_appointment
 group by grade

这个解决方案假设staff_id + date_of_appointment是唯一的key...if --在其中一个staff_id有一个日期的多个雇用历史条目的情况下，这是行不通的。您需要一些逻辑才能使‘最近就业历史记录条目’返回data...if staff_id + max(date_of_appointment)的唯一组合，这并不是唯一的，您需要在返回唯一数据的'a‘子查询中找到逻辑。

Answer 2

像这样的东西呢

SELECT *
FROM employment_history eh1
WHERE eh1.date_of_employment = (
    SELECT max(eh2.date_of_employment)
    FROM staff s
    JOIN employment_history eh2 ON s.id = eh2.staff_id
    WHERE s.id = ?
)

替换?，或酌情使用bind_param()。