使用 HAVING 进行筛选

改进 SQL Server 中的查询性能

Dean Smith

Founder, Atamai Analytics

HAVING 的执行顺序

1.  FROM

4.  WHERE

6.  HAVING

7.  SELECT

结合 WHERE 的分组

SELECT Team, 
    SUM(TotalPoints) AS TotalSGTeamPoints
FROM PlayerStats
WHERE Position = 'SG'
GROUP BY Team

Team	TotalSGTeamPoints
ATL	2034
BOS	1606
BRK	2126
CHI	2905
CHO	2661
CLE	2489
...	...

用 HAVING 做行筛选

SELECT Team, 
    SUM(TotalPoints) AS TotalSGTeamPoints
FROM PlayerStats
WHERE Position = 'SG'
GROUP BY Team

SELECT Team, 
    SUM(TotalPoints) AS TotalSGTeamPoints
FROM PlayerStats
--WHERE Position = 'SG'
GROUP BY Team, Position
HAVING Position = 'SG'

不要用 HAVING 筛选"单行"或"未分组"的行

Team	TotalSGTeamPoints
ATL	2034
BOS	1606
BRK	2126
CHI	2905
CHO	2661
CLE	2489
...	...

按组聚合

SELECT 
    Team, 
    SUM(DRebound+ORebound) AS TotRebounds,
    SUM(DRebound) AS TotDef,
    SUM(ORebound) AS TotOff
FROM PlayerStats
GROUP BY Team;

Team	TotRebounds	TotDef	TotOff
ATL	3436	2693	743
BOS	3645	2878	767
BRK	3644	2852	792
CHI	3663	2873	790
CHO	3728	2901	827
CLE	3455	2761	694
...	...	...	...

用 WHERE 做分组筛选

SELECT 
    Team, 
    SUM(DRebound+ORebound) AS TotRebounds,
    SUM(DRebound) AS TotDef,
    SUM(ORebound) AS TotOff
FROM PlayerStats
WHERE ORebound >= 1000
GROUP BY Team;

使用 WHERE 筛选单行，使用 HAVING 对"分组后"的数值进行筛选

Team	TotRebounds	TotDef	TotOff

缺少聚合函数时

SELECT 
    Team, 
    SUM(DRebound+ORebound) AS TotRebounds,
    SUM(DRebound) AS TotDef,
    SUM(ORebound) AS TotOff
FROM PlayerStats
GROUP BY Team
HAVING ORebound >= 1000;

对数值列应用 HAVING，需配合聚合函数

------------------------------------------------
-- ERROR
Column 'PlayerStats.ORebound' is invalid in the 
HAVING clause because it is not contained in 
either an aggregate function or the GROUP BY 
clause.

配合聚合函数时

SELECT 
    Team, 
    SUM(DRebound+ORebound) AS TotRebounds,
    SUM(DRebound) AS TotDef,
    SUM(ORebound) AS TotalOff
FROM PlayerStats
GROUP BY Team
    -- aggregate function SUM()
HAVING SUM(ORebound) >= 1000;

Team	TotRebounds	TotDef	TotOff
OKC	3695	2671	1024

小结

不要用 HAVING 筛选单行或未分组的行
用 WHERE 筛选单行；对"分组后"的数值筛选用 HAVING
HAVING 只能用于带聚合函数的数值列筛选

Let's practice!

改进 SQL Server 中的查询性能