SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT

SELECT column1, aggregate_function(column2)
FROM table
WHERE condition-- 可選的行級(jí)過(guò)濾
GROUP BY column1
HAVING aggregate_condition; -- 分組后過(guò)濾

SELECT customer, SUM(amount) AS total_spent
FROM sales
GROUP BY customer
HAVING total_spent > 1000;
-- 結(jié)果：
-- | customer | total_spent |
-- |----------|-------------|
-- | Alice| 1700|
-- | Bob| 1000| ? 不滿足條件
-- | Charlie| 1100|

SELECT customer, AVG(amount) AS avg_order
FROM sales
WHERE region = 'East'-- 先過(guò)濾東部數(shù)據(jù)
GROUP BY customer
HAVING avg_order > 600;
-- 結(jié)果：
-- | customer | avg_order |
-- |----------|-----------|
-- |
-- |----------|-----------|
-- | Charlie| 1100.0|

SELECT customer,
COUNT(*) AS order_count,
MAX(amount) AS max_order
FROM sales
GROUP BY customer
HAVING order_count > 1
AND max_order > 1000;
-- 結(jié)果：無(wú)符合記錄（Alice的最大訂單1200>1000但訂單數(shù)=2，Bob最大訂單800<1000）

SELECT region,
SUM(amount) AS total_sales,
COUNT(DISTINCT customer) AS customers
FROM sales
GROUP BY region
HAVING total_sales / customers > 800; -- 人均消費(fèi)>800的地區(qū)
-- 結(jié)果：
-- | region | total_sales | customers |
-- |--------|-------------|-----------|
-- | East| 2800| 3| 2800/3≈933 >800
-- | West| 1000| 2| 1000/2=500 <800 ?

SELECT product,
SUM(amount) AS revenue,
CASE
WHEN SUM(amount) > 1000 THEN 'High'
ELSE 'Low'
END AS category
FROM sales
GROUP BY product
HAVING category = 'High'; -- 篩選高收入產(chǎn)品
-- 結(jié)果：
-- | product | revenue | category |
-- |---------|---------|----------|
-- | Laptop| 2300| High|

-- 好：先過(guò)濾無(wú)效數(shù)據(jù)
SELECT customer, SUM(amount)
FROM sales
WHERE amount > 0--WHERE amount > 0-- 提前過(guò)濾無(wú)效訂單
GROUP BY customer
HAVING SUM(amount) > 1000
-- 差：所有數(shù)據(jù)都參與分組
SELECT customer, SUM(amount)
FROM sales
GROUP BY customer
HAVING SUM(amount) > 1000 AND amount > 0

-- 推薦（計(jì)算一次）
SELECT customer, SUM(amount) AS total
FROM sales
GROUP BY customer
HAVING total > 1000
-- 不推薦（重復(fù)計(jì)算）
SELECT customer, SUM(amount) AS total
FROM sales
GROUP BY customer
HAVING SUM(amount) > 1000

-- 錯(cuò)誤示例
SELECT customer, SUM(amount)
FROM sales
GROUP BY customer
HAVING product = 'Laptop'; -- product未包含在GROUP BY中
-- 正確做法：改用WHERE
SELECT customer, SUM(amount)
FROM sales
WHERE product = 'Laptop' -- 提前過(guò)濾
GROUP BY customer;

-- 錯(cuò)誤：試圖用WHERE過(guò)濾聚合結(jié)果
SELECT region, AVG(amount)
FROM sales
WHERE AVG(amount) > 1000 -- 非法！
GROUP BY region;
-- 正確：改用HAVING
SELECT region, AVG(amount)
FROM sales
GROUP BY region
HAVING AVG(amount) > 1000;

-- 錯(cuò)誤：缺少GROUP BY
SELECT customer, SUM(amount)
FROM sales
HAVING SUM(amount) > 1000;
-- 正確：添加GROUP BY
SELECT customer, SUM(amount)
FROM sales
GROUP BY customer
HAVING SUM(amount) > 1000;

SELECT customer
FROM sales
GROUP BY customer
HAVGROUP BY customer
HAVING SUM(amount) > 500ING SUM(amount) > 5000;