全文检索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 nameFROM testWHERE CONTAINS(name|id1, ' "香*"  ')GO--  执行成功SELECT sortnameFROM testsortWHERE CONTAINS(sortname, ' "水*"  ')GO--  执行成功SELECT ts.sortname,t.nameFROM test as tinner join testsort as tson ts.id = t.id1WHERE CONTAINS(t.name, ' "香*"  ') or CONTAINS(ts.sortname, ' "水果"  ')GO--  执行成功SELECT ts.sortname,t.nameFROM test as tinner join testsort as tson ts.id = t.id1WHERE CONTAINS(t.*, ' "香*"  ') or CONTAINS(ts.*, ' "水果"  ')GO--  执行错误SELECT ts.sortname,t.nameFROM test as tinner join testsort as tson ts.id = t.id1WHERE CONTAINS(test.name | *, ' "香*" and "1"  ')GO--  执行错误SELECT ts.sortname,t.nameFROM test as t,testsort as tsWHERE CONTAINS(t., ' "香*" and "1"  ')GO--  执行成功SELECT [key], [rank] FROM CONTAINSTABLE(test, *, '"香*"')--  执行成功select * from test WHERE CONTAINS(*, ' "香*"  ')