跳到主要内容

查询子句顺序

SQL 查询子句的书写顺序是什么？​

SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY
  • SELECT: 要输出的属性;
  • FROM: 数据来源;
  • WHERE: 元组级过滤;
  • GROUP BY: 分组;
  • HAVING: 分组级过滤;
  • ORDER BY: 排序;

一句完整查询里各子句出现在哪里？​

SELECT studioName, SUM(length) AS total
FROM Movies
WHERE year >= 1990
GROUP BY studioName
HAVING SUM(length) > 500
ORDER BY total DESC;
  • 顺序核对: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY;

各子句的作用时机可以从哪里看出来？​

  • WHERE 只看到单个元组, 因此不能写聚集条件;
  • HAVING 作用于 GROUP BY 之后, 因此可以写聚集条件, 见 分组与 HAVING;
  • ORDER BY 在最后执行, 因此可以按最终结果的列排序, 见 排序与表达式;

（source: 050-sql.md §全关系操作/§SQL 查询子句顺序）