group by语句
group by
语句用于将查询结果按字段分组,分组字段没有顺序区别。
使用分组字段时,select
的非聚合函数字段必须包含在分组字段中,但分组字段不一定要出现在select
字段中。
分组语句要声明在where
的后面,在order by
/limit
前面。
with rollup
可以在分组记录之后按分组字段进行聚合,比如分组字段为group by A, B
,则输出的分组为(A, B)
, (A, NULL)
, (NLL, NULL)
,但该关键字和order by
互斥:
SELECT
AVG(salary) avg_sal,
MIN(salary),
MAX(salary),
department_id,
job_id
FROM
employees
WHERE
salary > 0
GROUP BY
department_id,
job_id WITH ROLLUP;
# order by avg_sal DESC;
having语句
having
语句用于实现对聚合函数字段的过滤,必须放到group by
后面,但having
字段不一定是聚合或组合字段:
SELECT
AVG(salary) avg_sal,
MIN(salary),
MAX(salary) max_sal,
department_id
FROM
employees
GROUP BY
department_id
HAVING
avg_sal > 5000
AND max_sal < 20000;
对于非聚合函数字段的过滤,最好放在where
里,比放在having
中效率高:
SELECT
AVG(salary) avg_sal,
MIN(salary),
MAX(salary) max_sal,
department_id
FROM
employees
WHERE
department_id IN (10, 20, 30, 40)
GROUP BY
department_id
HAVING
avg_sal > 5000
AND max_sal < 20000;
where和having的对比
having
适用范围更广,但效率不如where
。
关联查询中,having
先连接后筛选,where
是先筛选后连接
SQL的完整结构
select ..., ..., agg1(), agg2()
from table1 (left/right) join table2
on 表连接条件1
(left/right) join table3
on 表连接条件2
where 不包含聚合函数的过滤条件
group by ...
having 包含聚合函数的过滤条件
order by ... (ASC/DESC)
limit ...;
SQL执行顺序
第一步:按序执行from .... join ... on ... where ... group by ... having ...
;
第二步:按序执行select ... distinct
;
第三步:按序执行order by ... limit ...
;
因为where
先于group by
执行,所以没有聚合函数的过滤条件必须写到where
中,否则会进行大量的无用分组。