Oracle SQL GROUP BY“not a GROUP BY expression”帮助您

I have a table some_table like

我有一张桌子，像。

+--------+----------+---------------------+-------+
| id     | other_id | date_value          | value |
+--------+----------+---------------------+-------+
| 1      | 1        | 2011-04-20 21:03:05 | 104   |
| 2      | 2        | 2011-04-20 21:03:04 | 229   |
| 3      | 3        | 2011-04-20 21:03:03 | 130   |
| 4      | 1        | 2011-04-20 21:02:09 | 97    |
| 5      | 2        | 2011-04-20 21:02:08 | 65    |
| 6      | 3        | 2011-04-20 21:02:07 | 101   |
| ...    | ...      | ...                 | ...   |
+--------+----------+---------------------+-------+

And I want the latest records for the other_id 1, 2, and 3. The obvious query I came up with is

我想要other_id 1 2 3的最新记录。我想到的一个明显的问题是

SELECT id, other_id, MAX(date_value), value
  FROM some_table 
 WHERE other_id IN (1, 2, 3) 
 GROUP BY other_id

However it spits a "not a GROUP BY expression" exception. I tried adding all other fields (i.e. id, value) in the GROUP BY clause, but that just returns everything, exactly as if there was no GROUP BY clause. (Well, it does make sense too.)

然而，它有一个“not a GROUP BY expression”的例外。我尝试在GROUP BY子句中添加所有其他字段(即id、值)，但这只是返回所有内容，就像没有GROUP BY子句一样。(嗯，这也说得通。)

So... I'm reading the Oracle SQL manual, and all I can find are some examples involving only queries with two or three columns and some i-have-never-seen-before grouping functions. How do I go and return

所以…我正在阅读Oracle SQL手册，我所能找到的只是一些示例，这些示例只涉及两个或三个列的查询，以及一些我以前从未见过的分组函数。我怎么回去呢

+--------+----------+---------------------+-------+
| id     | other_id | date_value          | value |
+--------+----------+---------------------+-------+
| 1      | 1        | 2011-04-20 21:03:05 | 104   |
| 2      | 2        | 2011-04-20 21:03:04 | 229   |
| 3      | 3        | 2011-04-20 21:03:03 | 130   |
+--------+----------+---------------------+-------+

(the latest entries for each other_id) ? Thank you.

(彼此的最新条目)?谢谢你！

3 个解决方案

#1

 select id, other_id, date_value, value from
 (
   SELECT id, other_id, date_value, value, 
   ROW_NUMBER() OVER (partition by other_id order BY Date_Value desc) r
   FROM some_table 
   WHERE other_id IN (1, 2, 3) 
 )
 where r = 1

#2

You cannot SELECT any column that is not either an aggregate or computed from only the columns used in the GROUP BY clause.

不能只从GROUP BY子句中使用的列中选择不属于聚合或计算的列。

However there are three ways to do it:

然而，有三种方法可以做到这一点:

You can use analytic functions

你可以用解析函数

SELECT id, other_id, date_value, value
  FROM ( SELECT id, other_id, date_value, MAX(date_value) OVER (partition by other_id) max_date, value
           FROM some_table )
 WHERE max_date = date_value;

You can use a self join with a “greater than ” clause and detect your max this way

您可以使用“大于”子句的自连接并通过这种方式检测您的最大值

SELECT t1.id, t1.other_id, t1.date_value, t1.value
  FROM some_table t1
  LEFT OUTER JOIN some_table t2
               ON ( t1.other_id = t2.other_id AND t2.date_value > t1.date_value )
 WHERE t2.other_id IS NULL

You can use a subquery

您可以使用子查询

  WITH max AS ( SELECT other_id, MAX(date_value) FROM some_table GROUP BY other_id )
SELECT id, other_id, date_value, value
  FROM some_table
 WHERE ( other_id, date_value ) IN ( SELECT * FROM max )

#3

Probably this is the simplest way

也许这是最简单的方法

SELECT id, other_id, date_value, value
FROM some_table
WHERE date_value in (SELECT MAX(date_value)
                     from some_table
                     GROUP BY other_id
                     HAVING other_id in (1,2,3));

Test the above query here

在这里测试上面的查询

#1

 select id, other_id, date_value, value from
 (
   SELECT id, other_id, date_value, value, 
   ROW_NUMBER() OVER (partition by other_id order BY Date_Value desc) r
   FROM some_table 
   WHERE other_id IN (1, 2, 3) 
 )
 where r = 1

#2