求效率最高的sql-server查询实现方法

[yangyanli]

表Table 字段 id AA1,AA2 …,记录100000条。
一组关键词数量不定，n个，分别是 K1 K2 K3 Kn
如何用最快的方法把表Table中字段AA1中包含关键词最多的记录id查出来。
各位给个思路。

[xxrl]

msn上忙
工作忙
先mark一下
sorry

[ren791123]

用程序实现
<%
for i=0 to ubound(关键字数组)
temp=temp & " union all " & "select id from where aaa1 like '%"&k1&"%'"
temp1=temp1 & " union " & "select id from where aaa1 like '%"&k1&"%'"
next
temp=right(temp,len(temp)-len(" union all "))
set temp1_rs=conn.execute(right(temp1,len(temp1)-len(" union "))
if not temp1_rs.eof then
maxid=""
max=0
do while not temp1_rs.eof
set maxid_rs=conn.execute(select count(id) from ("&temp&")aaa where aaa.id='"&temp1_rs("id")&"')
'''''''''判断count(id) 值最多
temp1_rs.movenext
loop
end if
%>
中间可能有些错误，具体思路差不多。不过我想能能够用数据存储过程会快些

[子陌红尘]

--生成测试数据表(用表变量替代)
declare @t table(id int,AA1 varchar(1000),AA2 varchar(1000))
insert into @t select 1,'K1,K2,K3,Kn',''
insert into @t select 2,'K1,K5,K3,K8',''

--执行处理过程
declare @str varchar(8000)
declare @tab table(keywords varchar(100))
set @str='K1,K2,K3,Kn'

while charindex(',',@str)>0
begin
insert into @tab select left(@str,charindex(',',@str)-1)
set @str=stuff(@str,1,charindex(',',@str),'')
end
insert into @tab select @str

select
top 1 a.id
from
@T a,
@tab b
where
charindex(b.keywords,a.AA1)>1
group by
a.id
order by
count(b.keywords) desc

--输出结果
/*
id
----
1
*/

[子陌红尘]

--生成测试数据
create table t(id int,AA1 varchar(1000),AA2 varchar(1000))
insert into t select 1,'',''
insert into t select 2,'K1,K2,K3,Kn',''
insert into t select 3,'K1,K5,K3,K8',''
insert into t select 4,'K1,K5,K3',''
insert into t select 5,'K1,K2,K3,K4',''
insert into t select 7,'K1,K2,K9,Kn',''
go

--创建用户定义函数
create function f_getmaxid(@str varchar(8000))
returns int
as
begin
declare @tab table(keywords varchar(100))
declare @id int
set @str='K1,K2,K3,Kn'

while charindex(',',@str)>0
begin
insert into @tab select left(@str,charindex(',',@str)-1)
set @str=stuff(@str,1,charindex(',',@str),'')
end
insert into @tab select @str

select
top 1 @id=a.id
from
t a,
@tab b
where
charindex(b.keywords,a.AA1)>1
group by
a.id
order by
count(1) desc

return @id
end
go

--调用用户定义函数输出结果
select id=dbo.f_getmaxid('K1,K2,K3,Kn')
go
/*
id
--
2
*/



--删除测试数据
drop function f_getmaxid
drop table t

[dh20156]

还有点没看明白题意，呵呵^_^
子陌兄，你的function里set @str='K1,K2,K3,Kn'这一句没注释掉 ^_^

[dh20156]

faint,没看清楚竟瞎说，呵呵，偶错了 ^_^

[子陌红尘]

--生成测试数据
create table t(id int,AA1 varchar(1000),AA2 varchar(1000))
insert into t select 1,'',''
insert into t select 2,'K1,K2,K3,Kn',''
insert into t select 3,'K1,K5,K3,K8',''
insert into t select 4,'K1,K5,K3',''
insert into t select 5,'K1,K2,K3,K4',''
insert into t select 7,'K1,K2,K9,Kn',''
go

--创建用户定义函数
create function f_getmaxid(@str varchar(8000))
returns int
as
begin
declare @tab table(keywords varchar(100))
declare @id int

while charindex(',',@str)>0
begin
insert into @tab select left(@str,charindex(',',@str)-1)
set @str=stuff(@str,1,charindex(',',@str),'')
end
insert into @tab select @str

select
top 1 @id=a.id
from
t a,
@tab b
where
charindex(b.keywords,a.AA1)>1
group by
a.id
order by
count(1) desc

return @id
end
go

--调用用户定义函数输出结果
select id=dbo.f_getmaxid('K1,K2,K3,Kn')
go
/*
id
--
2
*/



--删除测试数据
drop function f_getmaxid
drop table t

[超级大笨狼]

大家可能都会用
select * from tb where field like '%关键字%'
但是一旦数据量大用户多，就会造成查询过慢，因此MS提供一种牺牲空间来换取时间的解决方案。
全文检索。原文请看
http://www.microsoft.com/china/msdn/archives/library/dnsql2k/html/sql_fulltextsearch.asp
我对自己的实现加了些注释：
use PopAsk
--使用这个库
exec sp_fulltext_database 'enable'
--使这个库允许全文检索

-----以下对Question_Total表操作------
/*
首先：要为全文检索目录起个名字，比如Q_ID_Desc
然后：要确保你这个表必须有个唯一主键，比如我这个Question_Total表，主键名是：PK_Question_Total
剩下就把要检索的列加进去就可以了。
*/
exec sp_fulltext_catalog 'Q_ID_Desc', 'create'
--创建名为Q_ID_Desc的全文检索目录，默认位置。
exec sp_fulltext_table 'Question_Total', 'create', 'Q_ID_Desc', 'PK_Question_Total'
-- 把Question_Total这个表，创建到全文检索目录中，指定主键。
exec sp_fulltext_column 'Question_Total', 'Title', 'add'
-- 把Question_Total这个表，要检索的Title列，添加到全文检索中。
exec sp_fulltext_column 'Question_Total', 'Content', 'add'
-- 把Question_Total这个表，要检索的Content列，添加到全文检索中。
exec sp_fulltext_table 'Question_Total', 'start_full'
-- 填充全文检索记录。
select FULLTEXTCATALOGPROPERTY('Q_ID_Desc', 'Populatestatus')
-- 这句可以不用，是检测是否添完状态的，1代表正在进行，0代表完成。
-----以下对另外一个表Answer_Total操作------
exec sp_fulltext_catalog 'A_ID_Desc', 'create'
--创建名为A_ID_Desc的全文检索目录，默认位置。
exec sp_fulltext_table 'Answer_Total', 'create', 'A_ID_Desc', 'PK_Answer_Total'
-- 把Question_Total这个表，创建到全文检索目录中，指定主键。

exec sp_fulltext_column 'Answer_Total', 'Answer_Content', 'add'
-- 把Answer_Total这个表，要检索的Answer_Content列，添加到全文检索中。
exec sp_fulltext_table 'Answer_Total', 'start_full'
-- 填充全文检索记录。
select FULLTEXTCATALOGPROPERTY('A_ID_Desc', 'Populatestatus')
-- 这句可以不用，是检测是否添完状态的，1代表正在进行，0代表完成。
/*
执行下列语句用于检索。
SELECT *
FROM FREETEXTTABLE(Question_Total, title, '测试')
SELECT *
FROM FREETEXTTABLE(Answer_Total, Answer_Content, '测试')
*/
躁声字文件
C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config\noise.chs
我参考别人的做法，只保留了一个？号。
还有数据库默认语言排序规则设置成Chinese_PRC_CI_AS

不要忘记建立一个作业，定时（一般可以每天）为全文检索添加记录。
exec sp_fulltext_table 'Answer_Total', 'start_full'

exec sp_fulltext_table 'Question_Total', 'start_full'
-- 填充全文检索记录。