有表student
student_name student_number Description
azhugg 101 家住重庆沙坪坝
azhugg1 101 家住上海闸北区
azhugg3 102 家住北京海淀园
azhugg4 105 家住上海虹口区
NULL NULL 家住上海徐汇区
SELECT *
FROM student STBL
INNER JOIN
FREETEXTTABLE (student, Description,
'家住重庆沙坪坝') AS KTBL
ON STBL.studentID = KTBL.KEY
WHERE KTBL.RANK >= 10
ORDER BY KTBL.RANK DESC;
报错:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'KEY'.咋办?
student_name student_number Description
azhugg 101 家住重庆沙坪坝
azhugg1 101 家住上海闸北区
azhugg3 102 家住北京海淀园
azhugg4 105 家住上海虹口区
NULL NULL 家住上海徐汇区
SELECT *
FROM student STBL
INNER JOIN
FREETEXTTABLE (student, Description,
'家住重庆沙坪坝') AS KTBL
ON STBL.studentID = KTBL.KEY
WHERE KTBL.RANK >= 10
ORDER BY KTBL.RANK DESC;
报错:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'KEY'.咋办?
student_name student_number Description studentID
azhugg 101 家住重庆沙坪坝 1
azhugg3 102 家住北京海淀园 2
azhugg1 103 家住上海闸北区 3
azhugg4 105 家住上海虹口区 4
azhugg5 109 家住上海徐汇区 5
azhugg6 110 家住重庆沙坪坝11 6主键student_number采用最新修正的语句,
SELECT *
FROM student STBL
INNER JOIN
FREETEXTTABLE (student, Description,
'家住重庆沙坪坝') AS KTBL
ON STBL.student_number = KTBL.[KEY]
where ktbl.rank>=10
order by ktbl.rank desc;得到一行:
azhugg 101 家住重庆沙坪坝 1想得到
azhugg 101 家住重庆沙坪坝 1
azhugg6 110 家住重庆沙坪坝11 6两位已经辛苦了,我会加分的。另外请教freetexttable和containstable区别。
-- Author: liangCK 小梁
-- Date : 2008-11-15 12:00:35
---------------------------------
--> 生成测试数据: [student]
IF OBJECT_ID('[student]') IS NOT NULL DROP TABLE [student]
CREATE TABLE [student] (student_name VARCHAR(7),student_number INT,Description NVARCHAR(MAX),studentID INT NOT NULL)
INSERT INTO [student]
SELECT 'azhugg',101,'家住重庆沙坪坝',1 UNION ALL
SELECT 'azhugg3',102,'家住北京海淀园',2 UNION ALL
SELECT 'azhugg1',103,'家住上海闸北区',3 UNION ALL
SELECT 'azhugg4',105,'家住上海虹口区',4 UNION ALL
SELECT 'azhugg5',109,'家住上海徐汇区',5 UNION ALL
SELECT 'azhugg6',110,'家住重庆沙坪坝11',6--SQL查询如下:
GOALTER TABLE student
ADD CONSTRAINT PK_STUDENTID
PRIMARY KEY(studentID)
GOCREATE FULLTEXT CATALOG FTCatalog
IN PATH N'D:\FT_MyTest'
AS DEFAULTCREATE FULLTEXT INDEX
ON student(Description)
KEY INDEX PK_STUDENTID GOWAITFOR DELAY '00:00:10'SELECT *
FROM student AS STBL
JOIN FREETEXTTABLE(student,Description,N'"家住重庆沙坪坝"') AS KTBL
ON STBL.studentID=KTBL.[KEY]
ORDER BY KTBL.[RANK] DESCGODROP FULLTEXT INDEX ON student
DROP FULLTEXT CATALOG FTCatalog
DROP TABLE student/*
student_name student_number Description studentID KEY RANK
------------ -------------- -------------------- ----------- ----------- -----------
azhugg 101 家住重庆沙坪坝 1 1 731
azhugg6 110 家住重庆沙坪坝11 6 6 731(2 行受影响)
*/
FROM student AS STBL
JOIN FREETEXTTABLE(student,Description,N'"家住重庆沙坪坝*"') AS KTBL
ON STBL.studentID=KTBL.[KEY]
ORDER BY KTBL.[RANK] DESC
containstable
freetext
freetexttable从不同角度分组从语法和功能上来分, contains与freetext相似, freetextable与containstable相似
从精确度上来分, contains与containstable相似,freetext与freetexttable相似.
freetext功能似contains, 只是它用来呈现所有相似记录,不如contains精确。freetexttable与containstable以数据集合和格式回传结果。
SELECT *
FROM student AS STBL
JOIN FREETEXTTABLE(student,Description,N'"家住重庆沙坪坝*"') AS KTBL
ON STBL.studentID=KTBL.[KEY]
ORDER BY KTBL.[RANK] DESC我这里只能得到1行
azhugg 101 家住重庆沙坪坝 1 1 55那个
azhugg6 110 家住重庆沙坪坝11 6
还是得不到,咋办?我的英文版sql2005dev,这个有关系么?
用containstable 代替 freetexttable就可以了。
用containstable 代替 freetexttable就可以了。汗
用了您给的代码定义全部.....汗
我的description 用的是NVARCHAR(MAX),这个max代表啥意思?