Как заказать SQL-запрос с сгруппированными строками
У меня есть таблица (Product_Id, приоритет категории, атрибут1, атрибут2 ...) в MS Access, и я пытаюсь сделать запрос, который упорядочивает данные, сгруппированные по категориям и упорядоченные по наивысшему приоритету. Приоритет может быть пустым, поэтому его следует поставить в конце. Пример: Таблица
1, 100, 2, atr1, atr2
2, 300, , atr1, atr2
3, 100, 5, atr1, atr2
4, 200, 9, atr1, atr2
5, 100, , atr1, atr2
6, 200, 1, atr1, atr2
Ожидаемый результат в запросе:
6, 200, 1, atr1, atr2
4, 200, 9, atr1, atr2
1, 100, 2, atr1, atr2
3, 100, 5, atr1, atr2
5, 100, , atr1, atr2
2, 300, , atr1, atr2
Ответы 5
Вам нужно взвесить категории (я взвешиваю null с некоторым подходящим большим значением):
select t1.*
from myTable t1
join
(
select category, min(coalesce(priority, 1000)) weight
from myTable
group by category
) t2
on t1. category = t2. category
order by t2.weight, coalesce(t1. priority, 1000)
На самом деле вам нужно «заказать по t2.weight desc» и использовать «isnull (t1.priority, 0)» и max () - или как я понимаю исходный вопрос :)
Jet не поддерживает COALESCE или ISNULL.
ISNULL можно заменить на Nz () в Jet SQL, не так ли? Я не верю, что в Jet SQL есть какая-либо команда для COALESCE.
На самом деле нет, NZ () не является собственным синтаксисом Jet SQL. NZ () предоставляется объектной моделью MS Access и поэтому доступен только в запросах через интерфейс MS Access. Собственный Jet должен использовать IIF, который, как считает FWIW Аллен Браун, в любом случае превосходит NZ () (allenbrowne.com/QueryPerfIssue.html)
Насколько мне известно, Jet всегда сортирует NULL в конец набора результатов, когда используется явный ORDER BY.
См .: Свойства и настройки поставщика ADO
«Порядок сопоставления NULL: длинное значение (только для чтения), указывающее, где сравниваются (сортируются) значения NULL. Для поставщика Microsoft Jet значение всегда равно 4, что указывает на то, что значения NULL сортируются в нижнем конце списка. . "
Тот факт, что свойство NULL Collation Order доступно только для чтения для поставщика, настоятельно предполагает, что в движке Jet есть только одно сопоставление NULL, и, к счастью для вас, это то, что вам нужно.
Если «нулевые значения отсортированы в нижнем конце списка», то нулевые значения будут отображаться первыми в списке. это противоречит вопросу, где нуль «следует помещать в конец».
Рон, похоже, ты неверно истолковал значение слова «нижняя часть списка». Ключевое слово здесь - «конец». Подумайте о наборе записей: значения NULL будут отсортированы по позициям, ближайшим к EOF (конец файла). Вы тестировали это с помощью Jet? Попробуйте, и я думаю, вы согласитесь, что NULL не появляются первыми.
В Jet SQL это может подойти:
SELECT t2.MinOfPriority, tn.Field2, Nz([tn.Field3],999) AS Priority,
tn.Field4, tn.Field5
FROM tn
INNER JOIN (SELECT Min(Nz(tn.Field3,999)) AS MinOfPriority, tn.Field2
FROM tn GROUP BY tn.Field2) AS t2 ON tn.Field2 = t2.Field2
ORDER BY t2.MinOfPriority, tn.Field2, Nz([Field3],999);
бах, ты просто победил меня :) Вопрос о твоей «таблице» во внутреннем соединении значительно сложнее, чем в примере, который я использовал. Я не уверен, что так должно быть.
Я работал с ZLS, а не с пустыми полями из вырезания и вставки :(. Я исправил это.
«В Jet SQL это может подойти» - нет, потому что Jet SQL не поддерживает функцию NZ (). Вы должны были сказать: «В MS Access ...» или что-то подобное, потому что функция NZ () предоставляется интерфейсом MS Access. В Jet за пределами интерфейса MS Access ваши ошибки SQL: неопределенная функция «NZ» в выражении.
Это правда. Возможно, "Jet используется с Access"?
Лучше IIF ([Field3] IS NULL, 999, [Field3]), потому что он будет работать как в Jet, так и в MS Access и позволяет избежать известных проблем с типизацией данных (которые влияют на сортировку) с помощью NZ (). По другим причинам см. allenbrowne.com/QueryPerfIssue.html.
По всей видимости NZ (Value, ValueToReturnIfNull) можно использовать в MSAccess в качестве замены ISNULL, поэтому ...
SELECT a.*
FROM this_table AS a
INNER JOIN
(
SELECT category,min(NZ(priority,999999)) as min_priority_in_cat
FROM this_table group by category
) AS b ON a.category = b.category
ORDER BY b.min_priority_in_cat, a.category, NZ(a.priority,999999)
Большое спасибо, Рон, но это еще не работает полностью. Он отлично работает, если определены приоритеты. Если существует несколько категорий без приоритета, эти категории не группируются.
Я не уверен, что понимаю. Вышеупомянутое должно привести к тому, что любая категория будет иметь только нулевой приоритет вниз. Если вы хотите отсортировать эти категории (с приоритетом только NULL) между собой, добавьте a.category в конец предложения ORDER BY. Дайте мне знать, я обновлю.
еще раз спасибо, Рон, вот и все, я добавил a.category после b.min_priority_in_cat и работает отлично. Узнал от Рему (tn.Field2). Спасибо за вашу помощь!
«NZ (Value, ValueToReturnIfNull) может использоваться в jet как замена ISNULL» - Рон, Jet не имеет функции NZ (). Однако MS Access делает.
Самое простое решение (не обязательно лучшее в некоторых случаях) - использовать номера столбцов в выражениях упорядочивания:
SELECT t2.MinOfPriority,
tn.Field2,
Nz([tn.Field3],999) AS Priority,
tn.Field4,
tn.Field5
ORDER BY 1,2,3
MS Jet этого не поймет.