全文检索Sql测试

创建testsort表

if exists (select * from sysobjects where id = OBJECT_ID('[testsort]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [testsort]

CREATE TABLE [testsort] ( [id] [int]  IDENTITY (1, 1)  NOT NULL, [sortname] [nvarchar]  (50) NOT NULL)

ALTER TABLE [testsort] WITH NOCHECK ADD  CONSTRAINT [PK_testsort] PRIMARY KEY  NONCLUSTERED ( [id] )SET IDENTITY_INSERT [testsort] ON

INSERT [testsort] ([id],[sortname]) VALUES ( 1,'水果') INSERT [testsort] ([id],[sortname]) VALUES ( 2,'蔬菜')

SET IDENTITY_INSERT [testsort] OFF

------------------------------------------------------------------------------------------------

创建test表

if exists (select * from sysobjects where id = OBJECT_ID('[test]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [test]

CREATE TABLE [test] ( [id] [int]  IDENTITY (1, 1)  NOT NULL, [id1] [char]  (10) NOT NULL, [name] [nvarchar]  (50) NOT NULL)

ALTER TABLE [test] WITH NOCHECK ADD  CONSTRAINT [PK_test] PRIMARY KEY  NONCLUSTERED ( [id] )SET IDENTITY_INSERT [test] ON

INSERT [test] ([id],[id1],[name]) VALUES ( 1,'1','苹果') INSERT [test] ([id],[id1],[name]) VALUES ( 2,'1','香蕉') INSERT [test] ([id],[id1],[name]) VALUES ( 3,'2','菠菜') INSERT [test] ([id],[id1],[name]) VALUES ( 4,'2','红萝卜')

SET IDENTITY_INSERT [test] OFF

------------------------------------------------------------------------------------------------

--  执行错误 SELECT name FROM test WHERE CONTAINS(name|id1, ' "香*"  ') GO --  执行成功 SELECT sortname FROM testsort WHERE CONTAINS(sortname, ' "水*"  ') GO --  执行成功 SELECT ts.sortname,t.name FROM test as t inner join testsort as ts on ts.id = t.id1 WHERE CONTAINS(t.name, ' "香*"  ') or CONTAINS(ts.sortname, ' "水果"  ') GO --  执行成功 SELECT ts.sortname,t.name FROM test as t inner join testsort as ts on ts.id = t.id1 WHERE CONTAINS(t.*, ' "香*"  ') or CONTAINS(ts.*, ' "水果"  ') GO --  执行错误 SELECT ts.sortname,t.name FROM test as t inner join testsort as ts on ts.id = t.id1 WHERE CONTAINS(test.name | *, ' "香*" and "1"  ') GO --  执行错误 SELECT ts.sortname,t.name FROM test as t,testsort as ts WHERE CONTAINS(t., ' "香*" and "1"  ') GO --  执行成功 SELECT [key], [rank] FROM CONTAINSTABLE(test, *, '"香*"') --  执行成功 select * from test WHERE CONTAINS(*, ' "香*"  ')