Блог

Главная - Вопросы по программированию - Как найти набор записей с наибольшим значением поля, используя функцию MAX, где поле является строкой?

Как найти набор записей с наибольшим значением поля, используя функцию MAX, где поле является строкой?

#sql #vba

Вопрос:

У меня есть таблица в Microsoft Access с несколькими повторяющимися записями, содержащими разные значения в определенном поле столбца. Из-за оригинального дизайна тип поля столбца представляет собой короткий текст, значения которого содержат все числа, за исключением выбора «Без номера», и в настоящее время не могут быть изменены.

В VBA я пытаюсь найти запись, содержащую наибольшее значение в поле 3. Например, таблица может выглядеть следующим образом:

 Field1 | Field2 | Field3 Jay Red 2 Aden Blue 1 Jay Red 5 Colin Orange 3 Jay Red No Number

Я хотел бы вернуть набор записей, содержащий строку, содержащую Jay, Red, 5.

Я написал строку SQL, но, насколько мне известно, это неправильно, потому что Access жалуется на следующее:

введите описание изображения здесь

Вот как выглядит строка SQL:

 mySQL = "SELECT " amp; _  "(SELECT TOP 1 Field3" amp; _  "FROM MyTable" amp; _  "Where Field1 = '" amp; rs1.Fields("Field1") amp; "'" amp; _  ") as Field3, Field1, Field2 " amp; _  "FROM " amp; _  "(SELECT Max(Val(Field3)) As Fld3, Field1, Field2 " amp; _  "FROM MyTable" amp; _  "GROUP BY Field3, Field1, Field2)"    criteria = "Field1= '" amp; rs1.Fields("Field1") amp; "'"  Set rs2 = CurrentDb.OpenRecordset(mySQL, _  dbOpenSnapshot, _  dbReadOnly)  With rs2  rs2.FindLast criteria     If Not rs2.NoMatch Then  Dim str1 As String    str1 = rs2.Fields("Field3")   rs2.Close  Set rs2 = Nothing  End With

Я не думаю, что строка SQL в целом верна, но я также не уверен, как использовать функцию MAX в случае, когда поле столбца является строкой? Любая помощь будет признательна.

Ответ №1:

Вы можете добиться этого довольно просто с помощью SQL, который выглядит следующим образом:

 SELECT TOP 1 Field1, Field2, Field3 FROM MyTable WHERE Field3 lt;gt; 'No Number' ORDER BY CInt(Field3) DESC;

Это предполагает, что единственным нечисловым значением является «Без числа».

Объединение TOP 1 с ORDER BY DESC дает тот же эффект получения максимального значения, но проще, потому что нет необходимости в дополнительном выборе. Если вам нужно добавить другие критерии, просто добавьте их «И» в предложение WHERE.

Редактировать

После вашего комментария о том, что вам, возможно, потребуется включить «Без номера», измените SQL на:

 SELECT TOP 1 Field1, Field2, Field3 FROM MyTable WHERE -- your criteria here ORDER BY CInt(IIF(Field3='No Number',0,Field3)) DESC;

Я предполагаю здесь, что поле 3 всегда является числом больше 1, когда оно существует. Если оно может быть равно 0, то установите по умолчанию в IIF отрицательное число.

Комментарии:

1. спасибо, но мне нужно, чтобы набор записей был найден на основе WHERE условия Field1 в первом rs1 наборе записей, а не условия Field3 . Меняет ли это логику вашего кода? ОБНОВЛЕНИЕ: не видел вашего нижнего описания. Я проверю со своей стороны. Спасибо!

2. Отлично работает, но остается один вопрос. Я добавил еще один критерий в предложение WHERE для поиска записи на rs2 основе значения поля rs1 . Как я все еще могу сохранить найденную запись, rs2 если она содержит только «Без числа» в качестве значения Field3 ?

3. Пожалуйста, посмотрите мою правку

4. Я просто подумал о крайнем случае, и мне было интересно, так Field3 как это Short Text тип, т. Е. a String , я хочу изменить аргумент внутри IIF , чтобы быть IIF(Field3='No Number','No Number',Field3) , поэтому, если Field3='No Number' , затем установите его как No Number вместо 0, в противном случае установите его Field3 равным . Тем не менее, я получаю a Data type mismatch in criteria expression из-за преобразования a String в an Int из CInt функции. Я удалил CInt , но если значение Field3 gt; 10, то gt; SELECT не захватывает эту запись и захватывает что-либо ниже Field3 = 10 . Как я могу этого достичь?

5. Вы должны оставить IIF в том виде, в каком я его дал. Причина в том, что мы хотим включить его в порядок по, и для этого нам нужно преобразовать его в целое число. Ваша версия вообще не меняется — это то же самое, что просто поле!

Метки: , ,