================= TSQL XML列实现相关新闻的功能 =================

Question

declare @temp table
(
id int,
title nvarchar(50),
keywords xml
);
insert into @temp values(1,'叙利亚“救危”总理叛逃对阿萨德政权影响几何','<r><i>叙利亚</i><i>阿萨德</i></r>');
insert into @temp values(2,'难民大量涌入 宗教冲突恶化——叙利亚危机殃及周边国家','<r><i>叙利亚</i></r>');
insert into @temp values(3,'以色列执意打击伊朗','<r><i>伊朗</i></r>');
insert into @temp values(4,'利比亚即将尘埃落定，叙利亚何去何从','<r><i>叙利亚</i><i>利比亚</i></r>');
insert into @temp values(5,'国际人权组织就叙利亚阿勒颇轰炸发出警告','<r><i>阿萨德</i></r>');declare @id int;
set @id=1;
select * from @temp where id=@id;--这里查询出id=1的新闻，但同时我希望有另一个tsql查询出id=1的记录的keywords字段中每个i节点的值只在存在于其它记录中，就同时选择这条记录
--如这里应该是查询出记录id等于 2,4,5,因为记录2,4,5的keywords中都包含 id是1的keywords中的i值select * from @temp;高手指点。谢谢。

Answer 1

declare @temp table
(
id int,
title nvarchar(50),
keywords xml
)insert into @temp values(1,'叙利亚“救危”总理叛逃对阿萨德政权影响几何','<r><i>叙利亚</i><i>阿萨德</i></r>');
insert into @temp values(2,'难民大量涌入 宗教冲突恶化——叙利亚危机殃及周边国家','<r><i>叙利亚</i></r>');
insert into @temp values(3,'以色列执意打击伊朗','<r><i>伊朗</i></r>');
insert into @temp values(4,'利比亚即将尘埃落定，叙利亚何去何从','<r><i>叙利亚</i><i>利比亚</i></r>');
insert into @temp values(5,'国际人权组织就叙利亚阿勒颇轰炸发出警告','<r><i>阿萨德</i></r>');
declare @id int
set @id=1select * from @temp where id=@id
/*
id          title                                                keywords
----------- -------------------------------------------------- -----------------------------------
1           叙利亚“救危”总理叛逃对阿萨德政权影响几何                 <r><i>叙利亚</i><i>阿萨德</i></r>(1 row(s) affected)
*/
select * from @temp g
where g.id<>@id and
exists(
select 1 from 
(select o.value('.','varchar(10)') 'kw'
from (select * from @temp where id=@id) t
cross apply keywords.nodes('/r/i') x(o)) s1
inner join
(select o.value('.','varchar(10)') 'kw'
from (select * from @temp where id=g.id) t
cross apply keywords.nodes('/r/i') x(o)) s2
on s1.kw=s2.kw)
/*
id          title                                                    keywords
----------- -------------------------------------------------- --------------------------------------------
2           难民大量涌入 宗教冲突恶化——叙利亚危机殃及周边国家           <r><i>叙利亚</i></r>
4           利比亚即将尘埃落定，叙利亚何去何从                           <r><i>叙利亚</i><i>利比亚</i></r>
5           国际人权组织就叙利亚阿勒颇轰炸发出警告                       <r><i>阿萨德</i></r>(3 row(s) affected)
*/

Answer 2

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([id] INT,[title] VARCHAR(100),[keywords] XML)
INSERT [tb]
SELECT 1,'叙利亚“救危”总理叛逃对阿萨德政权影响几何','<r><i>叙利亚</i><i>阿萨德</i></r>' UNION ALL
SELECT 2,'难民大量涌入宗教冲突恶化——叙利亚危机殃及周边国家','<r><i>叙利亚</i></r>' UNION ALL
SELECT 3,'以色列执意打击伊朗','<r><i>伊朗</i></r>' UNION ALL
SELECT 4,'利比亚即将尘埃落定,叙利亚何去何从','<r><i>叙利亚</i><i>利比亚</i></r>' UNION ALL
SELECT 5,'国际人权组织就叙利亚阿勒颇轰炸发出警告','<r><i>阿萨德</i></r>'
--------------开始查询--------------------------declare @id int;
set @id=1;SELECT b.id FROM 
(
SELECT [id],t.value('(.)[1]', 'varchar(50)') [keywords] FROM tb CROSS
APPLY [keywords].nodes('/r/i') AS R(t)where id=1
) AS a
JOIN 
(
SELECT [id],t.value('(.)[1]', 'varchar(50)') [keywords] FROM tb CROSS
APPLY [keywords].nodes('/r/i') AS R(t)where id!=@id
) AS b
ON a.[keywords]=b.[keywords]

Answer 3

declare @id int;
set @id=1;SELECT t.* FROM 
(
SELECT [id],t.value('(.)[1]', 'varchar(50)') [keywords] FROM tb CROSS
APPLY [keywords].nodes('/r/i') AS R(t)where id=1
) AS a
JOIN 
(
SELECT [id],t.value('(.)[1]', 'varchar(50)') [keywords] FROM tb CROSS
APPLY [keywords].nodes('/r/i') AS R(t)where id!=@id
) AS b
ON a.[keywords]=b.[keywords]
JOIN tb AS t
ON b.id=t.id