显示相同的字段AS来自同一个表的不同名称 - Access 2010 [英] Displaying the same fields AS different names from the same table -Access 2010
问题描述
我有EmployeeName;它来自Employee表。 Employee表保留组织中的所有Employees，Employee表引用Position表的主键，该表保存不同的位置名称。所以这是我如何区分员工表中的员工; Employee表中的每个记录都有一个引用Position表的PosNo（worker = Pos1，manager = Pos2，etc ...）因此为了简单起见，employee表中的记录类似于：EmployeeName，EmployeeAddress，DeptNo， PosNo
这里是问题：某些职位在其他职位下。 Employee表中有工作，Employee表中有经理。我想要一个表，列出所有的工人及其经理。例如，表将有两个字段：EmployeeName，ManagerName。
Employee表分解为泛化层次结构。薪资表和小时表从雇员表中分支出来。然后，从Salary表中，另一个表分支出来称为Manager（我称为ProgramSupervisor;它有一个唯一字段）。工人是小时表的一部分。管理员（ProgramSupervisor）和工作者（每小时）通过ISL表相互关联。经理是ISL的主管，因此ISL有一个经理作为其中的一个领域。但是，工人（每小时）在ISL中工作，因此将ISLNo作为其表中的字段，即Hourly表。
因此，我试图找到方式来关联所有这些表作为一个表与两个字段，工人和经理，其中工人属于经理通过ISL表。我会使用某种类型的嵌套查询吗？我会发布我的代码，到目前为止，这是绝对不正确的（可能甚至没有正确的轨道），我会发布我的ERD，以便你可以得到一个更好的图表如何表相关。
SELECT EmpLastName +'，'+ EmpFirstName as ProgSupName，
EmpLastName +'，'+ EmpFirstName as EmpName
FROM Employee，Salary， ProgramSupervisor，ISL，Hourly
WHERE Employee.EmpNo = Salary.EmpNo
AND Salary.EmpNo = ProgramSupervisor.EmpNo
AND ProgramSupervisor.EmpNo = ISL.ProgramSupervisor_EmpNo
AND ISL.ISLNo = Hourly.ISLNo
AND Hourly.EmpNo = Employee.EmpNo
ERD：
[Employee-Supervisor关系] ]
EmpNo EmpFirstName EmpLastName
----- ------ ------ -----------
1 Montgomery Burns
2 Homer Simpson
[每小时]
EmpNo ISLNo
----- -----
2 1
[ISL] p>
ISLNo ProgramSupervisor_EmpNo ISLName
----- --------- -------------- -------------------------
1 1 Springfield核电厂
如果您将它们放在一个类似下面的查询中：
结果如下：
Employee_LastName Employee_FirstName ISLName Supervisor_LastName Supervisor_FirstName
------ ----------- ------------------ --------------------- ---- ------------------- --------------------
辛普森霍默斯普林菲尔德核电厂烧伤蒙哥马利
但等一下！我听说你说，在这个查询中有 4个表。[Supervisor]表是从哪里来的？
使用[Supervisor]作为其别名的[Employee]表的另一个实例。如果我们在讨论[EmpLastName]，[EmpFirstName]等时，我们使用别名来指定要引用的实例，那么表可以多次出现在查询中。
上述查询的SQL显示第二行最后一行的第二个实例 Employee AS Supervisor ：
< pre class =lang-sql prettyprint-override>
SELECT
Employee.EmpLastName AS Employee_LastName，
Employee.EmpFirstName AS Employee_FirstName，
ISL.ISLName，
Supervisor.EmpLastName AS Supervisor_LastName，
Supervisor.EmpFirstName AS Supervisor_FirstName
FROM
（
Employee
INNER JOIN
（
每小时
INNER JOIN
ISL
ON Hourly.ISLNo = ISL.ISLNo
）
ON Employee.EmpNo = Hourly.EmpNo
）
INNER JOIN
员工AS主管
ON ISL.ProgramSupervisor_EmpNo = Supervisor.EmpNo
I have EmployeeName; it is from the Employee table. The Employee table holds ALL Employees in the organization and the Employee table references the primary key of the Position table, which holds the different position names. So this is how I'm differentiating between employees in the Employee table; each record in the Employee table has a PosNo which references the Position table(worker = Pos1, manager = Pos2, etc...) So for simplicity's sake, a record in the employee table would be similar to: EmployeeName, EmployeeAddress, DeptNo, PosNo
Here's the problem: Certain positions are under other positions. There are workers in the Employee table and there are managers in the Employee table. I'm wanting to make a table that lists all workers and their managers. For example, the table would have two fields: EmployeeName, ManagerName.
The Employee table is broken down into a generalization hierarchy. The Salary and Hourly tables branch out from Employee table. Then, from the Salary table, another table branches out called Manager(which I call ProgramSupervisor; it has a unique field). Workers are part of the Hourly table though. Managers(ProgramSupervisor) and Workers(Hourly) are related to each other through the ISL table. The Manager is the head of the ISL and therefore ISL has a ManagerNo as one of its fields. Workers(Hourly), however, work in the ISL and therefore have ISLNo as a field in their table, the Hourly table.
So, I'm trying to find a way to relate all of these table as make a table with two fields, workers and managers, in which the workers belong to managers through the ISL table. Would I use a nested query of some sort? I'll post my code so far, which is absolutely not correct (probably no even on the right track) and I'll post my ERD so you can get a better picture of how the tables relate.
SELECT EmpLastName + ', ' + EmpFirstName as ProgSupName,
EmpLastName + ', ' + EmpFirstName as EmpName
FROM Employee, Salary, ProgramSupervisor, ISL, Hourly
WHERE Employee.EmpNo = Salary.EmpNo
AND Salary.EmpNo = ProgramSupervisor.EmpNo
AND ProgramSupervisor.EmpNo = ISL.ProgramSupervisor_EmpNo
AND ISL.ISLNo = Hourly.ISLNo
AND Hourly.EmpNo = Employee.EmpNo
ERD:
In its simplest form you can distill the Employee-Supervisor relationship down to three tables:
[Employee]
EmpNo EmpFirstName EmpLastName
----- ------------ -----------
1 Montgomery Burns
2 Homer Simpson
[Hourly]
EmpNo ISLNo
----- -----
2 1
[ISL]
ISLNo ProgramSupervisor_EmpNo ISLName
----- ----------------------- -------------------------
1 1 Springfield Nuclear Plant
If you put them together in a query that looks like this
it produces results like this:
Employee_LastName Employee_FirstName ISLName Supervisor_LastName Supervisor_FirstName
----------------- ------------------ ------------------------- ------------------- --------------------
Simpson Homer Springfield Nuclear Plant Burns Montgomery
"But wait a minute!" I hear you say, "There are four tables in that query. Where did the [Supervisor] table come from?"
That is just another instance of the [Employee] table that uses [Supervisor] as its alias. A table can appear in a query more than once provided that we use aliases to specify the instance to which we are referring when we talk about [EmpLastName], [EmpFirstName], etc..
The SQL for the above query shows the second instance Employee AS Supervisor on the second-last line:
SELECT
Employee.EmpLastName AS Employee_LastName,
Employee.EmpFirstName AS Employee_FirstName,
ISL.ISLName,
Supervisor.EmpLastName AS Supervisor_LastName,
Supervisor.EmpFirstName AS Supervisor_FirstName
FROM
(
Employee
INNER JOIN
(
Hourly
INNER JOIN
ISL
ON Hourly.ISLNo = ISL.ISLNo
)
ON Employee.EmpNo = Hourly.EmpNo
)
INNER JOIN
Employee AS Supervisor
ON ISL.ProgramSupervisor_EmpNo = Supervisor.EmpNo
这篇关于显示相同的字段AS来自同一个表的不同名称 - Access 2010的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
