使用HAVING子句在GROUP BY之後過濾分組數(shù)據(jù)，特別是當(dāng)條件涉及COUNT()、SUM()、AVG()等聚合函數(shù)時(shí)；2. 與WHERE子句不同，WHERE用於在分組前過濾單行，而HAVING用於在分組後基於聚合結(jié)果過濾組；3. HAVING必須置於GROUP BY之後，且不能使用SELECT中的列別名，需重複聚合表達(dá)式；4. 可同時(shí)使用WHERE和HAVING，前者過濾原始行，後者過濾分組結(jié)果；5. 常見應(yīng)用場(chǎng)景包括查找訂單數(shù)超過指定值的客戶、平均工資高於某數(shù)值的部門，或排除含NULL值的組；6. 總結(jié)：當(dāng)需要根據(jù)聚合結(jié)果篩選分組時(shí)，必須使用HAVING，它是SQL中實(shí)現(xiàn)分組級(jí)別條件過濾的關(guān)鍵機(jī)制。

The HAVING clause in SQL is used to filter groups of rows after the GROUP BY clause has been applied. Unlike the WHERE clause, which filters individual rows before grouping, HAVING filters after grouping, allowing you to apply conditions on aggregate functions like COUNT() , SUM() , AVG() , etc.

When to Use HAVING Instead of WHERE

Use HAVING when your filter condition involves aggregated data. For example:

• "Show departments with more than 5 employees" → needs COUNT() , so use HAVING
• "Show employees named 'John'" → filters individual rows, use WHERE

Basic Syntax

 SELECT column, AGGREGATE_FUNCTION(expression)
FROM table_name
WHERE condition (optional)
GROUP BY column
HAVING aggregate_condition;

Note: HAVING comes after GROUP BY .

Example: Filtering Groups with HAVING

Suppose you have a table sales :

You want to find regions where total sales exceed 200.

 SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region
HAVING SUM(amount) > 200;

Result:

Even though the South region has two entries, their total is 300? Wait — let's recalculate:

• North: 100 150 50 = 300
• South: 200 100 = 300

So both are above 200 → both should appear.

 -- Correct result will include both North and South

But if you change the threshold:

 HAVING SUM(amount) > 300; -- Then neither qualifies

Or maybe:

 HAVING COUNT(salesperson) >= 2; -- Regions with at least 2 sales records

This would return both regions again in this case.

Common Use Cases

• Find customers with more than 3 orders:

   SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
  HAVING COUNT(*) > 3;

• Find departments where average salary is above $70,000:

   SELECT dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept
  HAVING AVG(salary) > 70000;

• Exclude groups with missing data:

   SELECT project_id, AVG(hours_worked)
  FROM time_logs
  GROUP BY project_id
  HAVING COUNT(hours_worked) = COUNT(*); -- Ensures no NULLs in hours_worked

Key Points

• Always use HAVING with GROUP BY (except in some databases where it's allowed without, but it's rare).

• You cannot use aliases from the SELECT clause in the HAVING clause (because HAVING is logically processed before SELECT ).

   -- This might fail or be unsupported:
  SELECT region, SUM(amount) AS total
  FROM sales
  GROUP BY region
  HAVING total > 200; -- ? Using alias 'total' here is invalid in most SQL dialects

  Instead, repeat the expression:

   HAVING SUM(amount) > 200; -- ? Correct

• You can combine WHERE and HAVING :

   SELECT region, COUNT(*) 
  FROM sales
  WHERE amount > 50 -- Filters rows before grouping
  GROUP BY region
  HAVING COUNT(*) > 1; -- Filters groups after aggregation

  ---

  So, in short: use HAVING when you need to filter based on group-level summaries. It's essential for meaningful reporting and analysis in SQL.

  Basically, if your WHERE can't handle aggregate conditions, HAVING steps in.

  以上是您如何使用HAVER子句在SQL中過濾組？的詳細(xì)內(nèi)容。更多資訊請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！