请问一个关于“Distinct”过滤的查询问题
在datur表中有以下列 :id,name,userid
共计六条记录其中只有一对记录的“name”重复
现在要使用查询语句过滤掉 “name”相同的记录行代码如下:Dim rsCheck As New ADODB.Recordset
Dim DBstr As String
Dim name As String DBstr = "select Distinct name from datur"
rsCheck.Open DBstr, DBCnn, adOpenStatic, adLockOptimistic If rsCheck.RecordCount > 0 Then
name = Trim(rsCheck.Fields("name").Value)
End If调试发现 rsCheck.RecordCount =-1 select Distinct name from datur 这句在企业管理器里验证过请各位前辈指点一下我的代码应该如何写
在datur表中有以下列 :id,name,userid
共计六条记录其中只有一对记录的“name”重复
现在要使用查询语句过滤掉 “name”相同的记录行代码如下:Dim rsCheck As New ADODB.Recordset
Dim DBstr As String
Dim name As String DBstr = "select Distinct name from datur"
rsCheck.Open DBstr, DBCnn, adOpenStatic, adLockOptimistic If rsCheck.RecordCount > 0 Then
name = Trim(rsCheck.Fields("name").Value)
End If调试发现 rsCheck.RecordCount =-1 select Distinct name from datur 这句在企业管理器里验证过请各位前辈指点一下我的代码应该如何写
name = Trim(rsCheck.Fields("name").Value)
End If
另外,二楼写的这一段
If Not rs.EOF Then Then
name = Trim(rsCheck.Fields("name").Value)
End If
和楼主写的
If rsCheck.RecordCount > 0 Then
name = Trim(rsCheck.Fields("name").Value)
End If
两则的效果是一样的
Dim DBstr As String
Dim name As String DBstr = "select Distinct name from datur"
rsCheck.Open DBstr, DBCnn, adOpenStatic, adLockOptimistic If rsCheck.RecordCount > 0 Then
For a = 0 To rsCheck.RecordCount - 1
name = Trim(rsCheck.Fields("name").Value)
Debug.print name
rsCheck.MoveNext
Next a
End If
以上的代码可以了吧,关键的问题是 rsCheck.RecordCount =-1
rsCheck.Open DBstr, DBCnn, adOpenStatic, adLockOptimistic If rsCheck.RecordCount > 0 Then
name = Trim(rsCheck.Fields("name").Value)
End If
Dim DBstr As String
Dim name As String DBstr = "select Distinct name from datur"
rsCheck.Open DBstr, DBCnn, adOpenStatic, adLockOptimistic'这里游标类型和锁定类型不对 不能使用RecordCount 属性 另外 如果表中没有记录 RecordCount 也会出错 If rsCheck.RecordCount > 0 Then
For a = 0 To rsCheck.RecordCount - 1
name = Trim(rsCheck.Fields("name").Value)
Debug.print name
rsCheck.MoveNext
Next a
End If
Dim rsCheck As New ADODB.Recordset
Dim DBstr As String
Dim name As String DBstr = "select Distinct name from datur"
rsCheck.Open DBstr, DBCnn, 1, 1
If not rsCheck.eof Then
do until rsCheck.eof
name = Trim(rsCheck.Fields("name").Value)
Debug.print name
rsCheck.MoveNext
loop
End If
1. adOpenStatic, adLockOptimistic' 如果不是需要addnew操作的时候 要采用性能好的参数
2.记录机 是否为空的判断 rsCheck.RecordCount > 0 正确的应该是 判断.eof
3.For a = 0 To rsCheck.RecordCount - 1
for 后面的 to的参数最好是一个变量 否则在每次for循环的时候 他都会读取 你设置的属性或者运算 大大影响效率
4 Next a 这个是教科书上的写法 画蛇添足 而且降低效率
rsCheck.CursorLocation = adUseClient
rsCheck.Open DBstr, DBCnn, adOpenStatic, adLockOptimistic
......
Dim DBstr As String
Dim name As String DBstr = "select Distinct name from datur"
rsCheck.Open DBstr, DBCnn, adOpenStatic, adLockOptimistic while not rsCheck.eof
name = Trim(rsCheck.Fields("name").Value)
Debug.print name
rsCheck.MoveNext
wend