SQL SQL查询根据条件从两个表中选择多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7647306/
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 select Multiple Column from two table based on a condition
提问by Jpaul
I have two tables Table_Employee,Table_Departmentrespectively, i wanted to select the manager who belongs to employee more than three times & also want to display depatment name along with it.
我有两个表Table_Employee，Table_Department分别，我想选择属于员工的经理超过 3 次，并且还想显示部门名称。
This task has to be done in a single query. Is it easy to do it ? here is my table structure .
此任务必须在单个查询中完成。容易做到吗？这是我的表结构。
here the Column_Empno will be the Column_Manager, which means the table is self reference
这里的 Column_Empno 将是 Column_Manager，这意味着该表是自引用
Table_Employee
Column_Empno int
Column_Fname varchar(50)
Column_Lname varchar(50)
Column_Job varchar(50)
Column_Manager int
Column_HireDate date
Column_Salary int
Column_Commision int
Column_DeptNo int
Table_Department
Column_DeptNo int
Column_Dname varchar(50)
Column_Location varchar(50)
回答by Anna V?lkl
Manager + Department:
经理+部门：
SELECT Column_Fname, Column_Lname, table_Department.Column_Dname
FROM Table_Employee
INNER JOIN table_Department ON Table_Employee.Column_DeptNo = table_Department.Column_DeptNo
or you could also write:
或者你也可以写：
SELECT Column_Fname, Column_Lname, table_Department.Column_Dname
FROM Table_Employee
WHERE Table_Employee.Column_DeptNo = table_Department.Column_DeptNo
It's not tested. By the way, why do you Name your Colums "Column_..." and not just "Fname", "Lname",... and your table "Table_...." and not just "Employee" and "Department"?
它没有经过测试。顺便说一句，为什么您将列命名为“Column_...”而不仅仅是“Fname”、“Lname”、...以及您的表“Table_....”，而不仅仅是“Employee”和“Department” ?
回答by Alexey
select
t1.column_manager,
t2.column_dname
from
(
select column_manager, column_deptno = max(column_deptno)
from table_employee
group by column_manager
having count(*) > 3
) t1
join table_department t2 on t1.column_deptno = t2.column_deptno
