处理多个表. [英] Dealing with more than one table.
问题描述
我在sql中有拖表
首先是这样的
代号名称薪金部门No
而另一个
部门ID部门名称
我想知道查询语句将是两个表之间的关系
如果我想从第一个表中选择所有行,但我想显示部门名称,而我不想在第二个表中显示部门号?????????
I have tow table in sql
first like that
code name salary department No
and the other
department Id department name
there''s a relationship between two table I want to know what the query statement will be
if I want to select all rows from first table but I want to show the department name which in the second table i don''t want to show the department No ?????????
推荐答案
只需尝试select a.column1,a.column2...,b.departmentname from first a, second b where a.depnto = b.deptno
.
Select Table1.code,Table1.name,Table1.salary ,Table2.departmentname
FROM Table1 INNER JOIN
Table2 ON Table1.departmentNo = Table2.departmentId
SELECT
EMPLOYEES.CODE,
EMPLOYEES.NAME,
EMPLOYEES.SALARY,
DEPARTMENTS.DEPARTMENTNAME
FROM EMPLOYEES
LEFT JOIN DEPARTMENTS ON DEPARTMENTS.DEPARTMENTID = EMPLOYEES.DEPARTMENTNO
ORDER BY EMPLOYEES.CODE
提示:尝试与名称保持一致.表示不要一次使用DEPARTMENTID,而不要一次使用DEPARTMENTNO,这将在将来帮助您更轻松地维护代码.
不要使用解决方案1中的方法,这是非常无效的.
注意:
上面的SQL还会列出未分配给dpt的雇员.如果您想避免这种情况,只需使用WHERE ....
问候
Hint: Try to be consistent with names. Means do not use one time DEPARTMENTID and other time DEPARTMENTNO it will help you in the future to easier maintain your code.
Do not use things like in Solution 1, it is very inefficent.
Note:
The above SQL do also list employees not assign to a dpt. If you like to avoid this, simply use WHERE....
Regards
这篇关于处理多个表.的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!