使用row_number（）转换SQL查询以与Excel OLEDB查询兼容

OK, earlier I was trying to use a query that I would normaly use in SQL to grab the lowest valued 'OP' for each 'Facility Code'/'Order Number' pairing. This is contained in the bottom half of the union query shown below.

好的，早些时候我试图使用我在SQL中正常使用的查询来获取每个'Facility Code'/'Order Number'配对的最低值'OP'。这包含在下面显示的union查询的下半部分。

Dim Query As String = "SELECT [Facility Code],[Mach],[Part Number],[Part Name],[Order No#],[OP],[Status],[Order Qty],[Qty Comp]," & _
                                 "[Mfg Due Date],[Planner],[Standard efficiency],[TBC ],[Std run labor time],[Std mach time],[Std setup hrs] " & _
                                 "From [" & sheetName & "] WHERE [Facility Code] LIKE 'R%' " & _
                          "UNION " & _
                                  "SELECT m.[Facility Code],m.[Mach],m.[Part Number],m.[Part Name],m.[Order No#],m.[OP],m.[Status],m.[Order Qty],m.[Qty Comp]," & _
                                         "m.[Mfg Due Date],m.[Planner],m.[Standard efficiency],m.[TBC ],m.[Std run labor time],m.[Std mach time],m.[Std setup hrs] " & _
                                  "FROM " & _
                                  "(SELECT s.[Facility Code],s.[Mach],s.[Part Number],s.[Part Name],s.[Order No#],s.[OP],s.[Status],s.[Order Qty],s.[Qty Comp]," & _
                                         "s.[Mfg Due Date],s.[Planner],s.[Standard efficiency],s.[TBC],s.[Std run labor time],s.[Std mach time],s.[Std setup hrs], " & _
                                         "row_number() over(partition by s.[Facility Code],s.[Order No#] ORDER BY s.[OP] asc) as rn " & _
                                  "FROM [" & sheetName & "] s " & _
                                  "WHERE s.[Facility Code] LIKE 'J%' ) m " & _
                                  "WHERE m.rn =1 "

My problem is that this is reading from an Excel spreadsheet and fails. After going some googling I believe that the row_number() command is not compatible.

我的问题是这是从Excel电子表格中读取并失败。在进行一些谷歌搜索后，我认为row_number（）命令不兼容。

I need to accomplish the same output without it, I tried to group the records, but this query fails as well. I get an error due to the fields that are not in the 'group by' not being an aggregate function.

我需要在没有它的情况下完成相同的输出，我尝试对记录进行分组，但是这个查询也失败了。由于不在“group by”中的字段不是聚合函数，我收到错误。

Dim Query As String = "SELECT [Facility Code],[Mach],[Part Number],[Part Name],[Order No#],[OP],[Status],[Order Qty],[Qty Comp]," & _
                                 "[Mfg Due Date],[Planner],[Standard efficiency],[TBC],[Std run labor time],[Std mach time],[Std setup hrs] " & _
                                 "From [" & sheetName & "] WHERE [Facility Code] LIKE 'R%' " & _
                          "UNION " & _
                          "SELECT [Facility Code],[Mach],[Part Number],[Part Name],[Order No#],[OP],[Status],[Order Qty],[Qty Comp]," & _
                                 "[Mfg Due Date],[Planner],[Standard efficiency],[TBC],[Std run labor time],[Std mach time],[Std setup hrs] " & _
                                 "From [" & sheetName & "] WHERE [Facility Code] LIKE 'J%' " & _
                                 "GROUP BY [Facility Code],[Order No#] ORDER BY [Facility Code],[Order No#],[OP] asc"

Any help would be apreciated, thanks in advance!

任何帮助都会被贬低，提前谢谢！

1 个解决方案

#1

If you want to use a field, that is not in the GROUP BY, you have to use the Function FIRST.

如果要使用不在GROUP BY中的字段，则必须使用函数FIRST。

SELECT FIRST(a) FROM tbl GROUP BY b;

Of course, if you have different values of a inside this, it will take only the first one. Throwing away all others. I don't know the data, therefore I don't know if this is an issue.

当然，如果你有一个不同的内部值，它只需要第一个。扔掉所有其他人。我不知道数据，因此我不知道这是否是一个问题。

#1