عبارة HAVING
ينطبق على: التحقق من Databricks SQL Databricks Runtime
تصفية النتائج التي تم إنتاجها استنادا GROUP BY
إلى الشرط المحدد. غالبا ما يستخدم بالاقتران مع عبارة GROUP BY .
بناء الجمله
HAVING boolean_expression
معلمات
boolean_expression
أي تعبير يتم تقييمه إلى نوع
BOOLEAN
نتيجة . يمكن دمج تعبيرين أو أكثر معا باستخدام عوامل تشغيل منطقية مثلAND
أوOR
.يمكن أن تشير التعبيرات المحددة في
HAVING
عبارة فقط إلى:- التعبيرات الثابتة
- التعبيرات التي تظهر في GROUP BY
- دالات التجميع
امثله
> CREATE TABLE dealer (id INT, city STRING, car_model STRING, quantity INT);
> INSERT INTO dealer VALUES
(100, 'Fremont' , 'Honda Civic' , 10),
(100, 'Fremont' , 'Honda Accord', 15),
(100, 'Fremont' , 'Honda CRV' , 7),
(200, 'Dublin' , 'Honda Civic' , 20),
(200, 'Dublin' , 'Honda Accord', 10),
(200, 'Dublin' , 'Honda CRV' , 3),
(300, 'San Jose', 'Honda Civic' , 5),
(300, 'San Jose', 'Honda Accord', 8);
-- `HAVING` clause referring to column in `GROUP BY`.
> SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING city = 'Fremont';
Fremont 32
-- `HAVING` clause referring to aggregate function.
> SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING sum(quantity) > 15;
Dublin 33
Fremont 32
-- `HAVING` clause referring to aggregate function by its alias.
> SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING sum > 15;
Dublin 33
Fremont 32
-- `HAVING` clause referring to a different aggregate function than what is present in
-- `SELECT` list.
> SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING max(quantity) > 15;
Dublin 33
-- `HAVING` clause referring to constant expression.
> SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING 1 > 0 ORDER BY city;
Dublin 33
Fremont 32
San Jose 13
-- `HAVING` clause without a `GROUP BY` clause.
> SELECT sum(quantity) AS sum FROM dealer HAVING sum(quantity) > 10;
78