La clàusula HAVING s'ha afegit a SQL perquè la paraula clau WHERE no es pot utilitzar amb funcions agregades.
SQL HAVING Exemple 1
La següent instrucció SQL enumera el nombre de clients a cada país.
SQL:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
GenQL:
SelectCount customerIdCount = new SelectCount(CustomersFields.CUSTOMERID);
SelectGroupBy<String> countryGroupBy = new SelectGroupBy<String>(CustomersFields.COUNTRY);
Select2Columns<Long, String> s2c;
s2c = new Select2Columns<Long, String>(customerIdCount, countryGroupBy);
Where where = null;
Where having = customerIdCount.having().greaterThan(5L);
List<Select2Values<Long, String>> list = getCustomerManager().executeQuery(s2c, where, having);
print(list, customerIdCount.getSelectString(), CustomersFields.COUNTRY.javaName);
SQL HAVING Exemple 2
La següent instrucció SQL enumera el nombre de clients de cada país, ordenats de major a menor (només inclou països amb més de 5 clients):
SQL:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;
GenQL:
SelectCount customerIdCount = new SelectCount(CustomersFields.CUSTOMERID);
SelectGroupBy<String> countryGroupBy = new SelectGroupBy<String>(CustomersFields.COUNTRY);
Select2Columns<Long, String> s2c;
s2c = new Select2Columns<Long, String>(customerIdCount, countryGroupBy);
Where where = null;
Where having = customerIdCount.having().greaterThan(5L);
OrderBy orderBy = new OrderBy(customerIdCount.getSelectString(), OrderType.DESC);
List<Select2Values<Long, String>> list = getCustomerManager().executeQuery(s2c, where, having, orderBy);
print(list, customerIdCount.getSelectString(), CustomersFields.COUNTRY.javaName);
SQL HAVING Exemple 3
La següent instrucció SQL enumera els empleats que han registrat més de 10 comandes:
SQL:
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;
GenQL:
Field<String> lastName = new OrdersQueryPath().EMPLOYEES().LASTNAME();
SelectGroupBy<String> lastNameGroupBy = new SelectGroupBy<String>(lastName);
SelectCount orderIdCount = new SelectCount(OrdersFields.ORDERID);
Select2Columns<String, Long> s2c;
s2c = new Select2Columns<String, Long>(lastNameGroupBy, orderIdCount);
Where where = null;
Where having = orderIdCount.having().greaterThan(10L);
List<Select2Values<String, Long>> list;
list = getOrdersManager().executeQuery(s2c, where, having);
print(list, lastNameGroupBy.getSelectString(), orderIdCount.getSelectString());
SQL HAVING Exemple 4
La següent instrucció SQL indica si els empleats "Davolio" o "Fuller" han registrat més de 25 comandes:
SQL:
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;
GenQL:
Field<String> lastName = new OrdersQueryPath().EMPLOYEES().LASTNAME();
SelectGroupBy<String> lastNameGroupBy = new SelectGroupBy<String>(lastName);
SelectCount orderIdCount = new SelectCount(OrdersFields.ORDERID);
Select2Columns<String, Long> s2c;
s2c = new Select2Columns<String, Long>(lastNameGroupBy, orderIdCount);
Where where;
{
Where w1 = lastName.equal("Davolio");
Where w2 = lastName.equal("Fuller");
where = Where.OR(w1, w2);
}
Where having = orderIdCount.having().greaterThan(25L);
List<Select2Values<String, Long>> list;
list = getOrdersManager().executeQuery(s2c, where, having);
print(list, lastNameGroupBy.getSelectString(), orderIdCount.getSelectString());