sql用于查找组中的最近记录 | sql用于查找组中的最近记录

本文介绍了sql用于查找组中的最近记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有这样的表

  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 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 = Xin 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.

MySQLSQL Query, Selecting 5 most recent in each group

SQL SERVERSQL 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用于查找组中的最近记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！