日期:2014-05-17 浏览次数:20542 次
create table 新闻表(Id int, Tags varchar(50))
insert into 新闻表
select 1, '汽车,银行' union all
select 2, '银行,黄金' union all
select 3, '汽车' union all
select 4, '美食'
-- 第一条新闻
select Id, Tags
from 新闻表
where Id=1
-- 相关新闻
select distinct c.Id, c.Tags
from 新闻表 c
inner join
(select substring(a.Tags,b.number,charindex(',',a.Tags+',',b.number)-b.number) 'TagItem'
from 新闻表 a
inner join master.dbo.spt_values b
on b.[type]='P' and b.number between 1 and len(a.Tags) and substring(','+a.Tags,b.number,1) = ','
where a.Id=1) d on charindex(d.TagItem,c.Tags,1)>0
where c.Id<>1
/*
Id Tags
----------- --------------------------------------------------
2 银行,黄金
3 汽车
(2 row(s) affected)
*/
CREATE TABLE t1
(
id INT,
col VARCHAR(20)
)
INSERT INTO t1
SELECT 1,'汽车,银行' UNION ALL
SELECT 2,'银行,黄金' UNION ALL
SELECT 3,'汽车' UNION ALL
SELECT 4,'美食'
SELECT * FROM t1
;WITH AAA AS
(
SELECT SUBSTRING(A.col,B.number,CHARINDEX(',',A.col+',',B.number)-B.number) AS col
FROM t1 AS A WITH(NOLOCK) INNER JOIN
master..spt_values AS B ON B.number<=DATALENGTH(A.col)+1
AND SUBSTRING(','+A.col,B.number,1)=','
AND A.id=1
AND B.[type]='P'
)
SELECT A.*
FROM t1 AS A INNER JOIN
AAA AS B ON CHARINDEX(B.col,A.col)>0
AND A.id>1