时间戳

首页 > 解决方案 > “on 子句”中的未知列“EmployeeDemographics.EmployeeID”

mysql - “on 子句”中的未知列“EmployeeDemographics.EmployeeID”

问题描述

我想将此表作为输出，但出现此错误Unknown column 'EmployeeDemographics.EmployeeID' in 'on Clause'

我使用的查询是：

SELECT * FROM EmployeeDemographics FULL JOIN EmployeeSalary ON EmployeeDemographics.EmployeeID=EmployeeSalary.EmployeeID;

标签： mysql

解决方案

目前 MySQL 不完全支持 Full Join。因此，您将不得不依靠一些解决方法连接来完成您的逻辑。

create table EmployeeDemographics(employeeid integer, firstname varchar(100), lastname varchar(100));
insert into EmployeeDemographics(employeeid, firstname,lastname) values(1, "User1", "TestLastname1");
insert into EmployeeDemographics(employeeid, firstname,lastname) values(2, "User2", "TestLastname2");
insert into EmployeeDemographics(employeeid, firstname,lastname) values(null, "User3", "TestLastname2");


create table EmployeeSalary(id integer, employeeid integer, jobtitle varchar(100), salary integer);
insert into EmployeeSalary(id, employeeid, jobtitle, salary) values(1, 1, "UserJob1", 12345);
insert into EmployeeSalary(id, employeeid, jobtitle, salary) values(2, 2, "UserJob2", 67890);
insert into EmployeeSalary(id, employeeid, jobtitle, salary) values(3, 3, "UserJob3", null);

SELECT ed.firstname, ed.lastname, es.salary
  FROM EmployeeDemographics ed 
    LEFT JOIN EmployeeSalary es 
      ON ed.employeeid = es.employeeid
UNION ALL
  SELECT ed.firstname, ed.lastname, es.salary 
 FROM EmployeeDemographics ed 
    RIGHT JOIN EmployeeSalary es 
      ON ed.employeeid = es.employeeid
WHERE ed.employeeid IS NULL

它是很久以前要求的功能，但尚不可用。

推荐阅读