Oracle GROUP BY给出错误ORA-00979：不是GROUP BY表达式 [英] Oracle GROUP BY giving error ORA-00979: not a GROUP BY expression

问题描述

我们假设我们有两张表

  table1 
 ------------ ---------- 
 | ID |日期| 
 ---------------------- 
 | 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表达式

编辑：
列上的 MAX code> Date 不起作用，因为此列是 varchar（200）
数据库/表结构不是我的，我不能改变它。

解决方案

您需要做以下两件事之一...

• 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 会 not 顺序正确）），则需要将 date_field 加上 TO_DATE（date_field）以确保订单正确。

注意：使用 TO_DATE（date_field）也可能会解决MAX（）问题。

注意：如果您想将日期存储为字符串，但它们必须友好，请使用 yyyy-mm-dd

let's assume we have two tables

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

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

Using this query

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

Mysql should return this

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

In Oracle I get this error: ORA-00979: not a GROUP BY expression

EDIT: The MAX function on the column Date does not work because this column is varchar(200) The database/tables structure is not mine and I cannot alter it.

解决方案

You need to do one of two things...

• GROUP BY and use aggregate functions to consolidate multiple records down to one
• Use some lookup to identify the one record you want from the group

In your case, you don't just want a MAX() from table1, as it may be possible that a higher id has a lower date. In that case, I'd be inclined to use a lookup system...

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

NOTE: This assumes your date is formatted such that alphanumeric ordering WILL yield the correct date order. If that is NOT the case (and d-m-yyyy will not order correctly), you need to replace date_field with TO_DATE(date_field) to ensure the correct order.

NOTE: Use of TO_DATE(date_field) will also probably fix your MAX() problems.

NOTE: If you want to store dates as strings, but them to be order friendly, use yyyy-mm-dd

这篇关于Oracle GROUP BY给出错误ORA-00979：不是GROUP BY表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！