Поделиться через

HAVING (Entity SQL)

  • Статья

Specifies a search condition for a group or an aggregate.

[ HAVING search_condition ]

Arguments

  • search_condition
    Specifies the search condition for the group or the aggregate to meet. When HAVING is used with GROUP BY ALL, the HAVING clause overrides ALL.

Remarks

The HAVING clause is used to specify an additional filtering condition on the result of a grouping. If no GROUP BY clause is specified in the query expression, an implicit single-set group is assumed.

Note

HAVING can be used only with the SELECT (Entity SQL) statement. When GROUP BY (Entity SQL) is not used, HAVING behaves like a WHERE clause.

The HAVING clause works like the WHERE clause except that it is applied after the GROUP BY operation. This means that the HAVING clause can only make references to grouping aliases and aggregates, as illustrated in the following example.

SELECT Name, SUM(o.Price * o.Quantity) AS Total FROM orderLines AS o GROUP BY o.Product AS Name
HAVING SUM(o.Quantity) > 1

The previous restricts the groups to only those that include more than one product.

Example

The following Entity SQL query uses the HAVING and GROUP BY operators to specify a search condition for a group or an aggregate. The query is based on the AdventureWorks Sales Model. To compile and run this query, follow these steps:

  1. Follow the procedure in How to: Execute a Query that Returns PrimitiveType Results (EntityClient).

  2. Pass the following query as an argument to the ExecutePrimitiveTypeQuery method:

SELECT VALUE name FROM AdventureWorksEntities.Product 
    as P GROUP BY P.Name HAVING MAX(P.ListPrice) > 5

The output is shown below:

Value: LL Mountain Seat Assembly
Value: ML Mountain Seat Assembly
Value: HL Mountain Seat Assembly
Value: LL Road Seat Assembly
Value: ML Road Seat Assembly

See Also

Concepts

Entity SQL Reference
Query Expressions (Entity SQL)