chapter06_SELECT进阶

最新推荐文章于 2023-10-13 19:55:45 发布

原创最新推荐文章于 2023-10-13 19:55:45 发布 · 196 阅读

CC 4.0 BY-SA版权

本文深入探讨了SQL中ORDER BY, GROUP BY, DISTINCT, LIMIT, ALL及ANY的应用技巧，包括多关键字排序、分组统计、唯一值筛选、结果限制及集合比较等高级用法，帮助读者掌握高效的数据查询和分析方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

升序ASC和降序DESC可以同时用在一条语句中

示例

  SELECT * FROM movie_table ORDER BY title ASC, purchased DESC;

GROUP BY 可以进行分组加总、求平均值等操作

示例

  SELECT SUM(sales), AVG(sales), MIN(sales), MAX(sales), COUNT(DISTINCT sales_date) GROUP BY first_name ORDER BY SUM(sales) DESC;

LIMIT

(1) LIMIT k ：显示前k个结果

示例

  SELECT name, score FROM class_teacher ORDER BY score DESC LIMIT 5;

  选择排名前5的同学

(1) LIMIT s, k ：显示从s开始的k个结果 (s最小为0)

示例

  SELECT name, score FROM class_teacher ORDER BY score DESC LIMIT 11, 10;

  选择排名从11名开始的10名同学

ALL

(1) 大于(大于等于)加上ALL可以找出任何大于(大于等于)集合中最大值的值

  SELECT name, rating FROM restaurant_ratings 
  WHERE rating > ALL (SELECT rating FROM restaurant_ratings WHERE rating BETWEEN 3 AND 9) ;

(2) 小于(小于等于)加上ALL可以找出任何小于(小于等于)集合中最小值的值

  SELECT name, rating FROM restaurant_ratings 
  WHERE rating <= ALL (SELECT rating FROM restaurant_ratings WHERE rating BETWEEN 3 AND 9) ;

ANY

(1) 大于(大于等于)加上ANY可以找出任何大于(大于等于)集合中最小值的值

  SELECT name, rating FROM restaurant_ratings 
  WHERE rating > ANY (SELECT rating FROM restaurant_ratings WHERE rating BETWEEN 3 AND 9) ;

(2) 小于(小于等于)加上ANY可以找出任何小于(小于等于)集合中最大值的值

  SELECT name, rating FROM restaurant_ratings 
  WHERE rating <= ANY (SELECT rating FROM restaurant_ratings WHERE rating BETWEEN 3 AND 9) ;