SQL-必须声明标量变量误差

Question

我似乎无法弄清楚我的SQL代码有什么问题。它说我需要声明标量变量 @tempmile2，但它是一个声明的表。

INSERT INTO @tempMile (UserFullName, USERs_ID, timecard_date, timecard_createddate)
SELECT UserFullName, USERs_ID, timecard_date, timecard_createddate
FROM v_tblTimeCard
WHERE convert(date, TimeCard_CreatedDate) = dateadd(day,-1, cast(getdate() as date))
group by
userfullname,
USERs_ID,
timecard_date,
TimeCard_CreatedDate
DECLARE @tempMile2 table (USERs_ID int, timecard_date date)
INSERT INTO @tempMile2 (USERs_ID, timecard_date)
SELECT USERs_ID, timecard_date  --add count logic here --timecard entries 1 for day
FROM @tempMile
group by USERs_ID, timecard_date 
select * from dbo.tblMileage left join 
@tempMile2 on tblMileage.Users_ID = @tempMile2.USERs_ID AND tblMileage.DateOfService = @tempMile2.timecard_date
where @tempMile2.TimeCard_Date IS NOT NULL

仅在上一个选择语句中 @tempmile2要求声明。有任何想法吗？

Answer 1

您不能使用表变量来符合列名。表别名不能以@开头。因此，尝试一下：

select *
from dbo.tblMileage m left join 
     @tempMile2 tm
     on m.Users_ID = tm.USERs_ID and m.DateOfService = tm.timecard_date
where tm.TimeCard_Date IS NOT NULL;

当然，您在where子句中的比较意味着join是内在的联接，而不是外部连接，但我没有进行更改。