Oracle SQL GROUP BY“不是GROUP BY表达式”帮帮我 [英] Oracle SQL GROUP BY "not a GROUP BY expression" help

问题描述

我有一张表 some_table like

  + --- ----- + ---------- + --------------------- + ------- + 
 | id | other_id | date_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 | 
 | ... | ... | ... | ... | 
 + -------- + ---------- + --------------------- + --- ---- + 
  

我想要 other_id 1 ， 2 和 3 。我想到的显而易见的查询是

pre code> SELECT id，other_id，MAX（date_value），
FROM some_table
WHERE other_id IN（1，2，3）
GROUP BY other_id

然而，它会吐出不是GROUP BY表达式异常。我尝试在 GROUP BY 中添加所有其他字段（即 id ， value code>子句，但这只是返回所有内容，就好像没有 GROUP BY 子句一样。 （嗯，它也有道理。）

所以...我正在阅读Oracle SQL手册，我能找到的只是一些涉及查询的例子，两列或三列和一些我从未见过的分组功能。我如何去和返回

  + -------- + ---------- + --------------------- + ------- + 
 | id | other_id | date_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 | 
 + -------- + ---------- + --------------------- + --- ---- + 
  

（每个 other_id ）？


解决方案

  select id，other_id，date_value，value from 
 （
 SELECT ID，other_id，date_value，value，
 ROW_NUMBER（）OVER（by other_id order By BY Date_Value desc）r 
 FROM some_table 
 WHERE other_id IN（1，2， 3）
）
其中r = 1 
  




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

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.)

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

+--------+----------+---------------------+-------+
| 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.

解决方案

 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

这篇关于Oracle SQL GROUP BY“不是GROUP BY表达式”帮帮我的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！