問題描述

我想為全文搜索創建一個索引視圖.

i want to create a Index View for full text search.

我面臨子查詢的唯一問題，因為索引視圖不允許子查詢.

the only problem i,m facing with subquery, because index views does not allow subquery.

下面是我的查詢

ALTER VIEW [dbo].[Demo] with SCHEMABINDING  AS
select distinct a.ID,a.Title, a.Description ,b.Name as Recipe, c.Name as Taste , d.Name as CuisineType,
STUFF((SELECT ',' + Name FROM dbo.Ingredients where ID in (select IngredientID from dbo.listingIngredients 
where listingid = a.ID ) FOR XML PATH('')), 1, 1, '') as Ingredients
from dbo.Listing as a 
inner join dbo.RecipeType b on a.RecipeTypeID = b.ID
inner join dbo.taste c on a.tasteID = c.ID
inner join dbo.CuisineType d on a.CuisineTypeID = d.ID
inner join dbo.listingIngredients e on a.ID = e.listingID
GO

我使用子查詢從使用 STUFF 的成分表中獲取成分作為連接字符串.

I,m using subquery to get ingredients as concatenate string from Ingredients table using STUFF.

有人可以讓我知道如何刪除此子查詢并將成分作為內容字符串.

can some one please let me know how can i remove this subquery and have ingredients as contented string.

請告訴我

問候男子氣概

推薦答案

查詢的 XML 部分會導致問題，即使您確實設法刪除了子選擇.

The XML part of the query will cause problems, even if you did manage to remove the sub-selected.

然而，一切都沒有丟失.您可以將視圖重寫為可以編入索引的部分和另一個更便宜但不能編入索引的部分.例如，您可以這樣寫:

However, all is not lost. You could rewrite the view into a part that can be indexed and another part that is cheaper, but can't. For example, you could write:

ALTER VIEW [dbo].[Demo_Part] with SCHEMABINDING  AS
select a.ID,a.Title
, a.Description 
, b.Name as Recipe
, c.Name as Taste 
, d.Name as CuisineType
, e.name
from dbo.Listing as a 
inner join dbo.RecipeType b on a.RecipeTypeID = b.ID
inner join dbo.taste c on a.tasteID = c.ID
inner join dbo.CuisineType d on a.CuisineTypeID = d.ID
inner join dbo.listingIngredients e on a.ID = e.listingID
GROUP BY a.ID,a.Title
, a.Description 
, b.Name as Recipe
, c.Name as Taste 
, d.Name as CuisineType
, e.name

根據您的數據模型，您甚至可能不需要 group by.此視圖可以編入索引

Depending on your data model, you may not even need the group by. This view can be indexed

然后編寫另一個未編入索引但替換原始視圖的視圖

And then write another view that is not indexed, but which replaces your original view

CREATE VIEW [dbo].[Demo]
SELECT ...
 STUFF (...)
FROM [dbo].[Demo_Part]

作為一個元答案，我想補充一點，如果您需要索引這樣的視圖(并使用 DISTINCT)，很可能您的數據建模者在數據模型上犯了很大的錯誤，或者您的數據訪問代碼是非常低效.關于這一切的一切都像是在努力解決糟糕的編碼和建模實踐.

As a meta-answer I would add that if you need to index a view like this (and use the DISTINCT), chances are that your data modeller made a pretty big mistake with the data model or that your data access code is very inefficient. Everything about this smells like you are trying to work around poor coding and modelling practices.

這篇關于從視圖中刪除子查詢以使其成為索引視圖的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！