全文检索的 SQL 语句怎么写？

[Tenner]

现有一表，其中有25个字段，要求只要某记录中的任何一个字段包含"abc"就将其做为查询结果返回，这样的SQL怎么写？
是不是只能写一条很长的，用or连接的？有没有更好的写法？

[TechnoFantasy]

http://community.csdn.net/Expert/topic/2685/2685039.xml?temp=.9018061

[Tenner]

我用了下面的语句：
SELECT * FROM [mytable] WHERE CONTAINS(*, '"aaa"')

但是报错：操作符丢失
哪里写错了？

[TechnoFantasy]

全文检索好像不支持对任何字段的检索，我手边没有MSDN，你可以搜索一下MSDN看看CONTAINS的帮助。

[zcm123]

学习中 。。。

[zcm123]

我觉得用 like '%xxx%' 写 在 or 起来 也未尝不可阿

[ryuginka]

up

[tonywzw]

本人也在学习中。观望中

[Tenner]

like + or 是可以，不过如果是有好几百个字段的表呢？要写好几百次.....

[sssss342072]

我也遇到过这样的情况，我也是用OR连接很长SQL语句，我是一条SQL里包含30个OR+23个AND，针对2000记录查询需要3-5秒左右，主要是速度还能接受，对于它的美观倒没研究过，顶一个有人有好的方法吗？学习学习啊

[ybcaa]

帮你顶。

[Tenner]

我就是看了MSDN，它上面就是这么写的代码，我用的时候却不行，不知道为什么
下面是MSDN上的代码：

'Finds items based on CONTAINS, FREETEXT, FORMS, or LIKE predicate
'This example uses FREETEXT to find reports with names of cities
'Passes search result recordset to DoResults (see Enumerating Results)

'get computer and domain information
Set info = CreateObject("ADSystemInfo")
Set infoNT = CreateObject("WinNTSystemInfo")
cName = infoNT.ComputerName
dName = info.DomainDNSName

'create connection object
Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "Exoledb.DataSource"

'URL for connection object
'is at the virtual directory root
cURL = "http://" & _
cName & "." & _
dName & "/" & _
"public"

Conn.Open cURL

'relative URL is the folder to search
relURL = "Reports/FieldOffices"

'search specification variable
searchspec = "Tokyo London Bogata Honolulu Cairo Sydney Chicago"

'predicate to use in search
predicate = "FREETEXT"

FindSearchSpec searchspec, predicate, relURL, Conn

Sub FindSearchSpec(sender, relURL, Conn)

Const adErrNoCurrentRecord = 3021

Set rs = CreateObject("ADODB.Recordset")

'construct the SQL query
strQ = "SELECT ""urn:httpmail:subject"" "
strQ = strQ & "FROM """ & relURL & """ "


'Construct a query based on the search predicate.
Select Case predicate
Case "CONTAINS"
strQ = strQ & "WHERE CONTAINS(*, '" & Chr(34) & searchspec & Chr(34) & "') "
Case "FREETEXT"
strQ = strQ & "WHERE FREETEXT(*, '" & Chr(34) & searchspec & Chr(34) & "') "
Case "LIKE"
strQ = strQ & "WHERE ""urn:schemas:httpmail:textdescription"" LIKE '" & searchspec & "'"
Case "FORMSOF"
strQ = strQ & "WHERE CONTAINS('FORMSOF(INFLECTIONAL," & Chr(34) & searchspec & Chr(34) & ")')"
Case Else
strQ = strQ & "WHERE CONTAINS(*, '" & Chr(34) & searchspec & Chr(34) & "') "
End Select


Rs.Open strQ, Conn

'If empty recordset, return error
'If successful call DoResults routine passing the recorset
If Rs.EOF = True Then
On Error Resume Next
Err.Raise adErrNoCurrentRecord
Response.Write "No items found, run another query."
Else
Response.Write "Success! Found " & Rs.RecordCount
DoResults Rs
End If
Rs.Close

End Sub

[guxizhw]

用程序打出来
我写sql有好多是用一个辅助函数

'Public Sub PrintField(ByVal strTable As String, Optional ByVal intNewLineNum As Integer = -1)
'
' Dim rs As New ADODB.Recordset
'
' Dim strField As String
'
' Dim i As Integer
'
' rs.Open "EXEC sp_columns " & AddstringBracket(strTable), cn, adOpenStatic, adLockReadOnly
'
' strField = """"
'
' i = 0
'
' While Not rs.EOF
'
' If i = intNewLineNum Then
'
' strField = strField & """" & " _" & vbNewLine & "&" & """"
'
' i = 0
'
' End If
'
' strField = strField & rs("COLUMN_NAME") & ","
'
' i = i + 1
'
' rs.MoveNext
'
' Wend
'
' strField = Mid(strField, 1, Len(strField) - 1)
'
' Debug.Print strField
'
'End Sub


一点愚见，请大家指教
把表名传进去，然后运行一下，从立即窗口里面copy出来，贴到主程序里面
编译的时候把它注释掉，编译的时候不需要的

[hfgang]

contains(*,'keyword')这种查询方式sqlserver是支持的，但是只支持对建了索引的列进行，所以你要对所有的需要查询的列都建上索引。另外测试的时候不要用aaa这个词，换个有意义的词。

[Tenner]

那Access支不支持这种查询呢？在建立了索引以后？

[apple800]

精华区的帖子：

CREATE procedure searchname @sname varchar(10)

As

begin

create table #TableList(

tablename char(200),

colname char(200)

)



declare @table varchar(200)

declare @col varchar(200)



set nocount on

declare curTab scroll cursor for select name from sysobjects where xtype='u' or xtype='S'

open curTab

fetch next from curTab into @table

while @@FETCH_STATUS=0

begin

declare curCol scroll cursor for select name from syscolumns where (xtype=175 or xtype=167 ) and (id in (select id from sysobjects where name=@table))

open curCol

fetch next from curCol into @col

while @@FETCH_STATUS=0

begin

execute('insert into #TableList select '''+@table+''' , '''+@col+''' from '+@table+' where '+@col+'='''+@sname+'''')

fetch next from curCol into @col

end

close curCol

deallocate curCol

fetch next from curTab into @table

end

close curTab

deallocate curTab

set nocount off

select distinct * from #TableList

drop table #tablelist

end
GO