在oracle sql中通过表达式加入内部组 [英] inner join with group by expression in oracle sql
问题描述
我有两张表, employees
和作业
。 employees
包含一个变量 job_id
(多个员工可以拥有相同的job_ID)。 jobs
包含变量 job_id
和 job_title
(一个job_ID对应于一个job_title,如果你感兴趣的话,这是oracle中的hr模式)。
我希望查询返回:job_title,job_ID和拥有同样的job_Id。
我试过以下代码:
select j .job_title,e.job_ID,从雇员处统计(e.job_ID)
e,工作j
其中e.job_id = j.job_id
组别由e.job_Id
错误讯息是:
ORA-00979:不是GROUP BY表达式
00979. 00000 - 不是GROUP BY表达式
*原因:
*操作:
错误at行:83列:8
您能帮我解决这个问题吗? >解决方案
错误信息有点误导。当你选择
一堆字段和一个聚集时,你必须 group by
每个字段你选择
,只有你选择
的字段。所以你的查询必须是:
$ b
select j.job_title,e.job_ID,count(e .job_ID)雇员
e,工作j
其中e.job_id = j.job_id
由e.job_Id,j.jobtitle分组
I am new to sql, any help is appreciated.
I have two tables, employees
and jobs
. employees
contain a variable job_id
(multiple employees can have the same job_ID). jobs
contain variables job_id
and job_title
(one job_ID correspond to one job_title, this is the hr schema in oracle if you are interested).
I want the query to return: the job_title, job_ID and the number of people who have the same job_Id.
I tried the following code:
select j.job_title, e.job_ID, count(e.job_ID)
from employees e, jobs j
where e.job_id=j.job_id
group by e.job_Id
the error message is:
ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause:
*Action:
Error at Line: 83 Column: 8
Can you help me fix this?
The error message is a bit misleading. When you select
a bunch of fields and an aggregate, you have to group by
every field you select
and only the fields you select
. So your query has to be:
select j.job_title, e.job_ID, count(e.job_ID)
from employees e, jobs j
where e.job_id=j.job_id
group by e.job_Id, j.jobtitle
这篇关于在oracle sql中通过表达式加入内部组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!