基本执行顺序:
FROM > WHERE > GROUP BY > SELECT > ORDER BY
复杂版执行顺序:
FROM > ON > JOIN > WHERE > GROUP BY > 聚合函数 > HAVING > SELECT > DISTINCT > ORDER BY > LIMIT
-
where:对数据库记录生效,无法对聚合结果生效,可以过滤掉最大数量记录的条件必须写在where子句末尾,不能使用聚合函数(sum、count、max、avg)
-
group by:如何将上面过滤出的数据分组
- avg:求平均值
-
having:对上面已经分组的数据进行过滤的条
- select:查看结果集中的哪个列或列的计算结果
- distinct:对结果集重复值去重
-
order by:按照什么样的顺序来查看返回的数
- limit:截取出目标页数据
以上这段转载至https://segmentfault.com/a/1190000024577490,主要转载自用,若有侵权,还请联系我,谢谢啦~
聚合函数和CASE WHEN的使用关系:
聚合函数要放在外面,即:
MAX(CASE WHEN continent = 'America' THEN name ELSE NULL END) AS America
而不是:
CASE WHEN continent = 'America' THEN MAX(name) ELSE NULL END AS America
注意:CASE WHEN可以没有ELSE,但是一定要有END
SELECT
MAX(CASE WHEN continent = 'America' THEN name ELSE NULL END) AS America,
MAX(CASE WHEN continent = 'Asia' THEN name ELSE NULL END) AS Asia,
MAX(CASE WHEN continent = 'Europe' THEN name ELSE NULL END) AS Europe
FROM
(SELECT *,ROW_NUMBER() OVER(PARTITION BY continent Order by name) AS rk
FROM student) a
GROUP BY rk