Агрегатная функция в SQL WHERE-Clause

В тесте в университете возник вопрос; возможно ли использовать агрегатную функцию в предложении SQL WHERE.

Я всегда думал, что это невозможно, и я также не могу найти никакого примера, как это было бы возможно. Но мой ответ был помечен как ложный, и теперь я хочу знать, в каких случаях можно использовать агрегатную функцию в WHERE. Также, если это невозможно, было бы неплохо получить ссылку на спецификацию, где она описана.

Ответы

Ответ 1

Вы не упомянули СУБД. Предполагая, что вы используете MS SQL-Server, я обнаружил сообщение об ошибке T-SQL, которое не требует пояснений:

"Агрегат может не отображаться в WHERE, если оно не находится в подзапрос, содержащийся в предложении HAVING или список выбора, а столбец агрегировано является внешней ссылкой

http://www.sql-server-performance.com/

И пример того, что это возможно в подзапросе.

Показать всех клиентов и самый маленький заказ для тех, у кого есть 5 или более заказов (и NULL для других):

SELECT a.lastname
     , a.firstname
     , ( SELECT MIN( o.amount )
         FROM orders o
         WHERE a.customerid = o.customerid
           AND COUNT( a.customerid ) >= 5
        )
        AS smallestOrderAmount
FROM account a
GROUP BY a.customerid
       , a.lastname
       , a.firstname ;

UPDATE.

Вышеупомянутые действия выполняются как в SQL-Server, так и в MySQL, но не возвращают ожидаемый результат. Следующий ближе. Я предполагаю, что это связано с тем, что поле customerid, GROUPed BY и используется в соединении запроса-подзапроса в первом случае PRIMARY KEY внешней таблицы, а во втором случае это не так.

Показать все идентификаторы клиентов и количество заказов для тех, у кого есть 5 или более заказов (и NULL для других):

SELECT o.customerid
     , ( SELECT COUNT( o.customerid )
         FROM account a
         WHERE a.customerid = o.customerid
           AND COUNT( o.customerid ) >= 5
        )
        AS cnt
FROM orders o
GROUP BY o.customerid ;

Ответ 2

HAVING похож на WHERE с агрегатными функциями, или вы можете использовать подзапрос.

select EmployeeId, sum(amount)
from Sales
group by Employee
having sum(amount) > 20000

или

select EmployeeId, sum(amount)
from Sales
group by Employee
where EmployeeId in (
    select max(EmployeeId) from Employees)

Ответ 3

Вы не можете использовать агрегат непосредственно в предложении WHERE; что для предложений HAVING.

Вы можете использовать подзапрос, который содержит агрегат в предложении WHERE.

Ответ 4

ОБНОВЛЕНО запрос:

select id from t where id < (select max(id) from t);

Он выберет все, кроме последней строки, из таблицы t.

Ответ 6

Другим решением является Переместить агрегатную функцию на Scalar User Defined Function

Создайте свою функцию:

CREATE FUNCTION getTotalSalesByProduct(@ProductName VARCHAR(500))
RETURNS INT
AS
BEGIN

DECLARE @TotalAmount INT

SET @TotalAmount = (select SUM(SaleAmount) FROM Sales where [email protected])

RETURN @TotalAmount

END

Использовать функцию в разделе Where

SELECT ProductName, SUM(SaleAmount) AS TotalSales
FROM Sales
WHERE dbo.getTotalSalesByProduct(ProductName)  > 1000
GROUP BY Product

Литература:

1. 2.

Надежда помогает кому-то.