SQL Server 索引视图错误

发布于 2024-12-16 20:10:49 字数 1051 浏览 2 评论 0原文

我意识到这是一个非常人为的示例，但我已将完整版本简化为以下内容，以演示问题：

CREATE VIEW model.Appointments_Partition1
WITH SCHEMABINDING AS
  SELECT CONVERT(varchar(15), AppointmentId) as Id, 
         ap.AppTypeId as AppointmentTypeId, 
         ap.Duration as DurationMinutes, 
         ap.AppointmentId as EncounterId, 
         COUNT_BIG(*) as __count_big
    FROM dbo.Appointments ap 
    JOIN dbo.PracticeCodeTable pct ON SUBSTRING(pct.Code, 1, 1) = ap.ScheduleStatus 
                                  AND pct.ReferenceType = 'AppointmentStatus' 
   WHERE ap.AppTime > 0
GROUP BY CONVERT(varchar(15), AppointmentId), ap.AppTypeId, ap.Duration, ap.AppointmentId

CREATE UNIQUE CLUSTERED INDEX [IX_Appointments_Partition1_Id]
ON model.Appointments_Partition1 ([Id]);

我得到：

消息 8668，级别 16，状态 0，第 12 行
无法在视图“PracticeRepository.model.Appointments_Partition1”上创建聚集索引“IX_Appointments_Partition1_Id”，因为视图的选择列表包含聚合函数或分组列结果的表达式。考虑从选择列表中删除聚合函数或分组列结果的表达式。

我包括 count_big...那么为什么分组有问题？...以及如何解决该错误？

原文

I realize this is a very contrived example, but I've simplified the full version down to the following which demonstrates the problem:

CREATE VIEW model.Appointments_Partition1
WITH SCHEMABINDING AS
  SELECT CONVERT(varchar(15), AppointmentId) as Id, 
         ap.AppTypeId as AppointmentTypeId, 
         ap.Duration as DurationMinutes, 
         ap.AppointmentId as EncounterId, 
         COUNT_BIG(*) as __count_big
    FROM dbo.Appointments ap 
    JOIN dbo.PracticeCodeTable pct ON SUBSTRING(pct.Code, 1, 1) = ap.ScheduleStatus 
                                  AND pct.ReferenceType = 'AppointmentStatus' 
   WHERE ap.AppTime > 0
GROUP BY CONVERT(varchar(15), AppointmentId), ap.AppTypeId, ap.Duration, ap.AppointmentId

CREATE UNIQUE CLUSTERED INDEX [IX_Appointments_Partition1_Id]
ON model.Appointments_Partition1 ([Id]);

I get:

Msg 8668, Level 16, State 0, Line 12
Cannot create the clustered index 'IX_Appointments_Partition1_Id' on view 'PracticeRepository.model.Appointments_Partition1' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.

I'm including count_big...so why is the group by a problem?....and how can I resolve the error?

分享到QQ

分享到微博