只是移动列名后还不太了解查询 [英] Not quite understanding the query after just shifting column names

问题描述

这是我的表 tblEmployee ，当我尝试使用自联接查询时，结果在移动列时会有所不同，有人可以向我解释这两个查询之间的区别.谢谢

EmployeeID  Name    ManagerID
 1          Mike     3
 2          Rob      1
 3          Todd    NULL
 5          Ben      1
 6          Sam      1

Query_1

SELECT E.Name AS Employee, M.Name AS Manager
from tblEmployee E
LEFT JOIN tblEmployee M
on E.ManagerID = M.EmployeeID  

输出

Employee    Manager
 Mike        Todd
 Rob         Mike
 Todd        NULL
 Ben         Mike
 Sam         Mike

QUERY_2

SELECT E.Name AS Employee, M.Name AS Manager
from tblEmployee E
LEFT JOIN tblEmployee M
on E.EmployeeID = M.ManagerID  

输出

Employee  Manager
Mike       Rob
Mike       Ben
Mike       Sam
Rob        NULL
Todd       Mike
Ben        NULL
Sam        NULL

解决方案

首先简单说明一下:左联接采用第一个表中的每一行，而第二行中至少与您选择的列中的匹配. >

在第一个查询中，您正在考虑每行上的经理ID ，并在第二张表中的 Employee ID 中寻找匹配项:由于第二张表( Employee ID )中的值是 键 (至少在示例定义中)，因此只能找到一行匹配该ID.

例如:第一行Mike的经理编号为 = 3，当您查看表格时，只有一行的员工编号为 = 3，即托德

在第二个查询中，您正在考虑每行上的 Employee ID ，并在第二张表中的 Manager ID 上查找匹配项:由于可能有多个具有相同 Manager ID 的行，所以您将为每一行获得更多的价值.

例如:第一行，Mike的员工编号为 = 1，当您查看表格时，您有三行的 anager ID = 1，分别是Rob，Ben和山姆.

This is my table tblEmployee ,when I'm trying to use a self join query it's result is varying while shifting columns, can someone please explain me the difference between those two queries.Thanks

EmployeeID  Name    ManagerID
 1          Mike     3
 2          Rob      1
 3          Todd    NULL
 5          Ben      1
 6          Sam      1

Query_1

SELECT E.Name AS Employee, M.Name AS Manager
from tblEmployee E
LEFT JOIN tblEmployee M
on E.ManagerID = M.EmployeeID  

OUTPUT

Employee    Manager
 Mike        Todd
 Rob         Mike
 Todd        NULL
 Ben         Mike
 Sam         Mike

QUERY_2

SELECT E.Name AS Employee, M.Name AS Manager
from tblEmployee E
LEFT JOIN tblEmployee M
on E.EmployeeID = M.ManagerID  

OUTPUT

Employee  Manager
Mike       Rob
Mike       Ben
Mike       Sam
Rob        NULL
Todd       Mike
Ben        NULL
Sam        NULL

解决方案

First a simple explanation: the left join takes every row in the first table that has at least a match in the second table on the column you selected.

In the first query you are considering the Manager ID on every row and looking for a match on the Employee ID in the second table: since value in the second table (Employee ID) is a key (at least in the example definition), you can find only one row that matches that ID.

Eg: First row Mike has Manager ID = 3, when you are looking at the table you have only one row that has Employee ID = 3, and that is Todd

In the second query you are considering the Employee ID on every row and looking for a match on the Manager ID in the second table: since there could be more than one row with the same Manager ID, you will receive more value for every row.

Eg: First row Mike has Employee ID = 1, when you are looking at the table you have three rows with anager ID = 1 and those are Rob, Ben and Sam.

这篇关于只是移动列名后还不太了解查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！