表变量和错误“必须声明标量变量”
问题描述:
我需要为~20行创建临时表或变量。表变量和错误“必须声明标量变量”
DECLARE @UserDailyStatsForCategories TABLE (
CategoryId uniqueidentifier not null,
[Date] date not null,
[Unique users] int not null,
[Returning users] int not null,
[New users] int not null
);
insert into @UserDailyStatsForCategories (CategoryId, [Date], [Unique users], [Returning users], [New users])
select
@CategoryId,
eventdate,
count(*),
count(case when cnt > 1 then 1 end),
count(case when cnt = 1 then 1 end)
from
(
select cast(eventtime as date) as eventdate, userid, count(*) as cnt
from telemetrydata
where [dbo].[TelemetryData].[DiscountId] = @DiscountId
and [dbo].[TelemetryData].[EventName] = 'DiscountClick'
group by cast(eventtime as date), userid
) date_user
group by eventdate;
它的工作原理,但是当我补充一点:
update CategoryDailyStatsTemp
set
[CategoryDailyStatsTemp].[Unique users] = @UserDailyStatsForCategories.[Unique users],
[CategoryDailyStatsTemp].[Returning users] = @UserDailyStatsForCategories.[Returning users],
[CategoryDailyStatsTemp].[New users] = @UserDailyStatsForCategories.[New users]
where [CategoryDailyStatsTemp].[Id] = @UserDailyStatsForCategories.CategoryId
and [CategoryDailyStatsTemp].[Date] = @UserDailyStatsForCategories.[Date];
然后我得到了错误:
Msg 137, Level 15, State 2, Line 3 Must declare the scalar variable "@UserDailyStatsForCategories".
这是怎么回事?
答
正确的语法,这是如下:
Update C
Set [Unique users] = U.[Unique users],
[Returning users] = U.[Returning users],
[New users] = U.[New users]
From CategoryDailyStatsTemp C
Join @UserDailyStatsForCategories U On U.CategoryId = C.Id
And U.Date = C.Date
+0
是的,如果要将值放入表变量中,则必须加入表变量才能在更新查询中访问它们。你也可以使用相关的子查询,但是这更复杂和更慢。最好学习连接语法。 – HLGEM
'@ CategoryID'是** **列声明的表'@ UserDailyStatsForCategories'的。稍后,您尝试像使用声明的变量一样使用它... – Shnugo