Oracle SQL GROUP BY“不是 GROUP BY 表达式”帮助

Oracle SQL GROUP BY "not a GROUP BY expression" help

提问by Yanick Rochon

I have a table some_tablelike

我有一张some_table像

+--------+----------+---------------------+-------+
| 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_id1, 2, and 3. The obvious query I came up with is

我希望为最新的记录other_id1，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 BYclause, but that just returns everything, exactly as if there was no GROUP BYclause. (Well, it does make sense too.)

然而，它吐出一个“ not a GROUP BY expression”异常。我尝试在子句中添加所有其他字段（即id, value）GROUP BY，但这只会返回所有内容，就像没有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.

（每个的最新条目other_id）？谢谢你。

回答by Michael Pakhantsov

 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

回答by Benoit

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 )
  

• 您可以使用分析函数

  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;
  

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

  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
  

• 您可以使用子查询

    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 )
  

回答by Shivanand

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

在这里测试上面的查询