SQL 不同在 Excel VBA 中不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22855011/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL distinct not working in Excel VBA
提问by Sathish Kothandam
The below code is one of the part in big project.
下面的代码是大项目中的一部分。
Below code counts records in the particular column.
下面的代码计算特定列中的记录。
Sub qareportds()
Dim wb As Workbook
Dim ws As Worksheet
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sname As String
Dim i As Integer
Dim lrow As Integer
sname = ActiveSheet.Name
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
value1 = weeknum
con.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & ActiveWorkbook.FullName
If con.State = adStateClosed Then con.Open
Dim query1 As String
query1 = "SELECT COUNT(RETAIL_SKU) FROM [RETOUCH$]"
rs.Open query1, con, adOpenKeyset, adLockOptimistic
Sheets("SHEET3").Range("a1").CopyFromRecordset rs
rs.Close
Set rs = Nothing
Set con = Nothing
End Sub
Now i need to count the distinct values in the column .I used the below query
现在我需要计算列中的不同值。我使用了以下查询
query1 = "SELECT COUNT(distinct RETAIL_SKU) FROM [RETOUCH$]"
But it throws below error ..
但它抛出以下错误..
odbc driver does not support the requested properties
Update Section:
更新部分：
Is it possible to insert this query-
是否可以插入此查询-
SELECT COUNT(A.RETAIL_SKU) AS TotalCount FROM (SELECT DISTINCT RETAIL_SKU FROM [RETOUCH$]) AS A)
SELECT COUNT(A.RETAIL_SKU) AS TotalCount FROM (SELECT DISTINCT RETAIL_SKU FROM [RETOUCH$]) AS A)
into this
进入这个
SELECT REGION + '-' + STUDIO_SHORT_NAME,
AVG(WorkedHours),
AVG(OVERALL_CYCLE_TIME_HRS),
'Studio Retouch - By Studio Locations - Non IA' as value1,
13 as date1
FROM [RETOUCH$]
WHERE RETOUCH_LEVEL IN (1,2,3,4,5,6)
AND MERCHANT='Amazon'
AND SOURCE_TYPE='Studio'
GROUP BY REGION + '-' + STUDIO_SHORT_NAME
回答by John Woo
It's because COUNT(DISTINCT ...)is not yet supprted. Try,
这是因为COUNT(DISTINCT ...)还没有被支持。尝试，
SELECT COUNT(A.RETAIL_SKU) AS TotalCount
FROM
(
SELECT DISTINCT RETAIL_SKU
FROM [RETOUCH$]
) AS A
