分析SQL Server Compact中的查询时出错
本文关键字:查询 出错 SQL Server Compact 分析 | 更新日期: 2023-09-27 18:27:20
我正在使用WebMatrix运行查询,我认为它是正确的,但我遇到了一个错误。
我的查询如下:
var result = db.Query(
@"SELECT e.event_id, e.title, e.description, e.event_start, e.event_end, e.group_id, e.recurring
FROM event e
JOIN Membership m ON m.GroupID = e.group_id
WHERE e.recurring = 0
AND m.UserID = @0
AND e.event_start >= @1
AND e.event_end <= @2
UNION ALL
SELECT e.event_id, e.title, e.description, DATEADD(week, w.weeks, e.event_start), DATEADD(week, w.weeks, e.event_end), e.group_id, e.recurring
FROM event e
JOIN Membership m ON m.GroupID = e.group_id
CROSS JOIN
( SELECT row_number() OVER (ORDER BY Object_ID) AS weeks
FROM SYS.OBJECTS
) AS w
WHERE e.recurring = 1
AND m.user_id = 4;
AND e.event_start >= @4
AND e.event_end <= @5", userID, start, end, userID, start, end
);
错误为:
System.Data.SqlServerCe.SqlCeException(0x80004005):存在分析查询时出错。[令牌线编号=10,令牌线偏移=38,错误的令牌=OVER]位于的System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32小时)System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
我将查询保存在一个.cs文件中。有人告诉我应该创建一个存储过程,因为SQL Server compact可能不支持OVER函数,这是正确的吗?如何为该查询创建存储过程?
FROM之后,您可以列出多个用逗号分隔的表,结果将是所有这些表的CROSS JOIN。
但是不能在JOIN之后使用逗号语法。相反,写出交叉连接:
JOIN Membership m
ON m.GroupID = e.group_id
CROSS JOIN
(
SELECT row_number() OVER (ORDER BY Object_ID) AS weeks
FROM SYS.OBJECTS
) AS w