如何按2个字段分组并同时按日期排序? [英] How to group by 2 fields and order by date at the same time?
问题描述
我在employees
表中有3个字段:personal_id
,personal_id_type
和date_registered
.
I have 3 fields in an employees
table: personal_id
, personal_id_type
, and date_registered
.
我需要找到所有行,每个personal_id + personal_id_type
行(在这里我可以使用GROUP),但是由于同一personal_id + personal_id_type
可能有很多条目(因为员工可以辞职并以后再被雇用),我只需要获取最新的条目(order by date_registered DESC
).
I need to find all the rows, one row for each personal_id + personal_id_type
(here is where I think I can use a GROUP), but since there may be many entries for the same personal_id + personal_id_type
(because employees can quit and get hired again later), I need to fetch only the newest entry (order by date_registered DESC
).
从概念上讲这是正确的吗?按2个字段分组的语法是什么?我会满足我的需求吗?
Is this conceptually correct? What would be the syntax for grouping by 2 fields? Will I achieve what I need with that?
很抱歉,实际上有很多字段,所以我需要最新的行,而不仅仅是最新的日期.
I'm sorry, actually there are a lot of fields, so I need the newest row, not only the newest date.
推荐答案
在子查询中查找每个(personal_id, personal_id_type)
组合的最新日期,然后加入原始表:
Find the newest date for every (personal_id, personal_id_type)
combination in a subquery, then join to the original table:
SELECT
e.*
FROM
employees e
JOIN
( SELECT
personal_id
, personal_id_type
, MAX(date_registered) AS latest_date_registered
FROM
employees
GROUP BY
personal_id
, personal_id_type
) AS grp
ON grp.personal_id = e.personal_id
AND grp.personal_id_type = e.personal_id_type
AND grp.latest_date_registered = e.date_registered
这篇关于如何按2个字段分组并同时按日期排序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!