Filtering on calculated columns in hibernate? (HAVING)
Say I have a table with two columns, X and Y. I would like to run the following query via hibernate:
SELECT X*X + Y*Y AS Distance, POINTS.* from POINTS
WHERE X > 0
AND Y > 0
HAVING Distance < 50
开发者_Python百科How do I achieve it via hibernate? Can you provide a code sample?
Edit - This question seems heavily related. The only reason I'm not closing this one as a duplicate, is because it provides a much simpler use case (no GROUP BY).
In most dialects of SQL, you cannot use the 'display label' or the name in 'AS name' to refer to an expression inside the body of the query - much to the chagrin of people. However, you can use the expression in the WHERE clause.
SELECT X*X + Y*Y AS DistanceSquared, POINTS.* from POINTS
WHERE X > 0
AND Y > 0
AND (X * X + Y * Y) < 50;
The HAVING clause is associated with aggregates and comparisons on aggregates:
SELECT name, COUNT(*)
FROM SomeWhere
GROUP BY Name
HAVING COUNT(*) > 1;
The conditions in a HAVING clause must (should) involve at least one 'direct' aggregate; the other term in the condition might be a constant (as shown) or another direct aggregate, or a sub-query, possibly involving 'indirect' aggregates. (AFAIK, I've invented the 'direct/indirect' terminology for this answer - you probably won't be able to search for the expression usefully.)
精彩评论