英文:
"Unflatten" columns into rows
问题
在这里，Department 表中的两个 ..._employee_id 列是指向 Employee 表的外键。
我想获取所有既是经理又在其部门中有最可爱的毛衣的员工的员工信息列表。
可以这样做：
SELECT Employee.*
FROM Employee
WHERE Employee.id IN (SELECT manager_employee_id FROM Department WHERE manager_employee_id IS NOT NULL)
OR Employee.id IN (SELECT loveliest_jumper_employee_id FROM Department WHERE loveliest_jumper_employee_id IS NOT NULL);
但这有点麻烦。在我的情况下，实际上有四个相关的外键列，而不仅仅是两个，因此更加冗余。有没有更简单的方法来编写这个查询呢？
关于我的情况有一些额外的要点：
- 有时外键可以为NULL（即在该部门中既没有经理也没有可爱的毛衣穿者），在结果集中不应该有该行。
- 您可以假设除了NULL之外，没有任何外键会列在多个行或列中（没有人在多个部门，也没有经理有可爱的毛衣穿者）。
- 在结果集中，我不需要任何额外的信息，只需要员工表，即不需要部门名称或员工与部门的关系（经理/可爱的毛衣穿者）。
英文:
Imagine a very simple database schema:
CREATE TABLE Employee
(
id INTEGER PRIMARY KEY NOT NULL,
name TEXT NOT NULL
);
CREATE TABLE Department
(
id INTEGER PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
manager_employee_id INTEGER,
loveliest_jumper_employee_id INTEGER
);
Here, the two ..._employee_id columns in the Department table are foreign keys into the Employee table.
I'd like to get a list of employee information for all employees who are either managers or have the loveliest jumper in their department.
This would do:
SELECT Employee.*
FROM Employee, Department
WHERE Employee.id = Department.manager_employee_id
UNION
SELECT Employee.*
FROM Employee, Department
WHERE Employee.id = Department.loveliest_jumper_employee_id;
But it's a bit of a pain. In my case, there are actually four relevant foreign key columns, not just two, so it's even more redundant. Is there are simpler way to write this query?
Some extra points about my situation:
- Sometimes the foreign key could be NULL (i.e. no manager and/or no lovely jumpers at all in that department) and there should be no row in the result set for that.
- You can assume that there is no foreign key (except NULL) is listed in more than one row or column (no one is is more than one department ... and no managers have lovely jumpers).
- I don't need any information in the result set except the Employee table i.e. neither the department name nor the employee's relationship with the department (manager / lovely jumper wearer).
答案1
得分: 1
分析：
这是一段SQL查询语句，用于从Employee和Department表中选择数据。
想法：
查询语句通过INNER JOIN将Employee表与Department表连接，连接条件是Employee表的id等于Department表的manager_employee_id或loveliest_jumper_employee_id。
推测：
这个查询可能是为了获取某些员工和他们所在部门的相关信息。
汉化后的代码：
SELECT e.*
FROM 员工表 e
INNER JOIN 部门表 d
ON e.id = d.manager_employee_id OR e.id = d.loveliest_jumper_employee_id;
英文:
SELECT e.*
FROM Employee e
INNER JOIN Department d
ON e.id = d.manager_employee_id OR e.id = d.loveliest_jumper_employee_id;
答案2
得分: 1
可以使用IN子句：
SELECT e.*
FROM Employee e
INNER JOIN Department d
ON e.id IN (d.manager_employee_id, d.loveliest_jumper_employee_id);
英文:
We can also just use an IN clause:
SELECT e.*
FROM Employee e
INNER JOIN Department d
ON e.id IN (d.manager_employee_id, d.loveliest_jumper_employee_id);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论