VB.net中的聚合SQL查询

Question

我有以下查询：

INSERT INTO tbl_GroupInvoices 
    SELECT tbl_Invoices.CustomerName AS CustomerName, 
           tbl_Invoices.CountryCode AS CountryCode, 
           tbl_Invoices.[Group] AS [Group], 
           SUM((tbl_Invoices.PageReferenceVolume / tbl_Invoices.DaysInMonth) * tbl_Invoices.ActiveDaysInMonth) AS PageReferenceVolume, 
           SUM(tbl_Invoices.BlackPages + tbl_Invoices.ColorPages) AS ActualPageVolume, 
           ActualPageVolume / PageReferenceVolume AS UsageRate 
    FROM tbl_Invoices 
    GROUP BY tbl_Invoices.CustomerName, tbl_Invoices.CountryCode, tbl_Invoices.[Group]

当我在Ms 2013中直接执行它时，这是非常好的。但是，当嵌入到我的VB.net代码中时，我会收到一条错误消息：

作为聚合函数的一部分，您的查询不包括指定的表达式“ActualPage音量/PageReference容积”。

这是我的vb代码

cmd.Connection = mdlLocalAccDB.accessConn
cmd.CommandType = CommandType.Text

Try
   sQuery = String.Empty
   sQuery = sQuery & "INSERT INTO tbl_GroupInvoices "
   sQuery = sQuery & "SELECT tbl_Invoices.CustomerName AS CustomerName, "
   sQuery = sQuery & "tbl_Invoices.CountryCode AS CountryCode, "
   sQuery = sQuery & "tbl_Invoices.[Group] AS [Group], "
   sQuery = sQuery & "SUM((tbl_Invoices.PageReferenceVolume / tbl_Invoices.DaysInMonth) * tbl_Invoices.ActiveDaysInMonth) AS PageReferenceVolume, "
   sQuery = sQuery & "SUM(tbl_Invoices.BlackPages + tbl_Invoices.ColorPages) AS ActualPageVolume, "
   sQuery = sQuery & "ActualPageVolume / PageReferenceVolume AS UsageRate "
   sQuery = sQuery & "FROM tbl_Invoices "
   sQuery = sQuery & "GROUP BY tbl_Invoices.CustomerName, tbl_Invoices.CountryCode, tbl_Invoices.[Group] "
   cmd.CommandText = sQuery
   QueryReturn = cmd.ExecuteNonQuery()
Catch ex As Exception
    MsgBox("PrepareInvoicing: Invoicing step 6" & vbCrLf & ErrorToString())
    Exit Sub
End Try

有什么想法吗？(但最好使用参数，而不是字符串连接。

Answer 1

通常，在SQL中，不能引用定义相同select子句的其他部分中的列别名。我感到惊讶的是，Access在直接运行查询时支持这一点。

这将是运行该查询的更常见的方法(我刚刚用相应的表达式替换了ActualPageVolume / PageReferenceVolume )：

INSERT INTO tbl_GroupInvoices 
    SELECT tbl_Invoices.CustomerName AS CustomerName, 
           tbl_Invoices.CountryCode AS CountryCode, 
           tbl_Invoices.[Group] AS [Group], 
           SUM((tbl_Invoices.PageReferenceVolume / tbl_Invoices.DaysInMonth) * tbl_Invoices.ActiveDaysInMonth) AS PageReferenceVolume, 
           SUM(tbl_Invoices.BlackPages + tbl_Invoices.ColorPages) AS ActualPageVolume, 
           SUM(tbl_Invoices.BlackPages + tbl_Invoices.ColorPages) / SUM((tbl_Invoices.PageReferenceVolume / tbl_Invoices.DaysInMonth) * tbl_Invoices.ActiveDaysInMonth) AS UsageRate 
    FROM tbl_Invoices 
    GROUP BY tbl_Invoices.CustomerName, tbl_Invoices.CountryCode, tbl_Invoices.[Group]

希望这样更好。

关于你最后的小纸条：

但是，最好是使用参数而不是字符串连接。

当前的查询没有任何需要参数绑定的内容。你现在的方式是完全没有问题的。