如果把数据库A里的数据读取并转换格式输出到另一个数据库B,因为数据量非常大(几万条)请问有没有好的算法。我写了一个,特别特别的慢。初学,代码写的很差,希望大家帮我优化一下。谢谢了
数据格式如下:
A里数据姓名 王
姓名 张
姓名 李
学号 1
学号 3
得分 58
得分 78
....
需要转换为
B的数据格式姓名 王 张 李
学号 1 3
得分 58 78
....
就是说把第一字段相同的横向排。使用ADO和mshflexglid打开A,用DATA和DBGRID打开B
我写的代码如下:'工程->引用Microsoft ActiveX Data Objects 2.x Library
Option Explicit
Public mCnnString As String
Dim mRst As New ADODB.RecordsetPrivate Sub Command1_Click()
Dim i As Integer
Dim j As Long
Dim y As Long
Dim sr As String
j = 1
y = 1
Data1.Recordset.AddNew
Data1.Recordset.Update
Data1.Recordset.AddNew
Data1.Recordset.Update
Data1.Refresh
For i = 1 To MSHFlexGrid1.Rows - 1
If MSHFlexGrid1.TextMatrix(i, 1) <> MSHFlexGrid1.TextMatrix(i - 1, 1) Then
Call m_write(y, j, MSHFlexGrid1.TextMatrix(i - 1, 2))
Data1.Recordset.AddNew
Data1.Recordset.Update
y = y + 1
j = 1
Call m_write(y, 0, MSHFlexGrid1.TextMatrix(i, 1))Call m_write(y, j, MSHFlexGrid1.TextMatrix(i, 2))
Else
Call m_write(y, j, MSHFlexGrid1.TextMatrix(i - 1, 2))j = j + 1End If
NextData1.Refresh
Data1.Recordset.Delete
Data1.Refresh
Data1.Recordset.Delete
Data1.Refresh
Data1.Recordset.DeleteEnd SubPrivate Sub Command2_Click()
On Error GoTo mErr
mRst.Open "Select * From test1", mCnnString, adOpenStatic, adLockBatchOptimistic, adCmdText
Set MSHFlexGrid1.DataSource = mRst
Exit Sub
mErr:
MsgBox Err.Number & "," & Err.Description, vbCritical + vbOKOnly
End
End Sub
Private Sub Form_Load()
mCnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db3.mdb" & ";Persist Security Info=False"
mRst.CursorLocation = adUseClient
End SubPrivate Sub Form_Unload(Cancel As Integer)
If mRst.State = adStateOpen Then
mRst.Close
Set mRst = Nothing
End If
End SubPublic Sub m_write(m_row As Long, m_col As Long, text As String) DBGrid1.Col = m_col
DBGrid1.Row = m_row
DBGrid1.text = textEnd Sub
数据格式如下:
A里数据姓名 王
姓名 张
姓名 李
学号 1
学号 3
得分 58
得分 78
....
需要转换为
B的数据格式姓名 王 张 李
学号 1 3
得分 58 78
....
就是说把第一字段相同的横向排。使用ADO和mshflexglid打开A,用DATA和DBGRID打开B
我写的代码如下:'工程->引用Microsoft ActiveX Data Objects 2.x Library
Option Explicit
Public mCnnString As String
Dim mRst As New ADODB.RecordsetPrivate Sub Command1_Click()
Dim i As Integer
Dim j As Long
Dim y As Long
Dim sr As String
j = 1
y = 1
Data1.Recordset.AddNew
Data1.Recordset.Update
Data1.Recordset.AddNew
Data1.Recordset.Update
Data1.Refresh
For i = 1 To MSHFlexGrid1.Rows - 1
If MSHFlexGrid1.TextMatrix(i, 1) <> MSHFlexGrid1.TextMatrix(i - 1, 1) Then
Call m_write(y, j, MSHFlexGrid1.TextMatrix(i - 1, 2))
Data1.Recordset.AddNew
Data1.Recordset.Update
y = y + 1
j = 1
Call m_write(y, 0, MSHFlexGrid1.TextMatrix(i, 1))Call m_write(y, j, MSHFlexGrid1.TextMatrix(i, 2))
Else
Call m_write(y, j, MSHFlexGrid1.TextMatrix(i - 1, 2))j = j + 1End If
NextData1.Refresh
Data1.Recordset.Delete
Data1.Refresh
Data1.Recordset.Delete
Data1.Refresh
Data1.Recordset.DeleteEnd SubPrivate Sub Command2_Click()
On Error GoTo mErr
mRst.Open "Select * From test1", mCnnString, adOpenStatic, adLockBatchOptimistic, adCmdText
Set MSHFlexGrid1.DataSource = mRst
Exit Sub
mErr:
MsgBox Err.Number & "," & Err.Description, vbCritical + vbOKOnly
End
End Sub
Private Sub Form_Load()
mCnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db3.mdb" & ";Persist Security Info=False"
mRst.CursorLocation = adUseClient
End SubPrivate Sub Form_Unload(Cancel As Integer)
If mRst.State = adStateOpen Then
mRst.Close
Set mRst = Nothing
End If
End SubPublic Sub m_write(m_row As Long, m_col As Long, text As String) DBGrid1.Col = m_col
DBGrid1.Row = m_row
DBGrid1.text = textEnd Sub
解决方案 »
- 如何在StatusBar上显示当前操作员以及识别权限?
- VB问题,关于SetFocus
- 用 Setup Factory 7.0打包后的程序 安装后 目录里 窗体、工程都显示出来了!!请问怎么解决?
- 高分100,求助SendMessage问题
- 同一个Form里单击后,单击一个数据库联接按钮,如果为true则显示本页面另外三个控件。这样写为何不对。。。
- 关于如何用api操作xp防火墙
- 新装的VB6找不到帮助文件.
- 字符串里,我怎么让其中的几个字符变成另外一种颜色?
- 关于filelistbox的问题
- vb编译后的exe文件打包到别的电脑上时运行出错
- 求教!用Quartz.dll开发的播放器,声道切换的问题。
- 请高手帮忙,高分回报!!!!!
但是代码怎么写呢?我仅仅只会对grib的操作.很重要,麻烦大家了
把结构给出来,想办法用SQL语句来解决.