至少from的语法要改成这样,才正确FROM _TickOfCard, _FileData LEFT OUTER JOIN _WorkerData Checker ON _FileData.LastCheckerID = Checker.ID, _WorkerData但是你的查询始终没有指定与_WorkerData表的关系
翻译:列的前缀"_FileData"与用在查询中的表名或者表的别名不匹配!Checker是一张表吗?左外连接(left outer join)是将左表中的所有数据分别与右表中的每条数据进行连接组合,返回的结果除了内连接外,还包括左表中符合条件的数据,并在右表的相应列添加NULL值。下面是Microsoft SQL Server 2005 - Microsoft Document Exploer中对Left Outer Join的解释: Left Outer Join 运算符返回满足第一个(顶端)输入与第二个(底端)输入联接的每一行。它还返回任何在第二个输入中没有匹配行的第一个输入中的行。第二个输入中的非匹配行作为空值返回。如果 Argument 列内不存在任何联接谓词,则每行都是一个匹配行。
简化下再看看: SELECT a.ID, a.Type, a.Param, a.LastState, a.DateSend, a.DateUpdate, a.Year, a.MinuteFrom, a.MinuteTo, b.Data AS TickOfCard, c.Department AS SenderDepartment, c.[Group] AS SenderGroup, c.Post AS SenderPost, c.Name AS SenderName, Checker.Name AS CheckerName FROM _FileData a, _TickOfCard b, _WorkerData c LEFT OUTER JOIN c Checker ON a.LastCheckerID = Checker.ID WHERE b.WorkerID=a.SenderID AND b.Year=a.Year AND a.DateSend>=@DateFrom AND a.DateSend<=@DateTo
--try SELECT _FileData.ID, _FileData.[Type], _FileData.[Param], _FileData.LastState, _FileData.DateSend, _FileData.DateUpdate, _FileData.[Year], _FileData.MinuteFrom, _FileData.MinuteTo, _TickOfCard.Data AS TickOfCard, _WorkerData.Department AS SenderDepartment, _WorkerData.[Group] AS SenderGroup, _WorkerData.Post AS SenderPost, _WorkerData.Name AS SenderName, Checker.Name AS CheckerName FROM _TickOfCard JOin _FileData ON TickOfCard.WorkerID=_FileData.SenderID and _TickOfCard.[Year]=_FileData.[Year] LEFT OUTER JOIN _WorkerData Checker ON _FileData.LastCheckerID = Checker.ID WHERE _FileData.DateSend>=@DateFrom AND _FileData.DateSend<=@DateTo
_TickOfCard,
_FileData LEFT OUTER JOIN _WorkerData Checker ON _FileData.LastCheckerID = Checker.ID,
_WorkerData但是你的查询始终没有指定与_WorkerData表的关系
Left Outer Join 运算符返回满足第一个(顶端)输入与第二个(底端)输入联接的每一行。它还返回任何在第二个输入中没有匹配行的第一个输入中的行。第二个输入中的非匹配行作为空值返回。如果 Argument 列内不存在任何联接谓词,则每行都是一个匹配行。
SELECT
a.ID,
a.Type,
a.Param,
a.LastState,
a.DateSend,
a.DateUpdate,
a.Year,
a.MinuteFrom,
a.MinuteTo,
b.Data AS TickOfCard,
c.Department AS SenderDepartment,
c.[Group] AS SenderGroup,
c.Post AS SenderPost,
c.Name AS SenderName,
Checker.Name AS CheckerName
FROM
_FileData a,
_TickOfCard b,
_WorkerData c
LEFT OUTER JOIN c Checker ON a.LastCheckerID = Checker.ID
WHERE
b.WorkerID=a.SenderID
AND
b.Year=a.Year
AND
a.DateSend>=@DateFrom
AND
a.DateSend<=@DateTo
SELECT
_FileData.ID,
_FileData.[Type],
_FileData.[Param],
_FileData.LastState,
_FileData.DateSend,
_FileData.DateUpdate,
_FileData.[Year],
_FileData.MinuteFrom,
_FileData.MinuteTo,
_TickOfCard.Data AS TickOfCard,
_WorkerData.Department AS SenderDepartment,
_WorkerData.[Group] AS SenderGroup,
_WorkerData.Post AS SenderPost,
_WorkerData.Name AS SenderName,
Checker.Name AS CheckerName
FROM
_TickOfCard
JOin _FileData
ON TickOfCard.WorkerID=_FileData.SenderID and _TickOfCard.[Year]=_FileData.[Year]
LEFT OUTER JOIN _WorkerData Checker
ON _FileData.LastCheckerID = Checker.ID
WHERE
_FileData.DateSend>=@DateFrom
AND
_FileData.DateSend<=@DateTo