SQL服务器 - 关键字的选择列表和代名词
我有两个表:SQL服务器 - 关键字的选择列表和代名词
关键词
在哪里存储唯一的关键字。
CREATE TABLE [dbo].[Keywords]
[KeywordID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](200) NOT NULL
select * from Keywords
1 MVC
2 HTML
3 C#
4 ASP.NET MVC
5 MVC3
KeywordSynonymous
,我指出某些关键字是同义他人。
CREATE TABLE [dbo].[KeywordSynonymous]
[KeywordID] [int] NOT NULL,
[KeywordSynonymousID] [int] NOT NULL
这两个字段都是关键字表的FK,这两个组合字段在此表上都用作PK。
在这里,我想说'MVC'和'MVC3'是同义词,也许'MVC3'和'ASP.NET MVC'也是同义词。
select * from KeywordSynonymous
1 5
5 4
CONCEPTS
1)
如果关键字 'MVC' 是同义 'MVC3' 的
和 'MVC3' 是同义的 'ASP.NET MVC'
然后在概念上MVC是ALSO代名词 'ASP.NET MVC'
2)
如果关键字 'MVC' 的代名词 'MVC3'
那么它也是真实的反之亦然和那'MVC3是'MVC'的同义词
问题
在我的网站想象我在做一个搜索,并且用户可以输入任何东西,但是对于我们的例子,他可以键入“MVC”或“MVC3” ......
我怎么能用一条SQL语句获得所有可能的同义词,确保符合概念1和2?
意思是说:
>> if the user types 'MVC', my sql should return 'MVC, MVC3', 'ASP.NET MVC'.
>> if the user types 'MVC3', my sql should return 'MVC, MVC3', 'ASP.NET MVC'.
>> if the user types 'ASP.NETMVC', my sql should return 'MVC, MVC3', 'ASP.NET MVC'.
=================================== =============================
UPDATE
我觉得我必须补充一点关于我的网站发展。这是一个市场,年轻的专业人士可以通过新的方式推销自己的服务。
由于我们想要允许任何职业,我现在无法预见什么“关键字”会定义更好的每个职业。所以我会允许用户定义这些关键字。
我的问题是我需要允许UserX通过专业和关键字搜索这些年轻的专业人士。我需要允许这些用户将其搜索到的关键字与现有关键字进行匹配,以便当前和未来的搜索将自动匹配正确的配置文件。
这就是为什么我没有预先提供所有关键字,并且肯定无法识别未来的关键字及其各自的同义词。我也不能指望用户将所有现有的关键字匹配到所有相关的关键字...所以这就是为什么我需要Concept 1工作。
============================================= ===================
计算器TAGS
关键字的模块应该工作非常类似于*的标签(关键词),其中,如果我设置的标签是SQL ,你们正在搜索TSQL或SQL SERVER ......也应该看到这篇文章。
:-)
好了,这个怎么样:
DECLARE @TempKeywordID TABLE (KeywordID int)
INSERT INTO @TempKeywordID (KeywordID)(select KeywordID from Keywords where [Description] = @SearchKeyword)
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=(Select Count(KeywordSynonymousID) from KeywordSynonymous)) --Loop for all records in KeywordSynonymous
BEGIN
INSERT INTO @TempKeywordID (KeywordID)(Select KeywordSynonymousID from KeywordSynonymous where KeywordID in (Select KeywordID from @TempKeywordID))
INSERT INTO @TempKeywordID (KeywordID)(Select KeywordID from KeywordSynonymous where KeywordSynonymousID in (Select KeywordID from @TempKeywordID))
SET @intFlag = @intFlag + 1
END
SELECT * FROM Keywords WHERE KeywordID IN (SELECT * FROM @TempKeywordID)
1被称为Symmetric Relation和2被称为Transitive Relation。
我建议您在添加新关键字时进行维护。你可以这样做。将关键字添加到数据库时,如果已经没有同义词,则指定它为“主”关键字。否则,将新关键字链接到现有的主关键字。
下面是一个存储过程添加新的关键字是这样的:你通过一个新的关键字添加和可选你传递一个已知的代名词
CREATE PROCEDURE [dbo].[AddKeyword]
@newKeyword [varchar](200),
@synonymKeyword [varchar](200) = NULL
AS
BEGIN
SET NOCOUNT ON;
set transaction isolation level serializable
begin transaction
if EXISTS (select 1 from Keywords where [Description] = @newKeyword)
begin
commit transaction
return
end
declare @masterKeywordId int
select
@masterKeywordId = ISNULL(KeywordSynonymous.KeywordID, Keywords.KeywordID)
from
Keywords
left join
KeywordSynonymous
on
Keywords.KeywordID = KeywordSynonymous.KeywordSynonymousID
where
[Description] = @synonymKeyword
insert into Keywords VALUES (@newKeyword)
if @masterKeywordId is not null
insert into KeywordSynonymous VALUES (@masterKeywordId,SCOPE_IDENTITY())
commit transaction
END
在此存储过程。这个同义词不一定是“主人”。如果存在,则会查找“主”关键字id,新创建的关键字将与该“主”id相链接。
这就是你到底怎么选择他们都:
CREATE PROCEDURE [dbo].[GetSynonymKeywords]
@keyword [varchar](200)
AS
BEGIN
SET NOCOUNT ON;
declare @masterKeywordId int
select
@masterKeywordId = ISNULL(KeywordSynonymous.KeywordID, Keywords.KeywordID)
from
Keywords
left join
KeywordSynonymous
on
Keywords.KeywordID = KeywordSynonymous.KeywordSynonymousID
where
[Description] = @keyword
select
KeywordId,[Description]
from
Keywords
where
KeywordId = @masterKeywordId
union
select
Keywords.KeywordId,[Description]
from
KeywordSynonymous
join
Keywords
on
KeywordSynonymous.KeywordSynonymousID = Keywords.KeywordId
where
KeywordSynonymous.KeywordId = @masterKeywordId
END
这个存储过程首先找到给予通过相应关键字的ID。然后它查找这个id的“主”关键字。然后它将返回master关键字和所有与此master关键字同义的关键字。
增加新词的例子:
EXEC [dbo].[AddKeyword] @newKeyword = N'MVC'
EXEC [dbo].[AddKeyword] @newKeyword = N'ASP.NET MVC', @synonymKeyword = 'MVC'
EXEC [dbo].[AddKeyword] @newKeyword = N'MVC3', @synonymKeyword = 'ASP.NET MVC'
注意的是,在第三行,你可以指定“MVC”的代名词,它会工作一样好。
检索关键字的例子:
[dbo].[GetSynonymKeywords] @keyword = N'MVC3'
[dbo].[GetSynonymKeywords] @keyword = N'ASP.NET MVC'
[dbo].[GetSynonymKeywords] @keyword = N'MVC3'
所有这三个返回值相同的列表。
我将隔离级别设置为在AddKeyword SP中进行序列化,以确保没有并发性问题随意根据并发模型进行修改,序列化可能不适合您。
如果您愿意,还可以将GetMasterId(出现在两个SP中的块)拉出到UDF中,或者执行适合您特定场景的任何其他修改。
由于您的条件(概念),同义表是未规范化。这是你的问题的主要来源,需要复杂的查询/触发器来解决它。
我会保持关键字表:
CREATE TABLE [dbo].[Keywords]
[KeywordID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](200) NOT NULL
select * from Keywords
1 MVC
2 HTML
3 C#
4 ASP.NET MVC
5 MVC3
6 C sharp
,使同义表是不同的:
CREATE TABLE [dbo].[KeywordSynonymity]
[SynonymityID] [int] NOT NULL,
[KeywordID] [int] NOT NULL
select * from KeywordSynonymous
1 1 --- for the 1 (MVC) and 5 (MVC3)
1 5 --- being synonymous
2 3 --- for the 3 (C#) and 6 (C sharp)
2 6 --- being synonymous
然后添加MVC3
和ASP.NET MVC
也是同义的,你只需要添加同义词表中的行(1,4)。如果然后 - 由于未知的原因,但让我们假设 - 尽管如此,您想要合并MVC3
和C#
作为同义词,您必须将同义词ID = 2(与C#同义)的所有行更改为= 1(与MVC同义) )。
但是,所有的查询都会比较简单,因为表格是标准化的。
你一定要使用公用表表达式。这对于您的问题来说是理想的解决方案,因为它不会改变您当前的数据库模式,最重要的是,由于您的KeywordSynonymous
表具有递归性,因此CTE是一种优雅而合乎逻辑的解决方案。
要做到这一点,最好先创建一个视图,在两个方向上选择KeywordSynonymous中的所有行。在你的情况,该表返回的行
select * from KeywordSynonymous
1 5
5 4
什么下面的看法会做是为了显示
select * from KeywordSynonymousAll
1 5 0
2 NULL 0
3 NULL 0
4 NULL 0
4 5 1
5 1 1
5 4 0
这种观点的数据结构,将简化递归查询。它添加了第三列以确定何时进行了回复。这是满足您的第二个概念所必需的。
所以,在这里它的观点:
create view KeywordSynonymousAll as
select KeywordID, KeywordSynonymousID, 0 as reversed
from KeywordSynonymous
union
select K.KeywordID, null as KeywordSynonymousID, 0 as reversed
from Keywords K
where not exists(select null
from KeywordSynonymous
where KeywordID = K.KeywordID)
union
select KeywordSynonymousID, KeywordID, 1 as reversed
from KeywordSynonymous
和查询
declare @search varchar(200);
set @search = 'MVC3'; -- TEST HERE for different search keywords
with Synonymous (keywordID, SynKeywordID) as (
-- initial state: Get the keywordId and KeywordSynonymousID for the description as @search
select K.keywordID, KS.KeywordSynonymousID
from Keywords K
inner join KeywordSynonymous KS on KS.KeywordID = K.keywordId
where K.Description = @search
union all
-- also initial state but with reversed columns (because we want lookup in both directions)
select KS.KeywordSynonymousID, K.keywordID
from Keywords K
inner join KeywordSynonymous KS on KS.KeywordSynonymousID = K.keywordId
where K.Description = @search
union all
select S.SynKeywordID, KS.KeywordSynonymousID
from Synonymous S
inner join KeywordSynonymousAll KS on KS.KeywordID = S.SynKeywordID
where KS.reversed = 0 -- to avoid infinite recursion
union all
select KS.KeywordSynonymousID, S.SynKeywordID
from Synonymous S
inner join KeywordSynonymousAll KS on KS.KeywordID = S.KeywordID
where KS.reversed = 1 -- to avoid infinite recursion
)
-- finally output the result
select distinct K.Description
from Synonymous S
inner join Keywords K on K.KeywordID = S.keywordID
对于set @search = 'MVC3'
,结果集
ASP.NET MVC
MVC
MVC3
相同的结果集发生了set @search = 'MVC'
和set @search = 'ASP.NET MVC'
为set @search = 'C#'
和set @search = 'HTML'
你什么也得不到
编辑
在我以前的帖子,我说,结果集将是空的C#和HTML。如果您还想返回这些值,然后更改查询的最后一部分:
-- finally output the result
select distinct T.Description
from (
select K.Description
from Synonymous S
inner join Keywords K on K.KeywordID = S.keywordID
union
select Description
from Keywords
where Description = @search) T
现在,set @search = 'C#'
,结果集
C#
和set @search = 'HTML'
,结果集为
HTML
希望这有助于
你可能会更好过存储'KeywordSynonymous'所有组合了前面,而不是穿越的路径每个'select' – 2011-04-14 20:33:41
你必须去自制?任何不使用同义词词典的全文索引的理由?这里是MS的架构答案:http://msdn.microsoft.com/en-us/library/ms142541.aspx – 2011-04-14 20:49:32
@Martin >>这个表格将由网站的用户填充。他们会在需要的基础上添加关键字(几乎我们不限制进入的内容)。目前,管理员将每隔XX周管理同义词条......但我们正在考虑将此功能给予用户自己,他们将在这里“暗示”管理员2个关键字是同义词。管理员不会知道所有可能的匹配...这就是为什么您提出的解决方案是不可能的。 – 2011-04-15 02:38:16