时间戳

首页 > 解决方案 > ORACLE：不是 GROUP BY 表达式

sql - ORACLE：不是 GROUP BY 表达式

问题描述

我试图弄清楚这一点，但我不断收到此错误。我知道这与 SELECT 和 HAVING 子句中的子查询有关，但我不知道如何修复它们。任何帮助将不胜感激。

部门表：https ://gyazo.com/f9d782abd428acc7ec2e7d5d59befad7

员工表：https ://gyazo.com/b2d7d792c0933b13d6fdd7166fffb8a6

预期输出：https ://gyazo.com/41f6a5626a5827acbe76e3c41287742d

编辑：我想出了另一种语法！感谢所有的帮助！

    CURSOR c_emp (p_total_emp NUMBER) IS
        SELECT department.DEPARTMENT_NAME                                             
             ,  MAX(employee.HIRE_DATE)                            
             , (SELECT COUNT(EMPLOYEE_ID)
                  FROM employee
                HAVING HIRE_DATE = MAX(HIRE_DATE))
             , ROUND(MAX(employee.SALARY))
             , ROUND(MAX(employee.SALARY) * 0.68)                   
             , (SELECT COUNT(EMPLOYEE_ID)
                  FROM employee
                HAVING SALARY > ROUND(MAX(employee.SALARY) * 0.68))
          FROM employee
         INNER JOIN department 
            ON employee.DEPARTMENT_ID = department.DEPARTMENT_ID
         GROUP BY 1 
         HAVING (SELECT COUNT(EMPLOYEE_ID) 
                  FROM employee
                HAVING SALARY > ROUND(MAX(employee.SALARY) * 0.68))  > p_total_emp              
        ORDER BY DEPARTMENT_NAME ASC
        ;

样本：

标签： sqloracle

解决方案

希望到目前为止你度过了愉快的一周。:)

只是对查询的提醒，您遇到了错误，因为您不能 GROUP BY 1。您需要对未聚合的列进行 GROUP BY。（例如，department.DEPARTMENT_NAME），如下所示：-

CURSOR c_emp (p_total_emp NUMBER) IS
        SELECT department.DEPARTMENT_NAME                                             
             ,  MAX(employee.HIRE_DATE)                            
             , (SELECT COUNT(EMPLOYEE_ID)
                  FROM employee
                HAVING HIRE_DATE = MAX(HIRE_DATE))
             , ROUND(MAX(employee.SALARY))
             , ROUND(MAX(employee.SALARY) * 0.68)                   
             , (SELECT COUNT(EMPLOYEE_ID)
                  FROM employee
                HAVING SALARY > ROUND(MAX(employee.SALARY) * 0.68))
          FROM employee
         INNER JOIN department 
            ON employee.DEPARTMENT_ID = department.DEPARTMENT_ID
         GROUP BY department.DEPARTMENT_NAME 
         HAVING (SELECT COUNT(EMPLOYEE_ID) 
                  FROM employee
                HAVING SALARY > ROUND(MAX(employee.SALARY) * 0.68))  > p_total_emp              
        ORDER BY DEPARTMENT_NAME ASC
        ;

推荐阅读