Private Type stDenseInfo
stid As Integer
stName As String
End Type
Dim stMonthInfo() As stDenseInfoPrivate Sub Command1_Click()
Dim dbconn As New ADODB.Connection
Dim strs As New ADODB.Recordset
Dim intI As Integer, intJ As Integer, intK As Integer, intL As Integer
Dim stday(0 To 30) As Date
Dim stMonthPollValue(0 To 8, 0 To 30, 0 To 23, 0 To 21)
Dim stMothPollName(0 To 21) As String
dbconn.ConnectionString = "PROVIDER=MSDASQL;dsn=En;uid=a;pwd=a;"
dbconn.Open
stMothPollName(0) = "w1"
stMothPollName(1) = "w2"
stMothPollName(2) = "w3"
stMothPollName(3) = "w4"
stMothPollName(4) = "w5"
stMothPollName(5) = "w6"
stMothPollName(6) = "w7"
stMothPollName(7) = "w8"
stMothPollName(8) = "w9"
stMothPollName(9) = "w10"
stMothPollName(10) = "w11"
stMothPollName(11) = "w12"
stMothPollName(12) = "w13"
stMothPollName(13) = "W14"
stMothPollName(14) = "w15"
stMothPollName(15) = "w16"
stMothPollName(16) = "w17"
stMothPollName(17) = "w18"
stMothPollName(18) = "w19"
stMothPollName(19) = "w20"
stMothPollName(20) = "w21"
stMothPollName(21) = "w22"
'站点信息
With strs
.CursorLocation = adUseClient
.Open "select StationId,stationName from StationInfo where isuse=1", dbconn, adOpenKeyset, adLockOptimistic
If Not .EOF Then
ReDim stMonthInfo(0 To .RecordCount - 1)
intstcount = .RecordCount
.MoveFirst
For i = 0 To .RecordCount - 1
stMonthInfo(i).stid = !StationId
stMonthInfo(i).stName = !StationName
.MoveNext
Next
End If
.Close
End With
With strs
.CursorLocation = adUseClient
'Debug.Print GetTickCount
.Open "select * from dayreport where recdMonth-" & 9 & "=0 and recdYear-" & 2004 & "=0 order by stationid,channelid,recdyear,recdmonth,recdday", dbconn, adOpenKeyset, adLockOptimistic
'Debug.Print GetTickCount
'End
Debug.Print .RecordCount
If Not .EOF Then
For intL = 0 To 8 '物名
Debug.Print GetTickCount
For intI = 0 To 30 '天
stday(intJ) = DateSerial(2004, 9, intI + 1)
For intJ = 0 To 23 '小时
For intK = 0 To 21 '物类
'Dim l As Long
'l = GetTickCount
'Debug.Print GetTickCount
.Filter = "Stationid=" & stMonthInfo(intL).stid & " and recdDay=" & intI + 1 & " and recdHour=" & intJ & " and Pollobjectid=" & intK
'Debug.Print GetTickCount
If Not .EOF Then stMonthPollValue(intL, intI, intJ, intK) = !ModifyUnitValue Else stMonthPollValue(intL, intI, intJ, intK) = -1
'Debug.Print stMonthPollValue(intL, intI, intJ, intK)
Next intK
Next intJ
Next intI
Debug.Print GetTickCount
End
Next intL
Else
MsgBox "本月数据无!", vbCritical Or vbInformation, "系统"
'GoTo Err
End If
End With
End Sub________________________________
大概9x31X16X24条记录9万多条记录
其中dayreport中stationid,channelid,recdyear,recdmonth,recdday是主键。
我这样查下来大概要8分钟?请问有什么好方法提高效率?解决送分!!!谢谢!
stid As Integer
stName As String
End Type
Dim stMonthInfo() As stDenseInfoPrivate Sub Command1_Click()
Dim dbconn As New ADODB.Connection
Dim strs As New ADODB.Recordset
Dim intI As Integer, intJ As Integer, intK As Integer, intL As Integer
Dim stday(0 To 30) As Date
Dim stMonthPollValue(0 To 8, 0 To 30, 0 To 23, 0 To 21)
Dim stMothPollName(0 To 21) As String
dbconn.ConnectionString = "PROVIDER=MSDASQL;dsn=En;uid=a;pwd=a;"
dbconn.Open
stMothPollName(0) = "w1"
stMothPollName(1) = "w2"
stMothPollName(2) = "w3"
stMothPollName(3) = "w4"
stMothPollName(4) = "w5"
stMothPollName(5) = "w6"
stMothPollName(6) = "w7"
stMothPollName(7) = "w8"
stMothPollName(8) = "w9"
stMothPollName(9) = "w10"
stMothPollName(10) = "w11"
stMothPollName(11) = "w12"
stMothPollName(12) = "w13"
stMothPollName(13) = "W14"
stMothPollName(14) = "w15"
stMothPollName(15) = "w16"
stMothPollName(16) = "w17"
stMothPollName(17) = "w18"
stMothPollName(18) = "w19"
stMothPollName(19) = "w20"
stMothPollName(20) = "w21"
stMothPollName(21) = "w22"
'站点信息
With strs
.CursorLocation = adUseClient
.Open "select StationId,stationName from StationInfo where isuse=1", dbconn, adOpenKeyset, adLockOptimistic
If Not .EOF Then
ReDim stMonthInfo(0 To .RecordCount - 1)
intstcount = .RecordCount
.MoveFirst
For i = 0 To .RecordCount - 1
stMonthInfo(i).stid = !StationId
stMonthInfo(i).stName = !StationName
.MoveNext
Next
End If
.Close
End With
With strs
.CursorLocation = adUseClient
'Debug.Print GetTickCount
.Open "select * from dayreport where recdMonth-" & 9 & "=0 and recdYear-" & 2004 & "=0 order by stationid,channelid,recdyear,recdmonth,recdday", dbconn, adOpenKeyset, adLockOptimistic
'Debug.Print GetTickCount
'End
Debug.Print .RecordCount
If Not .EOF Then
For intL = 0 To 8 '物名
Debug.Print GetTickCount
For intI = 0 To 30 '天
stday(intJ) = DateSerial(2004, 9, intI + 1)
For intJ = 0 To 23 '小时
For intK = 0 To 21 '物类
'Dim l As Long
'l = GetTickCount
'Debug.Print GetTickCount
.Filter = "Stationid=" & stMonthInfo(intL).stid & " and recdDay=" & intI + 1 & " and recdHour=" & intJ & " and Pollobjectid=" & intK
'Debug.Print GetTickCount
If Not .EOF Then stMonthPollValue(intL, intI, intJ, intK) = !ModifyUnitValue Else stMonthPollValue(intL, intI, intJ, intK) = -1
'Debug.Print stMonthPollValue(intL, intI, intJ, intK)
Next intK
Next intJ
Next intI
Debug.Print GetTickCount
End
Next intL
Else
MsgBox "本月数据无!", vbCritical Or vbInformation, "系统"
'GoTo Err
End If
End With
End Sub________________________________
大概9x31X16X24条记录9万多条记录
其中dayreport中stationid,channelid,recdyear,recdmonth,recdday是主键。
我这样查下来大概要8分钟?请问有什么好方法提高效率?解决送分!!!谢谢!
问题主要是在用filter写入数组时,很费时。因为是一个月的汇总。
to回复人: lanWay(秋冰) ( )
我主要是让用户自己选择导入月报表,在这用存储过程不很方便。但临时表如何用,请给个示例。
to zjcxc(邹建) ( ) 、zhujiechang(小朱) ( )
dayreport是个视图是每天每站每物每小时的值,即每天recdyear、recdMonth、recdDay,每站StationInfo表里的StationId,每个物objectID。我写入数组是为了存入foxpro中导为.dbf格式。
因为每站的物有22个,在.dbf表里,要存为每天(dateserial(recdyear,recdmonth,recdday))每站每小时格式,用的是description的22个名字作为表横向字段。每物的表object里(有objectId,description,其中objectid为主键,与dayreport的objectid一致)。所以存入数组。
to zjcxc(邹建) ( )
大侠是数据库专家,想请问:在.dbf建表中我的每天用的datetime,strSQL = "CREATE TABLE [" & Right(strfile, Len(strfile) - InStrRev(strfile, "\")) & "] " & _
" (日期 datetime,站名 char(10),站号 numeric(1,0),Time numeric(1,0),Nox numeric(3,3) ," & _
strSQL = "insert into " & Right(strfile, Len(strfile) - InStrRev(strfile, "\")) & _
" values('" & stDay(intJ) & "','" & stMonthInfo(intI).stName & "'," & stMonthInfo(intI).stid & "," & _
,但存入时间数组stday(intJ)时却老是报数据类型不匹配。我觉得建.dbf也应该遵循sql原则。请指教。
如:改用存儲過程,並用臨時表來返回記錄集結果。
可能效率会提高,谁能给个例子,就可结分啦!
lanWay(秋冰) zjcxc(邹建) ,临时表没用过谁能简单示例,谢谢!
create table tb(姓名 varchar(10),部门 varchar(10),值 int)
insert tb select '张三','A部门',10
union all select '张三','B部门',10
union all select '李四','B部门',10/*--要求得到结果姓名 A部门 B部门
---------- ----------- -----------
李四 0 10
张三 10 10
--*/
go--处理的存储过程
create proc p_qry
as
set nocount on
declare @s varchar(8000)
set @s=''
select @s=@s+',['+部门+']=sum(case 部门 when '''+部门+''' then 值 else 0 end)'
from tb group by 部门
exec('select 姓名 '+@s+' from tb group by 姓名')
go--调用
exec p_qry
go--删除测试
drop table tb
drop proc p_qry