时间戳

首页 > 解决方案 > Oracle 对象关系数据库 ORA-00979：不是 GROUP BY 表达式

sql - Oracle 对象关系数据库 ORA-00979：不是 GROUP BY 表达式

问题描述

我正在尝试获取拥有 3 个以上项目的所有客户的姓名及其项目的数量，但出现以下错误：

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
Error at Line: 1 Column: 32

知道如何解决这个问题吗？我知道我必须将所有内容都包含在 GROUP BY 中，但我确实做到了。

customer_t 和 staff_t 继承自 user_t。

SELECT CONCAT(CONCAT(TREAT(REF(u) AS REF customer_t).name.first_name, ' '), (TREAT(REF(u) AS REF customer_t).name.last_name)) AS name, COUNT(p.projectno)
FROM "USER" u JOIN project p ON u.idno = p.customer.idno
WHERE VALUE(u) IS OF TYPE (customer_t)
GROUP BY name
HAVING COUNT(p.projectno) > 3;

CREATE TYPE name_t AS OBJECT (
  first_name     VARCHAR2(32),
  last_name      VARCHAR2(32)
);

CREATE TYPE address_t AS OBJECT (
  province       VARCHAR2(32),
  street         VARCHAR2(32),
  city           VARCHAR2(32),
  postal_code    VARCHAR2(10)
);

CREATE TYPE user_t AS OBJECT (
  idno           NUMBER,
  email          VARCHAR2(40),
  password       VARCHAR2(32),
  name           name_t,
  address        address_t,
  phone          VARCHAR2(15),
  MAP MEMBER FUNCTION get_idno RETURN NUMBER
) NOT FINAL;

CREATE TYPE rank_t AS OBJECT (
  rankno       NUMBER,
  name         VARCHAR2(40),
  description  VARCHAR2(60)
);

CREATE TABLE "RANK" OF rank_t (
  PRIMARY KEY (rankno),
  UNIQUE (name)
);

CREATE TYPE customer_t UNDER user_t (

);

CREATE TYPE staff_t UNDER user_t (
  salary       NUMBER(7,2),
  rank         REF rank_t
);

CREATE TABLE "USER" OF user_t (
  PRIMARY KEY (idno),
  UNIQUE (email));

CREATE TYPE project_t AS OBJECT (
  projectno    NUMBER,
  name         VARCHAR2(40),
  description  VARCHAR2(60),
  customer     REF customer_t,
  service      REF service_t
);

CREATE TABLE project OF project_t (PRIMARY KEY (projectno));

标签： sqloracle

解决方案

SELECT CONCAT(CONCAT(TREAT(REF(u) AS REF customer_t).name.first_name, ' '), (TREAT(REF(u) AS REF customer_t).name.last_name)) AS name, 
COUNT(p.projectno)
FROM "USER" u JOIN project p ON u.idno = p.customer.idno
WHERE VALUE(u) IS OF TYPE (customer_t)
GROUP BY CONCAT(CONCAT(TREAT(REF(u) AS REF customer_t).name.first_name, ' '), (TREAT(REF(u) AS REF customer_t).name.last_name)) 
HAVING COUNT(p.projectno) > 3;

推荐阅读