Oracle GROUP BY 给出错误 ORA-00979：不是 GROUP BY 表达式

Question

假设我们有两个表

table1
    ----------------------
    |ID         | Date   |
    ----------------------
    |1          |20110101|
    |1          |20110102|
    |1          |20110103|
    |2          |20110102|
    |2          |20110103|
    |2          |20110104|
    ----------------------

table2
    ----------------------
    |ID2        |val     |
    ----------------------
    |1          |152     |
    |2          |155     |
    ----------------------

使用这个查询

SELECT * FROM table1, table2
WHERE table1.ID = table2.ID2
GROUP BY table1.ID 
ORDER BY DATE DESC

Mysql应该返回这个

-------------------------------------------
|ID         |date    |ID2        |val     |
-------------------------------------------
|1          |20110103|1          |152     |
|2          |20110104|2          |155     |
-------------------------------------------

在Oracle中我得到这个错误： ORA-00979：不是 GROUP BY 表达式

编辑： Date 列上的 MAX 函数不起作用，因为该列是 varchar(200) 数据库/表结构不是我的，我无法更改它。

Answer 1

您需要执行两件事之一...

• GROUP BY 并使用聚合函数将多条记录合并为一条
• 使用一些查找来从组中识别您想要的一条记录

在您的情况下，您不只是想要来自 table1 的 MAX()，因为可能较高的 id 具有较低的日期。在这种情况下，我倾向于使用查找系统...

WITH
  ordered_table1 AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY date_field DESC) AS sequence_number,
    *
  FROM
    table1
)
SELECT
  *
FROM
  ordered_table1
INNER JOIN
  table2
    ON table2.id = ordered_table1.id
WHERE
  ordered_table1.sequence_id = 1

注意：这假设您的日期格式为字母数字排序将产生正确的日期顺序。如果情况并非如此（并且 dm-yyyy 将无法正确排序），您需要替换 date_field使用 TO_DATE(date_field) 确保顺序正确。

注意：使用TO_DATE(date_field)也可能会解决您的 MAX() 问题。

注意：如果您想将日期存储为字符串，但为了顺序友好，请使用yyyy-mm-dd

Answer 2

在带有 group by 的 SQL 语句的 select 和 order by 子句中，只能使用 group by 中使用的列/表达式或其他列的聚合函数（min、max、avg ..）。

我知道 Oracle 是这样，但令我感到有点惊讶的是 MySQL 中的情况有所不同

Answer 3

按以下方法尝试一下：

   SELECT *
     FROM table1
LEFT JOIN table2 
       ON table1.ID = table2.ID
 ORDER BY DATE DESC
 GROUP BY table1.ID

Answer 4

一般来说，将 select * from 与 group by 结合使用是一个坏主意。 select 子句中的表达式需要是标量，即要么是组的聚合函数，要么与分组无关，要么是在 group by 子句本身中。 Select * 几乎保证不会出现这种情况。你可能想要这样的东西：

select t1.id, max(t1.date), t2.id, max(t2.val)
  from table1 t1
  join table2 t2 on t1.id=t2.id
  group by t1.id
  order by  max(t1.date) desc

Answer 5

Oracle 中的 Group by 意味着您正在尝试对其中一列运行聚合函数（求和、计数等）。看来您只是想先按 ID 订购，然后再按日期订购。我会做这样的事情：

SELECT * 
FROM   table1
       JOIN table2 ON (table1.ID = table2.ID2)
ORDER BY ID,
         DATE DESC

如果这不是您想要的，那么答案是您需要 select 子句中不属于聚合函数的每一列都位于 group 子句中。

编辑

响应评论：

最好首先将表1作为子查询进行过滤，然后连接到表2：

SELECT *
FROM   (SELECT DISTINCT ID,MAX(TO_DATE(DATE,'mm-dd-yyyy')) FROM Table1 GROUP BY ID) T1
       JOIN Table2 ON (T1.ID = Table2.ID2)
ORDER BY date DESC