34,576
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE Info
(
id INT IDENTITY(1,1) PRIMARY KEY,
title nvarchar(50),
[content] nvarchar(MAX)
)
INSERT INTO Info VALUES('山东今年土豆丰收','恭喜了')
INSERT INTO Info VALUES('山东今年土豆、花生丰收','很不错')
INSERT INTO Info VALUES('山东丰收年','据悉今年花生、土豆的收成都不错')
INSERT INTO Info VALUES('丰收了','山东花生土豆丰收啦')
INSERT INTO Info VALUES('山东土豆丰收','今年山东土豆丰收')
INSERT INTO Info VALUES('哪里的土豆丰收?','好像是山东的')
INSERT INTO Info VALUES('今年是个丰收年','土豆收成不错哦')
select * from
(
select * from info
where title like '%土豆%' or [content] like '%土豆%'
) t
where title like '%山东%' or [content] like '%山东%'
SELECT * FROM Info
WHERE (title LIKE '%土豆%' or title like '%土豆%')
AND ([content] LIKE '%山东%' or [content] like '%山东%')
declare @a table
(
id INT IDENTITY(1,1) PRIMARY KEY,
title nvarchar(50),
[content] nvarchar(100)
)
INSERT INTO @a VALUES('山东今年土豆丰收','恭喜了')
INSERT INTO @a VALUES('山东今年土豆、花生丰收','很不错')
INSERT INTO @a VALUES('山东丰收年','据悉今年花生、土豆的收成都不错')
INSERT INTO @a VALUES('丰收了','山东花生土豆丰收啦')
INSERT INTO @a VALUES('山东土豆丰收','今年山东土豆丰收')
INSERT INTO @a VALUES('哪里的土豆丰收?','好像是山东的')
INSERT INTO @a VALUES('今年是个丰收年','土豆收成不错哦')
select * from @a
where sign(charindex('山东',title)+charindex('山东',content))
& sign(charindex('土豆',title)+charindex('土豆',content))=1
--result
/*id title content
----------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------
1 山东今年土豆丰收 恭喜了
2 山东今年土豆、花生丰收 很不错
3 山东丰收年 据悉今年花生、土豆的收成都不错
4 丰收了 山东花生土豆丰收啦
5 山东土豆丰收 今年山东土豆丰收
6 哪里的土豆丰收? 好像是山东的
(所影响的行数为 6 行)*/
SELECT * FROM Info
WHERE cast(charindex('土豆',title) as bit)&cast(charindex('山东',title+','+[content]) as bit)|
cast(charindex('土豆',[content]) as bit)&cast(charindex('山东',title+','+[content]) as bit)=1
5楼可以改为这样CREATE TABLE Info
(
id INT IDENTITY(1,1) PRIMARY KEY,
title nvarchar(50),
[content] nvarchar(MAX)
)
INSERT INTO Info VALUES('山东今年土豆丰收','恭喜了')
INSERT INTO Info VALUES('山东今年土豆、花生丰收','很不错')
INSERT INTO Info VALUES('山东丰收年','据悉今年花生、土豆的收成都不错')
INSERT INTO Info VALUES('丰收了','山东花生土豆丰收啦')
INSERT INTO Info VALUES('山东土豆丰收','今年山东土豆丰收')
INSERT INTO Info VALUES('哪里的土豆丰收?','好像是山东的')
INSERT INTO Info VALUES('今年是个丰收年','土豆收成不错哦')
--SELECT * FROM Info
SELECT * FROM Info
WHERE cast(charindex('土豆',title) as bit)&cast(charindex('山东',title+','+[content]) as bit)=1 or
cast(charindex('土豆',[content]) as bit)&cast(charindex('山东',title+','+[content]) as bit)=1
/*
id title content
----------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 山东今年土豆丰收 恭喜了
2 山东今年土豆、花生丰收 很不错
3 山东丰收年 据悉今年花生、土豆的收成都不错
4 丰收了 山东花生土豆丰收啦
5 山东土豆丰收 今年山东土豆丰收
6 哪里的土豆丰收? 好像是山东的
(6 行受影响)
*/
上面错了,改下CREATE TABLE Info
(
id INT IDENTITY(1,1) PRIMARY KEY,
title nvarchar(50),
[content] nvarchar(MAX)
)
INSERT INTO Info VALUES('山东今年土豆丰收','恭喜了')
INSERT INTO Info VALUES('山东今年土豆、花生丰收','很不错')
INSERT INTO Info VALUES('山东丰收年','据悉今年花生、土豆的收成都不错')
INSERT INTO Info VALUES('丰收了','山东花生土豆丰收啦')
INSERT INTO Info VALUES('山东土豆丰收','今年山东土豆丰收')
INSERT INTO Info VALUES('哪里的土豆丰收?','好像是山东的')
INSERT INTO Info VALUES('今年是个丰收年','土豆收成不错哦')
SELECT * FROM Info
WHERE cast(charindex('土豆',title) as bit)&cast(charindex('山东',title+','+[content]) as bit)^
cast(charindex('土豆',[content]) as bit)&cast(charindex('山东',title+','+[content]) as bit)=1
/*
id title content
----------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 山东今年土豆丰收 恭喜了
2 山东今年土豆、花生丰收 很不错
3 山东丰收年 据悉今年花生、土豆的收成都不错
4 丰收了 山东花生土豆丰收啦
6 哪里的土豆丰收? 好像是山东的
(5 行受影响)
*/
SELECT * FROM Info
WHERE (title LIKE '%土豆%' AND (title like '%山东%' or [content] like '%山东%'))
or ([content] like '%土豆%' AND (title like '%山东%' or [content] like '%山东%')
)
SELECT * FROM Info
WHERE (title LIKE '%土豆%' AND ([content] LIKE '%山东%' or [content] like '%山东%'))
or (title like '%土豆%' AND ([content] LIKE '%山东%' or [content] like '%山东%')
)