VBA ADODB.Recordset的等效SQL RANK（） [英] SQL RANK() equivalent for VBA ADODB.Recordset

问题描述

我正在尝试在VBA中为生成计算的等级字段的ADODB.Recordset对象编写SQL查询。如此处的示例所示，无法使用transact-SQL。

I'm trying to write an SQL query in VBA for an ADODB.Recordset object that generates a calculated rank field. Using transact-SQL does not work as seen in the sample here.

strSQL = "SELECT DISTINCT [MONTH-YYYY], Location, (SUM([O/T Earnings])/SUM([Monthly Earnings])) AS OT_AS_PCT "
strSQL = strSQL & "RANK() OVER (PARTITION BY [MONTH-YYYY] ORDER BY (SUM([O/T Earnings])/SUM([Monthly Earnings])) DESC) AS RANK "
strSQL = strSQL & "FROM [Overtime Data$] "
strSQL = strSQL & "GROUP BY [MONTH-YYYY], Location "

rst_ranks.Open strSQL, cnn, adOpenStatic, adLockReadOnly

在使用ADODB中的函数时，我注意到SQL函数不起作用，必须使用VBA函数代替它们。有一个VBA Rank（）函数允许PARTITION BY参数。

I've noticed when using functions in ADODB before that SQL functions do not work and VBA functions must be used in their place. Is there a VBA Rank() function that allows the PARTITION BY parameter.

谢谢！

推荐答案

奇怪的是，标准的SQL函数（例如RANK（）或BETWEEN（））在VBA / ADODB记录字符串中不起作用。相反，必须使用现有的VBA功能并且其工作原理相同。

strangely, standard SQL functions such as RANK() or BETWEEN() do not work in VBA/ADODB record strings. Instead, use of existing VBA functions are necessary and work the same.

这篇关于VBA ADODB.Recordset的等效SQL RANK（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！