SQL SQL查询显示姓名、职位、部门名称工资和等级
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7739186/
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
SQL Query to display the name, job, department name salary and grade
提问by rohit
The question is:
问题是：
- "Create a query that will display the
NAME, JOB, DEPARTMENT NAME, SALARYandGRADE(from theSALGRADEtable) for allEMPLOYEES."
- “创建一个查询，将显示所有的
NAME, JOB, DEPARTMENT NAME, SALARY和GRADE（从SALGRADE表中）EMPLOYEES。”
My code so far:
到目前为止我的代码：
SELECT e.ename, e.job, d.dname, e.sal, s.grade
FROM emp e, dept d, salgrade s
WHERE e.deptno = d.deptno
This returns 70 rows, but the result should only produce 14rows.
这将返回 70 行，但结果应该只产生14行。
EMPLOYEE EMP JOB DEPT NAME EMP SALARY SALARY GRADE
---------- --------- -------------- ---------- ------------
JAMES CLERK SALES 950 1
SMITH CLERK RESEARCH 800 1
ADAMS CLERK RESEARCH 1100 1
MARTIN SALESMAN SALES 1250 2
WARD SALESMAN SALES 1250 2
MILLER CLERK ACCOUNTING 1300 2
ALLEN SALESMAN SALES 1600 3
TURNER SALESMAN SALES 1500 3
BLAKE MANAGER RESEARCH 2850 4
CLARK MANAGER ACCOUNTING 2450 4
JONES MANAGER RESEARCH 2975 4
FORD ANALYST RESEARCH 3000 4
SCOTT ANALYST RESEARCH 3000 4
KING PRESIDENT ACCOUNTING 5000 5
emptable hasempno, ename, job, deptno, comm, salsalgradetable hasgradeDEPTtable hasdeptno, loc
emp表有empno, ename, job, deptno, comm, salsalgrade表有gradeDEPT表有deptno, loc
回答by marc_s
The root cause is your use of the old, deprecated "implicit join" syntax:
根本原因是您使用了旧的、已弃用的“隐式连接”语法：
select e.ename, e.job, d.dname, e.sal, s.grade
from emp e, dept d, salgrade s
where e.deptno = d.deptno
You're only joining the empand depttable with a condition in the WHEREclause - your salgradetable is cross joined - one entry there will be matched against all entries from the result.
您只是在子句中加入带有条件的empanddept表WHERE- 您的salgrade表是交叉连接的 - 一个条目将与结果中的所有条目匹配。
I would recommend to alwaysuse the new ANSI standard JOIN syntax:
我建议始终使用新的 ANSI 标准 JOIN 语法：
SELECT
e.ename, e.job, d.dname, e.sal, s.grade
FROM
dbo.emp e
INNER JOIN
dbo.dept d ON e.deptno = d.deptno
INNER JOIN
dbo.salgrade s ON ???????
Right away, you see there's no conditiondefined on how to join the salgradetable into your query - you need to provide an appropriate condition here, then your result should be just fine!
马上，您会看到没有定义关于如何将salgrade表加入您的查询的条件 - 您需要在此处提供适当的条件，然后您的结果应该就好了！
回答by Shahariar Khan
CREATE A QUERY THAT WILL DISPLAY THE NAME,JOB,DEPARTMENT NAME,SALARY AND GRADE( FROM THE SALGRADE TABLE) FOR ALL EMPLOYEES query should be ---
创建一个查询，将显示名称、工作、部门名称、薪水和等级（来自 SALGRADE 表）对于所有员工查询应该是 ---
SELECT e.ename,e.job,dname,e.sal,g.grade
FROM emp e,dept,salgrade g
WHERE (e.sal BETWEEN g.losal AND g.hisal) AND e.deptno=dept.deptno;
回答by Sam Casil
select e.ename, e.job, d.dname, e.sal, s.grade
from emp e, dept d, salgrade s
where e.deptno = d.deptno
and s.grade in (1,2,3,4,5)
回答by Bryan
try this one.
试试这个。
SELECT e.ename,e.job,d.dname,e.sal,s.grade
FROM emp e , dept d ,salgrade s
WHERE e.deptno=d.deptno
GROUP BY e.ename
回答by Nanda Venga
try this one while using join
在使用 join 时试试这个
select e.ename,d.dname,s.grade
from emp e
join dept d on (e.deptno=d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal);
