SQL oracle sql中通过表达式进行内部连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15870331/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
inner join with group by expression in oracle sql
提问by Cici
I am new to sql, any help is appreciated.
我是 sql 的新手，任何帮助表示赞赏。
I have two tables, employeesand jobs. employeescontain a variable job_id(multiple employees can have the same job_ID). jobscontain variables job_idand job_title(one job_ID correspond to one job_title, this is the hr schema in oracle if you are interested).
我有两张桌子，employees和jobs。employees包含一个变量job_id（多个员工可以有相同的 job_ID）。jobs包含变量job_id和job_title（一个job_ID对应一个job_title，如果你有兴趣，这是oracle中的hr模式）。
I want the query to return: the job_title, job_ID and the number of people who have the same job_Id.
我希望查询返回：job_title、job_ID 和具有相同 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
ORA-00979：不是 GROUP BY 表达式 00979。00000 - “不是 GROUP BY 表达式” *原因：
*操作：
第 83 行错误：第 8 列
Can you help me fix this?
你能帮我解决这个问题吗？
回答by Dan Bracuk
The error message is a bit misleading. When you selecta bunch of fields and an aggregate, you have to group byevery field you selectand only the fields you select. So your query has to be:
错误信息有点误导。当你select有一堆字段和一个聚合时，你必须对group by每一个字段你select和只有你的字段select。所以你的查询必须是：
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.job_title
