#sql #group-by #max #aggregate-functions
#sql #группа-по #максимум #агрегатные функции
Вопрос:
Запрос:
SELECT A.mrno, A.remarks, B.itemcode, B.description, B.uom, B.quantity, C.whsecode, MAX(C.quantity) AS whseqty, D.rate FROM Mrhdr A INNER JOIN Mrdtls B ON A.mrno = B.mrno INNER JOIN inventoryTable C ON B.itemcode = C.itemcode INNER JOIN Items D ON B.itemcode = D.itemcode WHERE (A.mrno = @MRNo AND B.quantity lt; C.quantity); Ошибка:
Столбец «Mrhdr.mrno» недопустим в списке выбора, поскольку он не содержится ни в агрегатной функции, ни в предложении GROUP BY.
Он говорит, что столбец мрно не содержится ни в агрегатной функции, но когда я что-то сделать, как положить его в группе п. в следующем столбце запросы возвращают ту же ошибку до последнего столбцов, кроме C.quantity столбца, и, когда все они находятся в группе by пункт, он будет возвращать только те же выходные данные не возвращаются наибольшее или максимальное значение для количества. Что мне делать с другими столбцами, когда я использую функции MAX или aggregate.
Вывод запроса выше:
Если я помещу все столбцы в предложение GROUP BY, оно вернет вывод с двумя кодами элементов FG 4751, это просто устранит ошибку агрегатной функции, но я просто хочу, чтобы было возвращено наибольшее значение (всего 100, наибольшее количество на складе/складе).
Комментарии:
1. Обычно, если вы помещаете некоторые из своих столбцов в функцию агрегирования, то все остальные столбцы должны быть либо в функции агрегирования, либо перечислены в предложении «группировать по».
2. Пожалуйста, расскажите нам, что должен делать запрос. Пока похоже, что вы хотите показать все Mrdtl (возможно, детали заказа?), у которых сумма меньше, чем общий запас продукта. Таким образом, с продуктом на складе 10 10 = 20 и заказами 10, 15, 20 и 25 вы бы показали только заказы 10 и 15. Это то, что вы хотите, чтобы запрос выполнял?
3. Что такое ваша СУБД? Пожалуйста, пометьте им запрос. Стандарт SQL, вероятно, подойдет для простого
GROUP BY b.id, в то время как некоторым другим СУБД могут потребоваться дополнительные столбцы в этом предложении. Всегда помечайте запросы SQL с помощью используемой вами СУБД.4. @ThorstenKettner Я добавил вывод, который он возвращает, и вывод, который должен выполнять запрос. Я забыл добавить его, когда опубликовал, надеюсь, это поможет.
Ответ №1:
Вы хотите иметь дело с максимальным количеством запасов на один продукт. Но вы объединяете все строки запасов, где вы должны выбрать только строки с максимальным количеством.
Это можно сделать с помощью бокового соединения, если ваша СУБД поддерживает это (вы забыли сообщить нам, что вы используете), или просто путем объединения рассматриваемых строк, применив оконную функцию следующим образом.
SELECT A.mrno, A.remarks, B.itemcode, B.description, B.uom, B.quantity, C.whsecode, C.whseqty, D.rate FROM Mrhdr A INNER JOIN Mrdtls B ON A.mrno = B.mrno INNER JOIN ( SELECT itemcode, whsecode, quantity as whseqty, MAX(quantity) OVER (PARTITION BY itemcode) AS max_qty FROM inventoryTable ) C ON B.itemcode = C.itemcode AND C.whseqty = C.max_qty INNER JOIN Items D ON B.itemcode = D.itemcode WHERE A.mrno = @MRNo AND B.quantity lt; C.whseqty; Этот запрос должен работать в большинстве СУБД. Если вы работаете с СУБД, которая поддерживает стандартное FETCH WITH TIES предложение SQL, я бы изменил соединение на:
INNER JOIN ( SELECT itemcode, whsecode, quantity as whseqty FROM inventoryTable ORDER BY RANK() OVER (PARTITION BY itemcode ORDER BY quantity DESC) FETCH FIRST ROW WITH TIES ) C ON B.itemcode = C.itemcode чтобы уже выбрать только верхние строки внутри подзапроса и не фильтровать их позже. Но что ж, боковое соединение здесь можно даже считать более прямым.
Комментарии:
1. Whsecode возвращает ошибку, потому что он не находится внутри инструкции SELECT языка C
2. @Ричард Джонсон: Спасибо, что указали на это.. Я пропустил эту часть. Поэтому мы не можем агрегировать, а должны вместо этого выбирать строки. Я соответствующим образом обновил свой ответ.
Ответ №2:
Альтернативой помещению всего в предложение group by было бы использование функции окна. Тогда возникает вопрос, по отношению к чему МАКСИМАЛЬНОЕ значение?
Например, вы можете получить МАКСИМАЛЬНОЕ значение на основе всех критериев, что вернет аналогичный результат для group by, не оставляя только отдельных значений для столбца.
SELECT A.mrno, A.remarks, B.itemcode, B.description, B.uom, B.quantity, C.whsecode, MAX(C.quantity) OVER(PARTITION BY A.mrno, A.remarks, B.itemcode, B.description, B.uom, B.quantity, C.whsecode, D.rate) AS whseqty, D.rate FROM Mrhdr A INNER JOIN Mrdtls B ON A.mrno = B.mrno INNER JOIN inventoryTable C ON B.itemcode = C.itemcode INNER JOIN Items D ON B.itemcode = D.itemcode WHERE (A.mrno = @MRNo AND B.quantity lt; C.quantity);