PostgreSQL GROUP BY:另一个MAX的SELECT列,第三列= x [英] PostgreSQL GROUP BY: SELECT column on MAX of another WHERE a third column = x
问题描述
假设我们在PostgreSQL中有两个表:
Let's suppose we have two tables in PostgreSQL:
表 citizens
Table "citizens"
country_ref citizen_name entry_date
-----------------------------------------------------
0 peter 2013-01-14 21:00:00.000
1 fernando 2013-01-14 20:00:00.000
0 robert 2013-01-14 19:00:00.000
3 albert 2013-01-14 18:00:00.000
2 esther 2013-01-14 17:00:00.000
1 juan 2013-01-14 16:00:00.000
3 egbert 2013-01-14 15:00:00.000
1 francisco 2013-01-14 14:00:00.000
3 adolph 2013-01-14 13:00:00.000
2 emilie 2013-01-14 12:00:00.000
2 jacques 2013-01-14 11:00:00.000
0 david 2013-01-14 10:00:00.000
表国家
country_id country_name country_group
-------------------------------------------
0 england 0
1 spain 0
2 france 1
3 germany 1
现在,我想获取给定country_group中每个国家/地区在公民表上最后输入的公民。
Now I want to obtain the last entered citizen on the "citizens" table for each country of a given country_group.
到目前为止,我最好的尝试是这个查询(我们称它为Query_1):
My best try so far is this query (Let's call it Query_1) :
SELECT country_ref, MAX(entry_date) FROM citizens
LEFT JOIN countries ON country_id = country_ref
WHERE country_group = 1 GROUP BY country_ref
输出:
country_ref max
---------------------------------
3 2013-01-14 18:00:00
2 2013-01-14 17:00:00
所以我可以这样做:
SELECT citizen_name FROM citizens WHERE (country_ref, entry_date) IN (Query_1)
...这将为我提供所需的输出: albert
和 esther
。
... which will give me the output I'm looking for: albert
and esther
.
但是我更愿意实现单个查询中。我想知道是否有可能?
But I'd prefer to achieve this in a single query. I wonder if it's possible?
推荐答案
这应该是最简单,最快的:
This should be simplest and fastest:
SELECT DISTINCT ON (i.country_ref)
i.citizen_name
FROM citizens i
JOIN countries o ON o.country_id = i.country_ref
WHERE o.country_group = 1
ORDER BY i.country_ref, i.entry_date DESC
只需将它们添加到 SELECT
列表中,就可以轻松地从两个表中返回更多列。
SQL小提琴。
You can easily return more columns from both tables by simply adding them to the SELECT
list.
SQL Fiddle.
详细信息,此相关答案中的链接和说明:
Details, links and explanation in this related answer:
- Select first row in each GROUP BY group?
这篇关于PostgreSQL GROUP BY:另一个MAX的SELECT列,第三列= x的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!