sql用于查找组中的最近记录 [英] sql for finding most recent record in a group

问题描述

我有这样的表

  table_id | series_id | revision_id | year 
 ------------------------------------------ 
 1 | 1 | 1 | 2010 
 2 | 2 | 1 | 2009 
 3 | 2 | 2 | 2008 
 4 | 2 | 2 | 2009 
 5 | 2 | 3 | 2010 
 6 | 2 | 3 | 2008 
 7 | 3 | 2 | 2007 
 8 | 3 | 3 | 2010 
 9 | 3 | 3 | 2010 
 

当series = X时，我需要查找按revision_id分组时的max
in postgresql。

例如：当x = 2时，我期望这个结果

  table_id | series_id | revision_id | year 
 ------------------------------------------ 
 2 | 2 | 1 | 2009 
 4 | 2 | 2 | 2009 
 5 | 2 | 3 | 2010 
  

这不起作用

  SELECT * from table 
 WHERE series_id = 2 
和table_id IN（
 SELECT table_id 
 FROM table 
 WHERE series_id = 2 
 GROUP by revision 
 ORDER BY年DESC 
）
  

因为我需要返回一个字段，我没有按

分组

这里有一些类似的问题在其他SQL风格。

MySQL
SQL查询，每个组中选择最近5个

SQL SERVER
SQL Server - 如何为每个用户选择最近的记录？

$ b

  SELECT table_id，series_id， revision_id，year 
 FROM tableName t INNER JOIN 
（SELECT revision_id，max（year）AS year 
 FROM tableName 
 WHERE series_id = 2 
 GROUP BY revision_id）s 
 USING（revision_id，year）
 WHERE series_id = 2; 
  

结果：

 code> table_id | series_id | revision_id |年
 ---------- + ----------- + ------------- + ------ 
 2 | 2 | 1 | 2009 
 4 | 2 | 2 | 2009 
 5 | 2 | 3 | 2010 
（3 rows）
  

i have a table like such

table_id | series_id | revision_id | year
------------------------------------------
1        | 1         | 1           | 2010
2        | 2         | 1           | 2009
3        | 2         | 2           | 2008
4        | 2         | 2           | 2009
5        | 2         | 3           | 2010
6        | 2         | 3           | 2008
7        | 3         | 2           | 2007
8        | 3         | 3           | 2010
9        | 3         | 3           | 2010

I need to find the table_id for the max(year) when grouped by revision_id when series = X in postgresql.

eg : when x =2 i expect this result

table_id | series_id | revision_id | year
------------------------------------------
2        | 2         | 1           | 2009
4        | 2         | 2           | 2009
5        | 2         | 3           | 2010

this doesn't work

SELECT * from table 
WHERE series_id = 2 
AND table_id IN (
    SELECT table_id 
    FROM table 
    WHERE series_id = 2 
    GROUP by revision 
    ORDER BY year DESC
)

I cannot figure out a way to do this in postgresql since I need to return a field i am not grouping by

here are some similar problems in other SQL flavors.

MySQL SQL Query, Selecting 5 most recent in each group

SQL SERVER SQL Server - How to select the most recent record per user?

解决方案

Query:

SELECT table_id, series_id, revision_id, year
FROM tableName t INNER JOIN
    (SELECT revision_id, max(year) AS year
    FROM tableName 
    WHERE series_id = 2 
    GROUP BY revision_id) s
USING (revision_id, year)
WHERE series_id = 2;

Result:

 table_id | series_id | revision_id | year
----------+-----------+-------------+------
        2 |         2 |           1 | 2009
        4 |         2 |           2 | 2009
        5 |         2 |           3 | 2010
(3 rows)

这篇关于sql用于查找组中的最近记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！