select带有基于其他列的值的临时列

我有mysql表,其中架构类似于下面的json数组。

[ { "id": 1, "firstName": "Charles Montgomery", "lastName": "Burns", "managerId": 12}, { "id": 2, "firstName": "Bart", "lastName": "Simpson", "managerId": 1}, { "id": 3, "firstName": "Marge", "lastName": "Simpson", "managerId": 1}, { "id": 4, "firstName": "Lisa", "lastName": "Simpson", "managerId": 1}, { "id": 5, "firstName": "Maggie", "lastName": "Simpson", "managerId": 1}, { "id": 6, "firstName": "Homer", "lastName": "Simpson", "managerId": 4}, { "id": 7, "firstName": "Ned", "lastName": "Flanders", "managerId": 4}, { "id": 8, "firstName": "Krusty", "lastName": "The Clown", "managerId": 2}, { "id": 9, "firstName": "Waylon", "lastName": "Smithers", "managerId": 2}, { "id": 10, "firstName": "Ralph", "lastName": "Wiggum", "managerId": 5}, { "id": 11, "firstName": "Itchy", "lastName": "", "managerId": 5}, { "id": 12, "firstName": "Comic Book Guy", "lastName": "", "managerId": 4} ] 

managerId字段是自引用id字段的外键。

我需要做一个查询,这将添加列与飞机上的“managerName”计算从id字段将保存连接firstName和lastName。

下面是我试过的(简化的)查询。 问号是由mysql驱动程序填充的占位符(在nodejs中):

 SELECT * , (SELECT CONCAT_WS(' ', firstName, lastName) FROM employee WHERE managerId = id ) as managerName FROM employee WHERE employee.id = ?; 

我意识到这个WHERE managerId = id是看的地方,但我真的不知道什么条件来匹配正确的值。

谁能帮忙?

编辑如果有人绊倒相同的事情,我发现有用的文章 。 甚至例子都是类似的。

       

网上收集的解决方案 "select带有基于其他列的值的临时列"