SQL多关键字查询 并按精确度排序
缺点：无论关键词怎么变，都会把所有的数据搜索出来。/**********SQL2005************/
--> 生成测试数据: @tbDECLARE @tb TABLE (ID INT,CONTENT VARCHAR(100))INSERT INTO @tbSELECT 1,'JAVA是面对对象的语言' UNION ALLSELECT 2,'PHP学起来比JAVA要容易点' UNION ALLSELECT 3,'ASP比PHP用起来简单,PHP比JAVA用起来简单' UNION ALLSELECT 4,'FLEX做出来的界面好漂亮,而且可以和ASP,PHP,JAVA结合使用' UNION ALLSELECT 5,'ASP,PHP用来做网页都不错'--SQL查询如下:DECLARE @str varchar(100);SET @str = 'ASP JAVA PHP FLEX';SELECT * FROM @tb AS AORDER BY (SELECT COUNT(*) FROM ( SELECT B.x.value('.','varchar(50)') AS s FROM (SELECT CONVERT(xml,'<v>'+REPLACE(@str,' ','</v><v>')+'</v>') AS s) AS T CROSS APPLY T.s.nodes('//v') AS B(x)) AS T WHERE CHARINDEX(s,A.CONTENT) > 0) DESC/*ID CONTENT----------- ---------------------------4 FLEX做出来的界面好漂亮,而且可以和ASP,PHP,JAVA结合使用3 ASP比PHP用起来简单,PHP比JAVA用起来简单5 ASP,PHP用来做网页都不错2 PHP学起来比JAVA要容易点1 JAVA是面对对象的语言(5 行受影响)*/
/**********SQL2000************/
--> 生成测试数据: @tbDECLARE @tb TABLE (ID INT,CONTENT VARCHAR(100))INSERT INTO @tbSELECT 1,'JAVA是面对对象的语言' UNION ALLSELECT 2,'PHP学起来比JAVA要容易点' UNION ALLSELECT 3,'ASP比PHP用起来简单,PHP比JAVA用起来简单' UNION ALLSELECT 4,'FLEX做出来的界面好漂亮,而且可以和ASP,PHP,JAVA结合使用' UNION ALLSELECT 5,'ASP,PHP用来做网页都不错'--SQL查询如下:DECLARE @str varchar(100);SET @str = 'ASP JAVA PHP FLEX';SELECT * FROM @tb AS AORDER BY (SELECT COUNT(*) FROM (SELECT SUBSTRING(@str,number,CHARINDEX(' ',@str+' ',number)-number) AS s FROM master.dbo.spt_values WHERE type = 'p' AND SUBSTRING(' '+@str,number,1) = ' ' AND number <= LEN(@str)) AS T WHERE CHARINDEX(s,A.CONTENT) > 0) DESC/*ID CONTENT----------- ------------------------4 FLEX做出来的界面好漂亮,而且可以和ASP,PHP,JAVA结合使用3 ASP比PHP用起来简单,PHP比JAVA用起来简单5 ASP,PHP用来做网页都不错2 PHP学起来比JAVA要容易点1 JAVA是面对对象的语言(5 行受影响)*/
/***************其他类似方法****************************/
-> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb]GOCREATE TABLE [tb]([ID] INT,[CONTENT] NVARCHAR(40))INSERT [tb]SELECT 1,N'JAVA是面对对象的语言' UNION ALLSELECT 2,N'PHP学起来比JAVA要容易点' UNION ALLSELECT 3,N'ASP比PHP用起来简单，PHP比JAVA用起来简单' UNION ALLSELECT 4,N'FLEX做出来的界面好漂亮，而且可以和ASP,PHP,JAVA结合使用' UNION ALLSELECT 5,N'ASP，PHP用来做网页都不错'GO--SELECT * FROM [tb]--创建对照表数据IF NOT OBJECT_ID('[关键字对照表]') IS NULL DROP TABLE [关键字对照表]GOCREATE TABLE [关键字对照表]([ID] INT IDENTITY,[name] NVARCHAR(40))INSERT [关键字对照表]SELECT 'JAVA' UNION ALLSELECT 'PHP' UNION ALLSELECT 'ASP' UNION ALLSELECT 'FLEX' GO-->SQL查询如下:--创建获取关键字数量的自字义函数：IF NOT OBJECT_ID('[fn_px]') IS NULL DROP FUNCTION [fn_px]GOCREATE FUNCTION fn_px(@str NVARCHAR(100))RETURNS INTASBEGIN RETURN(SELECT SUM(CASE WHEN CHARINDEX(NAME,@str)>0 THEN 1 ELSE 0 END) FROM [关键字对照表])ENDGO--调用函数查询如下:SELECT * FROM tb ORDER BY dbo.fn_px([CONTENT]) desc--结果：/*ID CONTENT----------- ----------------------------------------4 FLEX做出来的界面好漂亮，而且可以和ASP,PHP,JAVA结合使用3 ASP比PHP用起来简单，PHP比JAVA用起来简单5 ASP，PHP用来做网页都不错2 PHP学起来比JAVA要容易点1 JAVA是面对对象的语言(5 行受影响)*/
--關鍵字數量倒序，再ID順序排列
--> 生成测试数据表:tb IF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([ID] INT,[CONTENT]NVARCHAR(40)) INSERT [tb] SELECT 1,N'JAVA是面对对象的语言' UNION ALL SELECT 2,N'PHP学起来比JAVA要容易点' UNIONALL SELECT 3,N'ASP比PHP用起来简单，PHP比JAVA用起来简单' UNION ALL SELECT 4,N'FLEX做出来的界面好漂亮，而且可以和ASP,PHP,JAVA结合使用' UNION ALL SELECT 5,N'ASP，PHP用来做网页都不错' GO --SELECT * FROM [tb] --创建对照表数据 IF NOTOBJECT_ID('[关键字对照表]') IS NULL DROP TABLE [关键字对照表] GO CREATE TABLE [关键字对照表]([ID] INTIDENTITY,[name] NVARCHAR(40)) INSERT [关键字对照表] SELECT 'JAVA' UNION ALL SELECT 'PHP' UNION ALL SELECT 'ASP'UNION ALL SELECT 'FLEX' GO -->SQL查询如下: --创建获取关键字数量的自字义函数： IF NOT OBJECT_ID('[fn_px]') IS NULLDROP FUNCTION [fn_px] GO CREATE FUNCTION fn_px(@str NVARCHAR(100)) RETURNS INT AS BEGIN RETURN(SELECTSUM(CASE WHEN CHARINDEX(NAME,@str)>0 THEN 1 ELSE 0 END) FROM [关键字对照表]) END GO --调用函数查询如下:SELECT * FROM tb ORDER BY dbo.fn_px([CONTENT]) DESC,ID --结果： /* ID CONTENT ----------- ---------------------------------------- 4 FLEX做出来的界面好漂亮，而且可以和ASP,PHP,JAVA结合使用 3 ASP比PHP用起来简单，PHP比JAVA用起来简单 2 PHP学起来比JAVA要容易点 5 ASP，PHP用来做网页都不错 1 JAVA是面对对象的语言 (5 行受影响) */
--> 生成测试数据: @tbDECLARE @tb TABLE (ID INT,CONTENT VARCHAR(100))INSERT INTO @tbSELECT 1,'JAVA是面对对象的语言' UNION ALLSELECT 2,'PHP学起来比JAVA要容易点' UNION ALLSELECT 3,'ASP比PHP用起来简单,PHP比JAVA用起来简单' UNION ALLSELECT 4,'FLEX做出来的界面好漂亮,而且可以和ASP,PHP,JAVA结合使用' UNION ALLSELECT 5,'ASP,PHP用来做网页都不错' UNION ALLSELECT 6,'liangck'--SQL查询如下:DECLARE @str varchar(100);SET @str = 'ASP JAVA PHP FLEX';--将关键字转成行集表SELECT SUBSTRING(@str,number,CHARINDEX(' ',@str+' ',number)-number) AS sINTO #tmpFROM master.dbo.spt_valuesWHERE type = 'p' AND SUBSTRING(' '+@str,number,1) = ' ' AND number BETWEEN 1 AND LEN(@str)--查找,排序SELECT * FROM @tb AS AWHERE EXISTS(SELECT * FROM #tmp WHERE CHARINDEX(s,A.CONTENT) > 0)ORDER BY (SELECT COUNT(*) FROM #tmp WHERE CHARINDEX(s,A.CONTENT) > 0) DESC, ID; --如果关键字出现次数一样,按ID从小到大排序.--删除临时表DROP TABLE #tmp;/*ID CONTENT----------- -----------------------4 FLEX做出来的界面好漂亮,而且可以和ASP,PHP,JAVA结合使用3 ASP比PHP用起来简单,PHP比JAVA用起来简单5 ASP,PHP用来做网页都不错2 PHP学起来比JAVA要容易点1 JAVA是面对对象的语言(5 行受影响)*/
