如何使用SQL和VBS从访问表中获取多个小计? [英] How to obtain Multiple subtotals using SQL and VBS from Access Table?
问题描述
我正在尝试使用Windows中的外部vbs文件vbscript从访问表中获取多个小计.
I am trying to obtain multiple subtotals from an Access Table , using a external vbs file vbscript in Windows.
mySQL = "SELECT [Data.Time],"& Quantity &"*Sum([Data.Price]) AS
SumOfPrice FROM Data WHERE ( ( [Data.Ticker] Like '"& CE1 &"'
Or [Data.Ticker] Like '"& CE2 &"' Or [Data.Ticker] Like '"& CE3 &"'
Or [Data.Ticker] Like '"& CE4 &"' Or [Data.Ticker] Like '"& CE5 &"'
Or [Data.Ticker] Like '"& PE1 &"' Or [Data.Ticker] Like '"& PE2 &"'
Or [Data.Ticker] Like '"& PE3 &"' Or [Data.Ticker] Like '"& PE4 &"'
Or [Data.Ticker] Like '"& PE5 &"'
) AND ([Data.DateTr]=#"& DateIn &"#))
GROUP BY [Data.Time] HAVING [Data.Time] > #"& startTime(i) &"# and (((Count([Data.Ticker]))= 10))"
进一步处理......
Further Processing....
objRecordSet2.Open mySQL,objConnection, adOpenStatic, adLockOptimistic
objRecordSet2.MoveFirst
Do Until objRecordSet2.EOF
If objRecordSet2.Fields.Item(1) > testvalue
目前,每次(每1分钟)提供一次CE + PE总计 必须确保每个时刻恰好有5条CE记录和5条PE记录,因此总共10条.
Presently it provides the total of CE+PE for each time (every 1 minute) Must ensure exactly 5 CE records and 5 PE records for every time instant , hence total 10.
我们可以对其进行修改以分别提供CE和PE的总计. SELECT Time,SumofCEPrice,SumofPEPrice每1分钟
Can we Modify it to provide totals of CE and PE seperately. SELECT Time , SumofCEPrice , SumofPEPrice for each 1 minute
推荐答案
只需将 Ticker 添加为GROUP BY
中的另一个分组变量.另外请考虑以下项目:
Simply add, Ticker as another grouping variable in GROUP BY
. Additionally consider below items:
-
任何没有通配符运算符
%
或*
的LIKE
表达式都是多余的,应使用等式=
.实际上,您甚至可以在这里使用IN()
子句;
Any
LIKE
expression without a wildcard operator,%
or*
, is redundant and should use the equality,=
. In fact, here you can use even anIN()
clause;
应将未聚合的列排除在HAVING
之外,并放在WHERE
子句中;
Non-aggregated columns should be left out of HAVING
and placed in WHERE
clause;
使用DAO和ADO连接支持的参数化.
Use parameterization which is supported by DAO and ADO connections.
调整后的SQL字符串
"SELECT [Data.Time], [Data.Ticker], "& Quantity &" * Sum([Data.Price]) AS SumOfPrice " & _
" FROM Data " & _
" WHERE ( " & _
" ( [Data.Ticker] = '"& CE1 &"' " & _
" OR [Data.Ticker] = '"& CE2 &"' OR [Data.Ticker] = '"& CE3 &"' " & _
" OR [Data.Ticker] = '"& CE4 &"' OR [Data.Ticker] = '"& CE5 &"' " & _
" OR [Data.Ticker] = '"& PE1 &"' OR [Data.Ticker] = '"& PE2 &"' " & _
" OR [Data.Ticker] = '"& PE3 &"' OR [Data.Ticker] = '"& PE4 &"' " & _
" OR [Data.Ticker] = '"& PE5 &"' " & _
" ) " & _
" AND ([Data.DateTr] = #" & DateIn & "#)" & _
" AND ([Data.Time] > #" & startTime(i) & "#) " & _
" ) " & _
" GROUP BY [Data.Time], [Data.Ticker] " & _
" HAVING Count(([Data.Ticker]) = 10)"
或者,将参数化与
这篇关于如何使用SQL和VBS从访问表中获取多个小计?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!