SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER VIEW [dbo].[v_HotelPagination]
WITH SCHEMABINDING
AS
SELECT dbo.HotelInfo.ID, dbo.HotelInfo.Group_id, dbo.HotelInfo.Name, dbo.HotelInfo.Alias, dbo.HotelInfo.County, dbo.HotelInfo.Address, dbo.HotelInfo.Longitude, dbo.HotelInfo.Latitude,
dbo.HotelInfo.District,
HP.minPrice, HP.maxPrice,rtp.hotel_id, RTP.num
FROM dbo.HotelInfo INNER JOIN
(SELECT Hotel_Id, MIN(Price) AS minPrice, MAX(Price) AS maxPrice
FROM dbo.RoomTypePrice
GROUP BY Hotel_Id) AS HP ON dbo.HotelInfo.ID = HP.Hotel_Id
INNER JOIN
(SELECT Hotel_Id, COUNT(1) AS num
FROM dbo.RoomTypePrice AS RoomTypePrice_1
GROUP BY Hotel_Id) AS RTP ON dbo.HotelInfo.ID = RTP.Hotel_Id
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO提供错误有两个:
1,该视图没有唯一的聚集索引。
2.它包含一个或多个不允许不允许使用的构造。求解
GO
SET QUOTED_IDENTIFIER ON
GOALTER VIEW [dbo].[v_HotelPagination]
WITH SCHEMABINDING
AS
SELECT dbo.HotelInfo.ID, dbo.HotelInfo.Group_id, dbo.HotelInfo.Name, dbo.HotelInfo.Alias, dbo.HotelInfo.County, dbo.HotelInfo.Address, dbo.HotelInfo.Longitude, dbo.HotelInfo.Latitude,
dbo.HotelInfo.District,
HP.minPrice, HP.maxPrice,rtp.hotel_id, RTP.num
FROM dbo.HotelInfo INNER JOIN
(SELECT Hotel_Id, MIN(Price) AS minPrice, MAX(Price) AS maxPrice
FROM dbo.RoomTypePrice
GROUP BY Hotel_Id) AS HP ON dbo.HotelInfo.ID = HP.Hotel_Id
INNER JOIN
(SELECT Hotel_Id, COUNT(1) AS num
FROM dbo.RoomTypePrice AS RoomTypePrice_1
GROUP BY Hotel_Id) AS RTP ON dbo.HotelInfo.ID = RTP.Hotel_Id
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO提供错误有两个:
1,该视图没有唯一的聚集索引。
2.它包含一个或多个不允许不允许使用的构造。求解
表里建个索引...
SELECT dbo.HotelInfo.ID, dbo.HotelInfo.Group_id, dbo.HotelInfo.Name, dbo.HotelInfo.Alias, dbo.HotelInfo.County, dbo.HotelInfo.Address, dbo.HotelInfo.Longitude, dbo.HotelInfo.Latitude,
dbo.HotelInfo.District,
HP.minPrice, HP.maxPrice,rtp.hotel_id, RTP.num
FROM dbo.HotelInfo INNER JOIN
(SELECT top 10000000 Hotel_Id, MIN(Price) AS minPrice, MAX(Price) AS maxPrice
FROM dbo.RoomTypePrice
GROUP BY Hotel_Id) AS HP ON dbo.HotelInfo.ID = HP.Hotel_Id
INNER JOIN
(SELECT top 10000000 Hotel_Id, COUNT(1) AS num
FROM dbo.RoomTypePrice AS RoomTypePrice_1
GROUP BY Hotel_Id) AS RTP ON dbo.HotelInfo.ID = RTP.Hotel_Id
因为有with schemabinding 子句创建的视图不能包含别名数据列
去掉with schemabinding 后,是不能给视图建立索引了吧。
如果使用了聚合函数,则必须在SELECT列表里包含COUNT_big(*)