博客

达梦DCA证书考试有一道题目是：统计各部门工资总和，并列出大于80000的所有部门、ID和总工资数，当时手里少写了一个条件，练习的时候想起来了，记录一下大致如下：

我写的语句：

select
        a.DEPARTMENT_ID as 部门编号,
        sum(SALARY)     as 部门工资,
        DEPARTMENT_NAME as 部门名称
from
        TEST.EMPLOYEE a,TEST.DEPARTMENT b
where   a.DEPARTMENT_ID = b.DEPARTMENT_ID
group by
        a.DEPARTMENT_ID
having  sum(SALARY) > 80000
ORDER BY
        a.DEPARTMENT_ID;

报错：

错误[-4080]:

不是 GROUP BY 表达式

问题原因：

ORACLE或者DMDB库中：GROUP BY后面必须加上你SELECT后面所查询的所有除聚合函数之外的所有字段，原语句太粗心少写了一个列。

正确语句：

select
        a.DEPARTMENT_ID as 部门编号,
        sum(SALARY)     as 部门工资,
        DEPARTMENT_NAME as 部门名称
from
        TEST.EMPLOYEE a,TEST.DEPARTMENT b
where   a.DEPARTMENT_ID = b.DEPARTMENT_ID
group by
        a.DEPARTMENT_ID,DEPARTMENT_NAME
having  sum(SALARY) > 80000
ORDER BY
        a.DEPARTMENT_ID;

语句等价于：

select
        a.DEPARTMENT_ID as 部门编号,
        sum(SALARY)     as 部门工资,
        DEPARTMENT_NAME as 部门名称
from
        TEST.EMPLOYEE a
inner join TEST.DEPARTMENT b
on
        a.DEPARTMENT_ID = b.DEPARTMENT_ID
group by
        a.DEPARTMENT_ID,
        DEPARTMENT_NAME
having
        sum(SALARY) > 80000
ORDER BY
        a.DEPARTMENT_ID;

补充SQL基本关键词书写顺序规则：

select==》from==》where==》group by==》having==》order by