Private Function Custom_Money(nSN As String, nResult As String) As String
'nSN为顾客编号
'nType为消费类型
'nResult为返回类型,如总消费额,总付款,欠款等Dim curSeriesPrice As Currency '套系价格
Dim curSecondBuy As Currency '二次消费
Dim curAllBuy As Currency '总消费金额Dim curPayTotal As Currency '总付款记录
Dim curArrearage As Currency '欠款金额
Call objConnDim rsSeries As New ADODB.Recordset
rsSeries.CursorLocation = adUseClient
Dim sqlSeries As String
sqlSeries = "SELECT Receivable FROM Custom WHERE CustomNO='" & nSN & "'"
rsSeries.Open sqlSeries, conn, 1, 1
If rsSeries.EOF = False Then
curSeriesPrice = rsSeries.Fields("Receivable") '提取套系价格
Else
Custom_Money = 0
Exit Function
End If
'
Set rsSeries = NothingDim rsBuy As New ADODB.Recordset
rsBuy.CursorLocation = adUseClient
Dim sqlBuy As String
sqlBuy = "SELECT SUM(Price)Price FROM CustomDetails WHERE Class='Retail' AND CustomSN='" & nSN & "'"
rsBuy.Open sqlBuy, conn, 1, 1If rsBuy.EOF = False Then
If Not IsNull(rsBuy.Fields(0)) Then curSecondBuy = rsBuy.Fields(0) '二次消费记录
Else
curSecondBuy = 0
End IfcurAllBuy = curSeriesPrice + curSecondBuy '总消费记录Set rsBuy = Nothing'付款记录
Dim rsPay As New ADODB.Recordset
rsPay.CursorLocation = adUseClient
Dim sqlPay As String
sqlPay = "SELECT SUM(Money)Money FROM Money WHERE CustomNO='" & nSN & "'"
rsPay.Open sqlPay, conn, 1, 1If rsPay.EOF = False Then
If Not IsNull(rsPay.Fields(0)) Then curPayTotal = rsPay.Fields(0) '付款金额
Else
curPayTotal = 0
End IfcurArrearage = curAllBuy - curPayTotal '欠款金额Set rsPay = NothingSelect Case nResult
Case "套系金额"
Custom_Money = curSeriesPrice
Case "总消费额"
Custom_Money = curAllBuy
Case "二次消费"
Custom_Money = curSecondBuy
Case "总付款额"
Custom_Money = curPayTotal
Case "欠款"
Custom_Money = curArrearage
End SelectEnd FunctionPrivate Sub RefillList(ByVal lv As ListView, ByVal strType As String)
Dim clm As ColumnHeader
Dim itm As ListItem
Dim i As Long, j As Long
Dim nSecondBuy As String
Dim nArr As String
lv.ListItems.Clear
lv.View = lvwReport
lv.LabelEdit = lvwManual Call objConn
Dim rsCustom As New ADODB.Recordset
Dim strListSql As String
rsCustom.CursorLocation = adUseClient
If strType = "Search" Then
strListSql = "SELECT * FROM Custom WHERE SubCorp='" & CurrentSubCorp & "' AND (CustomNO Like '%" & SafeSQL(Trim(txtSearch(0).Text)) & "%') AND (M_Name Like '%" & SafeSQL(Trim(txtSearch(1).Text)) & "%') AND (F_Name Like '%" & SafeSQL(Trim(txtSearch(2).Text)) & "%') AND (CardNO Like '%" & SafeSQL(Trim(txtSearch(3))) & "%') ORDER BY ID DESC"
Else
strListSql = "SELECT * FROM Custom WHERE SubCorp='" & CurrentSubCorp & "' ORDER BY ID DESC"
End If
rsCustom.Open strListSql, conn, 1, 1
If rsCustom.EOF = True And rsCustom.BOF = True Then Exit Sub
rsCustom.MoveLast
nItems = rsCustom.RecordCount
If nItems > 30 Then PanelReadDatabase.Visible = True: MDIMain.MousePointer = 11: DoEvents
rsCustom.MoveFirst
For i = 1 To nItems
nSecondBuy = Custom_Money(rsCustom.Fields("CustomNO"), "二次消费")
nArr = Custom_Money(rsCustom.Fields("CustomNO"), "欠款")
nCustomNO$ = "" & rsCustom.Fields("CustomNO")
nSeriesType$ = "" & rsCustom.Fields("SeriesType")
nCustomName = "" & rsCustom.Fields("M_Name") & " " & rsCustom.Fields("F_Name")
nCustomMobile = "" & rsCustom.Fields("M_Mobile") & " " & rsCustom.Fields("F_Mobile")
nCustomWork = "" & rsCustom.Fields("M_Work")
nSeries = "" & rsCustom.Fields("Series")
nSeriesPrice = "" & rsCustom.Fields("Receivable")
nCardNO = "" & rsCustom.Fields("CardNO")
nReturnMoney = "" & rsCustom.Fields("InReturnMoney")
Set itm = lv.ListItems.Add(, "Row" & i, rsCustom.Fields(0))
itm.SubItems(1) = Trim(nCustomNO)
itm.SubItems(2) = Trim(nSeriesType)
itm.SubItems(3) = Trim(nCustomName)
itm.SubItems(4) = Trim(nCustomMobile)
itm.SubItems(5) = Trim(nCustomWork)
itm.SubItems(6) = Trim(nSeries)
itm.SubItems(7) = Trim(nSeriesPrice)
itm.SubItems(8) = nSecondBuy
itm.SubItems(9) = nArr
itm.SubItems(10) = Trim(nCardNO)
itm.SubItems(11) = nReturnMoney
rsCustom.MoveNext
Next i
PanelReadDatabase.Visible = False
MDIMain.MousePointer = 0
rsCustom.Close
Set rsCustom = Nothing
TabSeries.Tabs(1).Caption = "共搜索相关顾客资料 " & lstCustom.ListItems.count & " 个"
'Set conn = Nothing
End SubPrivate Sub Form_Load()'列取顾客资料Call RefillList(lstCustom, "")End Sub这样读取出数据特别慢。请问大师怎么样优化?
写出结构 提供表结构 给你写个
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Custom]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Custom]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Money]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Money]
GOCREATE TABLE [dbo].[Custom] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[CustomNO] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[SubCorp] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[SeriesType] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[SeriesGroup] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Series] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Amount] [int] NULL ,
[SeriesPrice] [money] NULL ,
[TotalPrice] [money] NULL ,
[M_Name] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[M_Birthday] [datetime] NULL ,
[M_BirthdayCN] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[M_Phone] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[M_Mobile] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[M_Address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[M_Zip] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[M_Work] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[F_Name] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[F_Birthday] [datetime] NULL ,
[F_BirthdayCN] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[F_Phone] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[F_Mobile] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[F_Address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[F_Zip] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[F_Work] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[OrderDate] [datetime] NULL ,
[PhotoDate] [datetime] NULL ,
[IsPhotoExt] [bit] NULL ,
[PhotoExtPlace] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[PhotoExtDate] [datetime] NULL ,
[SelectDate] [datetime] NULL ,
[DigitalSelectDate] [datetime] NULL ,
[TakeOrderDate] [datetime] NULL ,
[TakeDate] [datetime] NULL ,
[MarryDate] [datetime] NULL ,
[MarryDateCN] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Exigence] [bit] NULL ,
[VIP] [bit] NULL ,
[Abate] [money] NULL ,
[Receivable] [money] NULL ,
[OrderPay] [money] NULL ,
[SelectAdd] [money] NULL ,
[Arrearage] [money] NULL ,
[Source] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[CardNo] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Zone] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[InNo] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[InMale] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[InFemale] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[InReturnMoney] [int] NULL ,
[ReturnMoney] [int] NULL ,
[CustomType] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Sales] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[Dress] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[DressExt] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[Prink] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PrinkExt] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PrinkMale] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PrinkMaleExt] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PrinkAssistant] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PrinkAssistantExt] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[Photo] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PhotoExt] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PhotoProcExt] [int] NULL ,
[PhotoAssistant] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PhotoAssistantExt] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[Selected] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[DigitalSelected] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[SelectProcExt] [int] NULL ,
[Design] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[DigitalDesign] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Take] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[PhotoEnd] [int] NULL ,
[PhotoExtEnd] [int] NULL ,
[SelectEnd] [int] NULL ,
[DesignEnd] [int] NULL ,
[DigitaSelectEnd] [int] NULL ,
[TakeEnd] [int] NULL ,
[Operator] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Re] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[P_Photo_Supply] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[P_Design_First_Accept] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[P_Design_First_Accept_Date] [datetime] NULL ,
[P_Design_First_Date] [datetime] NULL ,
[P_Photo_Amount] [int] NULL ,
[P_Design_First_EndDate] [datetime] NULL ,
[P_Select_Details] [nvarchar] (3000) COLLATE Chinese_PRC_CI_AS NULL ,
[P_Select_Amount] [int] NULL ,
[P_Select_Re] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[P_DigitalSelect_Re] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[ProcessEnd] [bit] NULL ,
[Custom_Type_Flag] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Money] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Money] [money] NOT NULL ,
[Shroff] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Sales] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Assistant] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomNO] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Date] [datetime] NOT NULL ,
[Type] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PayType] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CardNo] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Re] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[RobeID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Operator] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO这是这两上表的脚本。
如果你觉得用户体验的问题呢,就加progressbar
like是不使用索引的,要傻傻的一个一个的检查数据是否包含特征串
nAllBuy = Custom_Money(rsCustom.Fields("CustomNO"), "总消费")
nArr = Custom_Money(rsCustom.Fields("CustomNO"), "欠款")
如果没有这两个的话,5000条的速度就是不到4秒,要是加上这个的话那么就是不到10分钟。
from custom a inner join customdetails b on a.custoNO=b.customSN
inner join money c on a.customNO=c.customNO
where 条件
group by a.customNO,a.Receivable
查询获得记录集后,循环处理各客户的应收款,已收款,欠款.
SELECT SUM(c.Money) AS Expr1
FROM Custom a INNER JOIN
Money c ON a.CustomNO = c.CustomNO
WHERE (a.CustomNO LIKE '%07125%')
GROUP BY a.CustomNO
的话,出来的结果是对的。但是加上sum(b.Price), 就不对了。
晴天老大。我现在测试用
SELECT Custom.CustomNO, Custom.Receivable AS Expr1, CustomDetails.Price AS Expr3,
SUM(Money.Money) AS Expr2
FROM Custom INNER JOIN
CustomDetails ON Custom.CustomNO = CustomDetails.CustomSN INNER JOIN
Money ON Custom.CustomNO = Money.CustomNO
WHERE (Custom.CustomNO = '0008950') AND (CustomDetails.Class = 'Retail')
GROUP BY Custom.CustomNO, Custom.Receivable, CustomDetails.Price
结果是:CustomNO Expr1 Expr3 Expr2
0008905 2980 180 3735
0008905 2980 200 3735
0008905 2980 375 3735
SUM(Money.Money)结果对。应该CustomDetails.Price 也用SUM计算一下,但是这样的话出来的结果就不对了。Expr3的结果就错了。如果算出Expr3 的结果相加就对了。然后是一行。