我正在尝试重写以下查询(不是我自己创建的),因为这会引发执行错误:
旧查询:
SELECT LastName + ', ' + FirstName AS 'Teamleader'
FROM dbo.EmpTable
WHERE EmpID IN (
SELECT SupEmpID
FROM dbo.EmpTable
WHERE
SupEmpID = (
SELECT EmpID
FROM EmpTable
WHERE SupEmpID = (
SELECT EmpID
FROM EmpTable
WHERE NTID = @NTID
)
)
)
ORDER BY TeamLeader
我想使用 JOIN 而不是嵌套查询,但我不确定当涉及 IN 语句时如何应用它。到目前为止,我有以下内容,但这返回了相同的错误(可能是因为我仍然在那里有 IN 语句)。
新查询(尝试):
SELECT A.LastName + ', ' + A.FirstName AS TeamLeader
FROM dbo.EmpTable AS A
WHERE A.EmpID IN
(
SELECT D.SupEmpID
FROM dbo.EmpTable AS B
INNER JOIN dbo.EmpTable AS C
ON C.SupEmpID = B.EmpID
INNER JOIN dbo.EmpTable AS C
ON D.SupEmpID = C.EmpID
WHERE B.NTID = @NTID
)
ORDER BY TeamLeader
有人会在这里解释 IN 语句,并让我知道我可以做些什么来解决这个问题吗?
如果没有两个别名表C
,则最后一个查询可能会起作用:
SELECT A.LastName + ', ' + A.FirstName AS TeamLeader
FROM dbo.EmpTable AS A
WHERE A.EmpID IN
(
SELECT D.SupEmpID
FROM dbo.EmpTable AS B
INNER JOIN dbo.EmpTable AS C
ON C.SupEmpID = B.EmpID
INNER JOIN dbo.EmpTable AS D
--------------------------------------------------------^
ON D.SupEmpID = C.EmpID
WHERE B.NTID = @NTID
)
ORDER BY TeamLeader
IN
可能是最好的解决方案。 IN
将返回"外部"表中的所有记录,其中内部表(或子查询)中有匹配的记录。
如果您执行了JOIN
,则外部表中的记录将与内部表中的记录配对,因此如果内部表中有多个匹配项,您可能会返回多个结果。 您可以对结果进行DISTINCT
以消除重复项,但IN
可能会更快,因为它不必匹配所有记录然后消除重复项。
尝试LIKE
语句,因为如果 ID 是字符串值,那将是最佳实践。
在第二个查询中修复了别名的明显问题后,您可以使用更优雅的 CTE:
;WITH ids as (
SELECT D.SupEmpID
FROM dbo.EmpTable AS B
INNER JOIN dbo.EmpTable AS C1 ON C1.SupEmpID = B.EmpID
INNER JOIN dbo.EmpTable AS C2 ON D.SupEmpID = C2.EmpID
WHERE B.NTID = @NTID
)
SELECT A.LastName + ', ' + A.FirstName AS TeamLeader
FROM dbo.EmpTable AS A
INNER JOIN ids ON ids.SupEmpID = A.EmpID
ORDER BY TeamLeader